Combine row data based on Duplicate ID

ID-SSN City Pet1 Pet2 Pet3 Pet4
111-11-1111 Baltimore Dog Bird
111-11-1111 Cow
777-77-7777 Augusta Cat Horse
777-77-7777 Dog
777-77-7777 Cow

I am trying to MASH UP rows with duplicate ID-SSNs.
End result would be:

ID-SSN City Pet1 Pet2 Pet3 Pet4
111-11-1111 Baltimore Dog Cow Bird
777-77-7777 Augusta Cat Dog Horse Cow

Any advise. I'm really stuck. Tried some nested IF statements to no avail.
Any help is GREATLY APPRICIATED! Michelle

The sample above didn't post visually as I had hoped. The "cow" entry for 2nd line of 111-11-111 should show as being located in the Pet3 column.
For row 4 777-77-7777 Dog is located in Pet2 and row 5 777-77-777 Cow is located in Pet4. Hope I haven't confused you further..ugh.

Hello mlgill,

The attached workbook has the following macro attached to a button that runs it. I tried to cover as many possible scenarios that might arise. The code is rather long but is heavily commented.

The macro will allow and track multiple cities for one social security number. If there are more than 4 pets per SSN, the user is alerted, the cells are highlighted in yellow, and the macro stops so the data can be corrected.

An entry will not be recorded if it is the first entry for a SSN and there is no city. After a city has been entered, if the city is absent then the previous city for that SSN is used.

Please Login or Register  to view this content.