Need help with merging rows by matching unique identifier.

[lunatyck]

Hello all.

I have a problem that I need help getting resolved. I have two large sets of data (50k+ lines) on one, and another with 6k lines. Let's call these sheets A and B respectively. Both sheets are organized in the same way with the same amount of columns (A-AW columns), etc. There are some matching unique identifiers among the two sheets, but I am having difficulty merging these matches due to the large amount of data. For example, Sheet A doesn't have some of the data from Sheet B for the same identifier. If i just copy paste both sheets into one sheet, I will have duplicates. If i remove duplicates, I lose some of the data that is missing from either sheet. I am looking for a way to merge these two sheets into one sheet that can take matching identifiers and create 1 consolidated row that fills in any missing data into sheet A data from B, and copies any rows of data where there is no duplicate identifier from B into A. I hope that is clear.

In Sheet 1 of my attachment, you will see what I mean by merging (Column A is not part of the sheets, just there for labeling). Also, if there is data in both Sheet A and Sheet B for a column, have sheet A win and copy that information into the merged row. Otherwise, if there is no match between sheet A and B for a unique identifier, then simply copy the non-matching rows into a consolidated sheet.

mergeHelp.xlsx

Please help me or let me know if this is possible. This is too much data to try to perform by vlookup.

Thank you!

[AB33]

Luna,
There are not any data in sheet 2. It is difficult to test the code if there are not data. Could you please put in some data and show a desired result?

[lunatyck]

Luna,
There are not any data in sheet 2. It is difficult to test the code if there are not data. Could you please put in some data and show a desired result?

Attached the data you requested:
testData.xlsx

[AB33]

Thanks! Now I need to understand your data. The two sheets are almost identical, except, column "A" in sheet 1 is blank. Which column is the unique identifier? Which column/s do you want to look, or compare with and which column/s do you want to return?

[jindon]

lunatyck

The attached should do what you want.

[lunatyck]

Attached the data you requested:
Attachment 190405

Column D labeled "merged" is the unique ID

Edit: I know that Column A is missing in sheet B. This is why I need the merge between the two sheets. Sheet A has majority of the data, but it still needs some data from sheet B.

[lunatyck]

lunatyck

The attached should do what you want.

Hey Jindon.

This looks like it's working! However, I am not sure how I would run this for my full list of data. How do I set this up so it runs on my spreadsheets of 50k+ lines? Thanks!!

[jindon]

Replace the code with the following for the latest attachment.

Please Login or Register  to view this content.

[lunatyck]

replace the code with the following for the latest attachment.

Please Login or Register  to view this content.

thank you!