0

I have thousands of entries for the same site names taken between different days. However not all row labels are identical. I just want to select all row labels that are shared among both spreadsheets based on the names contained in column A for example and copy them to a new sheet. Filtering and selecting wont work, theres thousands and different entries between the two. I just want to delete entries that are not shared among both spreadsheets.

I looked through other forums using vlookup but I am not sure i understand the syntax: e.g. i looked at this forum: Matching two columns from two spreadsheets and grabbing data from one of the spreadsheets it proposed this solution: =index(sheet2!B1:B3;match(sheet1!G1;sheet2!A1:A3;0)). So this solution join row from 2nd sheet to row first sheet. If column G 1st sheet and column A 2nd sheet are the same then you can use this to match. Place formula in column H 1st sheet. With this formula you will fetch data from column B 2nd sheet to column H 1st sheet.

I dont think this is the case since the positions of similar row values in both columns differ. I just want to know what labels are shared among both and delete entries that dont share those names

example of output

MaxiGui
  • 6,190
  • 4
  • 16
  • 33
communityecologist
  • 3
  • 3

1 Answers1

0

Yes vlookup is the key because if you lookup something in spreadsheet1:colA to see if it is in spreadsheet2:colA if it is not present you will get a null value. Then you can filter out these nulls to get only the list of rows which share the column A value in spreadsheet2. You will also need to repeat this in spreadsheet2.

For context, assuming the image you posted covers cells A1:C6, and your highlighted cells are A4:C5 then: In sheet1 cell D3 put =VLOOKUP(A3, Sheet2!A:A, 1,0) and copy down for the rest of column D, and in sheet2 cell D3: =VLOOKUP(A3, Sheet1!A:A, 1,0)

IF the vlookup finds a match, this will give the exact same value in columnD which is present in both spreadsheets, otherwise it will give #N/A. Then you can filter those out. (By the way the syntax for the sheet name depends on whether you have spaces in the name so Sheet1!A:A but 'Sheet 1'!A:A, I usually get these by highlighting them so excel does the work of naming the worksheet).

A side note, I would drag both worksheets into one file, you certainly can perform vlookups between different files, but this then relies on the exact file path so if you ever move either file, the vlookups will break and give you errors. I only ever vlookup within the same file.

ChrisD
  • 50
  • 7