1

I have 2 spreadsheets and need to merge and update the data combination of both.

Spreadsheet A
userid, name, phone1, phone2, city
1, mister T, 123 123, 123 124, paris
2, mister A, , 123 124, tanger
3, mister B, 123 153,, tanger
5, mister D, 213 123, 123 435, london

Spreadsheet B
userid, name, phone1, phone2
1, mister T, 123 123, 123 124
2, mister A, 193 124 , 
3, mister B, 123 154,123 153
4, mister C, 123 183, 183 184
6, mister Y, 123 883, 183 884 


The resulting merge should be
userid, name, phone1, phone2
1, mister T, 123 123, 123 124, paris [No change, both are the same]
2, mister A, 193 124, 123 124, tanger [row get updated merging both spreadsheet]
3, mister B, 123 153, 123 153, tanger [phone1 get updatse with A as A wins, missing info gets merged]
[userid 4 gets remove as it doesn't exist in A]
5, mister D, 213 123, 123 435, london [new row gets added from A]

Entries from B not existing in A, should be removed as a matter of fact, A is considered the master for conflicts.

Altogether, this is not a simple copy-paste nor a regular merge. Can someone offer assistance?

Thanks !

PS: thinking about it, I understand access maybe more powerful. so if you can think of a solution where I can import those csv into a DB or DBs and the right queries to merge those, that would work for me as well.

5
  • 1
    This is the classical nemesis of Excel in the world - you mail it to x people and get x version back, each one changed differently. The recommended solution is to use a document management system or a database instead of Excel. - If you have a clear row key, you can write macros that handle all cases, but if you allow changes in every column, there is no automated solution possible, because there can be no automated match-up of records
    – Aganju
    Jan 14, 2016 at 21:24
  • is there not? I hear excel can do powerful query like stuff... actually this is more like System A vs System B. People only update System A but still want stuff from System B where it has to end up.
    – KitkatNeko
    Jan 14, 2016 at 21:32
  • You could copy and paste the two spreadsheets (A & B) to separate pages in a new workbook, then do a VLOOKUP on another page. Once you have the data then you can copy and paste values.
    – BillDOe
    Jan 14, 2016 at 21:49
  • But only if you have a valid key that still matches.
    – Aganju
    Jan 14, 2016 at 21:58
  • The good thing is that keys would match!
    – KitkatNeko
    Jan 14, 2016 at 22:03

1 Answer 1

0

Is the first row "userid" the persistent identifier? Assuming, yes. Can it happen that the name changes and the identifier remains? Assuming, yes. Is it possible that different people get the same identifier? Assuming, no.

So you don't have to merge people from both spreadsheets, you simply ignore people from Spreadsheet B not present in Spreadsheet A. In other words, you retain the list of people in Spreadsheet A, and only update their properties if they are present in Spreadsheet B as well.

Then the task becomes quite straightforward. The resulting spreadsheet has a one-to-one correspondence to Spreadsheet A, and you can use VLOOKUP to find the corresponding entries in Spreadsheet B and then use IF and ISBLANK to update values.

Assuming that both spreadsheets contain data on Sheet1, are saved to different files and opened in Excel, and a new spreadsheet is created.

Fill the first row with titles same as in Spreadsheet A.

Then, enter the following formulas:

Cell A2:

='[Spreadsheet A.xlsx]Sheet1'!A2

Cell B2:

=IF(ISBLANK('[Spreadsheet A.xlsx]Sheet1'!B2);VLOOKUP($A2;'[Spreadsheet B.xlsx]Sheet1'!$A:$E;COLUMN());'[Spreadsheet A.xlsx]Sheet1'!B2)

Using the fill handle, copy the B2 formula to all cells to the right. After that, select the second row and use the fill handle to copy the resulting formulas to subsequent rows below. Rows exceeding the number of entries in Spreadsheet A will be filled with #N/A. If you wish, another IF can be added to replace errors to blank spaces.

1
  • I eventually added a new row for mitigation winner and did as suggested by Goblin Alchemist using many vlookup to look for the right references in the spreadsheets.
    – KitkatNeko
    Jan 15, 2016 at 15:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .