Concatenate Rows if Duplicate Value

  • Thread starter Thread starter rrstudio2
  • Start date Start date
R

rrstudio2

I have a spreadsheet that has some duplicate ID values but with
different details in another column. When there are duplicate IDs, I
want to concatenate all the different details into one row so I just
have one row with all the information. For example,

ID State DesiredConcatentation
1 AZ AZ
2 CA CAAZ
2 AZ CAAZ
3 Fl FlCaAz
3 Ca FlCaAz
3 Az FlCaAz
4 Fl Fl

I have thought of some ways to make the last row or first row contain
the desired concatentation, but I can't figure out how to make all the
rows contain the same value.

Thanks,
Andrew V. Romero
 
I have a spreadsheet that has some duplicate ID values but with
different details in another column. When there are duplicate IDs, I
want to concatenate all the different details into one row so I just
have one row with all the information. For example,

ID State DesiredConcatentation
1 AZ AZ
2 CA CAAZ
2 AZ CAAZ
3 Fl FlCaAz
3 Ca FlCaAz
3 Az FlCaAz
4 Fl Fl

I have thought of some ways to make the last row or first row contain
the desired concatentation, but I can't figure out how to make all the
rows contain the same value.

Thanks,
Andrew V. Romero
Click to expand...

Here is a two step solution based on the fact that your IDs are sorted as
they are in your example:

Row A = Titles.
Col A = ID (A2 = 1, A3 = 2 etc)
Col B = State (B2 = AZ, B3 = CA etc)
Col C = Temp
Col D = DesiredConcatenation

C2 contains this formula: =IF(A2=A1,C1&B2,B2)
C3 =IF(A3=A2,C2&B3,B3)
etc.

D2 contains the formula: =IF(A2=A3,D3,C2)
D3 =IF(A3=A4,D4,C3)
etc

You can drag the formulae down to fill further cells.

This gives your desired concatenation for the values you provided.

Thomas
 
Back
Top