Add cell string to another cell if 2 cells are the

Question

I'm trying to make a macro that will go through a spreadsheet, and based on the first and last name being the same for 2 rows, add the contents of an ethnicity column to the first row.

eg.

FirstN|LastN |Ethnicity                                       |ID      |

Sally |Smith |Caucasian                                       |55555   |
Sally |Smith |Native American                                 |        |
Sally |Smith |Black/African American                          |        | 

(after the macro runs)

Sally |Smith |Caucasian/Native American/Black/African American|55555   |

Any suggestions on how to do this? I read several different methods for VBA but have gotten confused as to what way would work to create this macro.

EDIT There may be more than 2 rows that need to be combined, and the lower row(s) need to be deleted or removed some how.

Answer 1

If you can use a formula, then you can do those:

Couple of assumptions I'm making:

• Sally is in cell A2 (there are headers in row 1).
• No person has more than 2 ethnicities.

Now, for the steps:

1. Put a filter and sort by name and surname. This provides for any person having their names separated. (i.e. if there is a 'Sally Smith' at the top, there are no more 'Sally Smith' somewhere down in the sheet after different people).
2. In column D, put the formula =if(and(A2=A3,B2=B3),C2&"/"&C3,"")
3. Extend the filter to column D and filter out all the blanks.

That is does is it sees whether the names cells A2 and A3 are equal (names are the same), and whether the cells B2 and B3 are equal (surnames are the same).

If both are true, it's the same person, so we concatenate (using & is another way to concatenate besides using concatenate()) the two ethnicities.

Otherwise, if either the name, or username, or both are different, leave as blank.

To delete the redundant rows altogether, copy/paste values on column D, filter on the blank cells in column D and delete. Sort afterwards.

EDIT: As per edit of question:

The new steps:

1. Put a filter and sort by name and surname. (already explained above)
2. In column E, put the formula =IF(AND(A1=A2,B1=B2),E1&"/"&C2,C2) (I changed the formula to adapt to the new method)
3. In column F, put the formula =if(and(A1=A2,B1=B2),F1+1,1)
4. In column G, put the formula =if(F3<F2,1,0)
5. In column H, put the formula =if(and(D2="",A1=A2,B1=B2),H1,D2) (this takes the ID wherever it goes).

Put the formulae as from row 2. What step 3 does is putting an incremental number for the people with same name.

What step 4 does is checking for when the column F goes back to 1. This will identify your 'final rows to be kept'.

Here's my output from those formulae:

The green rows are what you keep (notice that there is 1 in column G that allows you to quickly spot them), and the columns A, B, C, E and H are the columns you keep in the final sheet. Don't forget to copy/paste values once you are done with the formulae and before deleting rows!

Answer 2

If first Sally is in A1 then =IF(AND(A1=A2,B1=B2),C1&"/"&C2,"")copied down as appropriate might suit. Assumes where not the same a blank ("") is preferred to repetition of the C value.