Excel concatenate the column based on other column

问题:

 I have data in excel like this 

   A         B      C

   p1       ABC    2
   p2wt     ABC    3
   p3       EFG    1
   p3wtke   EFG    1
   p9r      EFG    2

I'm trying to sum up C column if B column has same data and concatenate A column data. And data looks like

    A             B       C

   p1-p2wt       ABC     5      
   p3-p3wtke-p9r EFG     4

I tried using =SUMIFS(C1:C5,A1:A9,B1) but it is giving #value, and how do i concate A? I tried Excel concatenate, but is A2>A1 in this link is for number fields?

回答1:

Use a helper column.

In D1 put:

=IF(B1<>B2,A1,A1&"-"&D2)

Then copy/drag down the data set.

The in another column create a list of the unique values in column B. I put mine in G.

Then in F1 I put:

=VLOOKUP(G1,B:D,3,FALSE)

And in H1 I put:

=SUMIF(B:B,G1,C:C)

Then copies/dragged down.