How to join a list of data from rows to a single cell.

**kaknoche** · 05-24-2013, 04:32 PM

I have a list of Supervisor Codes that I need to join into a single cell.

column A column B
272111 2011
272111 2056
272111 2059
272325 2056
272325 2058
275456 1125
275456 2056

column A New Column
272111 2011; 2056; 2059
272325 2056; 2058
275456 1125; 2056

Any help would be greatly appreciated!!

Thanks

Kathy

**JBeaucaire** · 05-24-2013, 05:13 PM

1) Put titles in A1:B1 for "Supervisor", "Codes"

2) Drop the data in A2:B2 downward

3) Put this formula in C3 and copy the cell downward:
=IF(A2=A3, B2&";"&C3,B2)

4) Copy column C, then do a PASTE SPECIAL > VALUES to remove the formulas.

5) Put this formula in D3 and copy the cell downward:
=IF(COUNTIF(A1:A2,A2)=1, "keep", "delete")

6) Highlight columnD and turn on the Data > Filter

7) Filter column D to show "delete" only

8) Highlight all visible rows and delete them

9) Delete columns C:D

**kaknoche** · 05-24-2013, 05:43 PM

Thank you so much!! This worked

Originally Posted by JBeaucaire

1) Put titles in A1:B1 for "Supervisor", "Codes"

2) Drop the data in A2:B2 downward

3) Put this formula in C3 and copy the cell downward:
=IF(A2=A3, B2&";"&C3,B2)

4) Copy column C, then do a PASTE SPECIAL > VALUES to remove the formulas.

5) Put this formula in D3 and copy the cell downward:
=IF(COUNTIF(A1:A2,A2)=1, "keep", "delete")

6) Highlight columnD and turn on the Data > Filter

7) Filter column D to show "delete" only

8) Highlight all visible rows and delete them

9) Delete columns C:D