Merging two excel rows that have same data or differing data in columns

0
votes

I have the following issue in excel. I have a spreadsheet with data sorted by Visit ID and would like to combine the rows that have the same Visit ID. The problem is this:

12345 value (column1) value (column2) value(column3)

12345 no value (column 1) value (column2) no value (column3)

The rows have columns that are either in common with the same value or they have columns where one row has the information and the other has a blank.

How do I merge 12345 IDs to have all columns populated? Thanks!

1
excelms-accessmerge
Trying to clarify: you may have multiple rows with the same ID that have incomplete information- but if there is information in a particular column for that ID, it is always the same? Is this data numerical , text, mixed? - Floris

1 Answers

0
votes

I would imagine that you also have another issue, that you may or may not know about yet. Not only do you have missing values you have mismatched values. Sheet1 has a value for 12346 in C2 that is different then Sheet2 12346 C2. The only way around this is human decision making, your goal is to automate as much as possible and then get down to the stuff you have to do by hand.

Sheet1

ID  |C1 |C2 |C3
12345   |The    |   |Jumped
12346   |   |Quick  |
12347   |   |   |Brown
12348   |Fox    |   |

Sheet2

ID  |C1 C2  C3
12345   |   |   |Jumped
12346   |The    |   |
12347   |   |Quick  |Brown
12354   |Fox    |   |

To preserve your raw data, work on sheet3 as the location for your combined data set

  • Copy the ID column from both sheets to Sheet3
  • Use remove duplicates to leave one of each ID, and sort them

Sheet3

ID
12345
12346
12347
12348
12354

  • Use Vlookup to transfer over the values from Sheets 1 & 2

    =VLOOKUP(A2,Sheet1!A:D,2,FALSE)

Sheet3

ID  |S1C1   |S1C2   |S1C3   |S2C1   |S2C2   |S3C3
12345   |The    |0  |Jumped |0  |0  |Jumped
12346   |0  |Quick  |0  |The    |0  |0
12347   |0  |0  |Brown  |0  |Quick  |Brown
12348   |Fox    |0  |0  #N/A    #N/A    #N/A
12354   #N/A    #N/A    #N/A    |Fox    |0  |0

Copy and paste all of sheet3 over it's self to convert the formulas to values (very important)

Now create new joined columns and use formula to identify and move over matching values.

Modifications of this formula will let you piece together the rest of the work.

=IF(B2=E2,B2,"no match")

Screen shot