Google Sheets – How to Reconcile and Sync Two Sets of Data with Same Key Value

google sheets

Ive got a list of URLS in a google sheet.

charlie.com |  apple   | 
alpha.com   |  orange  |
beta.com    |  banana  |

Ive got another list of data as a CSV (this could be imported into google sheets if it made the workflow easier) this data is as follows :

charlie.com |  one
alpha.com   |  three 
beta.com    |  seven

(note the URLS are the same, but the other data is different, the row order based upon the URLS is different also.

I want to combine these two sets of data so i get :

charlie.com |  one    |  apple
alpha.com   |  three  |  orange
beta.com    |  seven  |  banana

(so that all the data is correctly aligned using the URLS as the key value.

How can i do this ?

So far from doing some research on Google, ive been looking at a 3rd party paid plugin (HERE) from that would enable me to do this, i feel its something that a spreadsheet would have input as a native function / workflow, but i cant seem to find the right search term for it when researching.

Best Answer

Since both lists of URLs have the same order and the same number of elements, you could use the Google Sheet array handling feature, i.e.:

={List1!A1:B3,List2!B1:B3}

If the real data from both list doesn't have the same number of elements but one list include all the elements of the other list you could do a "left join" or a "right join". The common ways to do this in Google Sheets is by using:

VLOOKUP
Using together INDEX and MATCH

If the real data from both list doesn't have the same number of elements and none list have all the elements of the other list but you don't want to miss any row you could do a "full outer join". A common way to do this in Google Sheets is to first create a list of all the values used as the row identifier, in this case the URLs, then do use the functions mentioned above two times, one for each list.

To create the list of URLs you could use the following formula:

=UNIQUE({List1!A1:A3;List2!A1:A3})

References

Related Solutions

Google Sheets – List Key with Highest Value in Key-Value Pair

You need to specify a condition, not just a value, for the FILTER function.

So:

=FILTER(A2:A6, B2:B6 = MAX(B2:B6))

I have set up a demonstration spreadsheet for this, feel free to have a look or copy it.

Google Sheets – How to Sync Two Cells Value

You can link two cells using a google script. You can use on Edit trigger of each sheet to poll for which cell is getting modified and modify the other one in the other sheet.

The below code will link cell A1 in "Sheet1" in of Spreadsheet with ID spd1ID to B1 in "Sheet1" in Spreadsheet with ID spd2ID

var spd1ID = "ID of first sheet here"
var spd2ID = "ID of second sheet here"

function myLinkCells() {
 ScriptApp.newTrigger("callOnEdit").forSpreadsheet(spd1ID).onEdit().create()
 ScriptApp.newTrigger("callOnEdit").forSpreadsheet(spd2ID).onEdit().create()
}


function callOnEdit(e){
  var range = e.range
  var cellAdd = range.getA1Notation()
  Logger.log("Called")
  var ssID = e.source.getId()
  var sName = range.getSheet().getSheetName()
  if( ssID == spd1ID && sName == "Sheet1"){
  if(cellAdd == "A1"){
     var ss = SpreadsheetApp.openById(spd2ID)
     var rng = ss.getSheetByName("Sheet1").getRange("B1")
     rng.getSheet().getSheetName()
     rng.setValue(e.value)
     Logger.log("In Cell A1")
     }
  } else if(ssID == spd2ID && sName == "Sheet1"){
    if(cellAdd == "B1"){
     var ss = SpreadsheetApp.openById(spd1ID)
     var rng = ss.getSheetByName("Sheet1").getRange("A1")
     rng.setValue(e.value)
     Logger.log("In Cell B1")

     }
  }
}

Where to paste this code? Open your spreadsheet Tools>Script Editor

What is spreadsheet ID? https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id

After you paste the code and modify the ID, run myLinkCells() function using Run menu. That will link the cells.

Hope that helps!

Best Answer

Related Solutions

Google Sheets – List Key with Highest Value in Key-Value Pair

Google Sheets – How to Sync Two Cells Value

Related Topic