Sum If and Concatenate

[MikeBerlanguet]

Hi,

Probably a simple solution but here is the question:

I want to concatenate text in cells that are in the " sum range" of a Sum If formula. I would like excel to take the names from Team #1 (column B) and put them in a single cell: glen, paul, mike Thought a sumIf might do it but can't seem to get it right. Any suggestions?

[TABLE="width: 224"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: center"]A
Team #[/TD]
[TD="align: center"]B
Player[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]glen[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]paul[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Mike[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

You may need to use a Custom Function
Have a look here for the UDF
AND
a sample workbook to play with


Get Multiple Lookup Values in a Single Cell in Excel

 

[Aladin Akyurek]

Also:

1. You can try a formula with TEXTJOIN on a 2016 system.

2. Run a generic UDF like ACONCAT for which you can find a lot of posts where it is used.

3. Or run the Hong Kong method:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]Team #[/td][td]Player[/td][td]concat[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]glen[/td][td], glen, paul, mike[/td][/tr]
[tr][td]

3​

[/td][td]

1​

[/td][td]paul[/td][td], paul, mike[/td][/tr]
[tr][td]

4​

[/td][td]

1​

[/td][td]mike[/td][td], mike[/td][/tr]
[tr][td]

5​

[/td][td]

2​

[/td][td]dan[/td][td], dan, damon[/td][/tr]
[tr][td]

6​

[/td][td]

2​

[/td][td]damon[/td][td], damon[/td][/tr]
[tr][td]

7​

[/td][td="bgcolor:#D0CECE"][/td][td="bgcolor:#D0CECE"][/td][td="bgcolor:#D0CECE"][/td][/tr]
[tr][td]

8​

[/td][td][/td][td]

1​

[/td][td]glen, paul, mike[/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In C2 enter and copy down:

=", "&$B2&IFERROR(VLOOKUP($A2,CHOOSE({1,2},$A3:$A$7,$C3:$C$7),2,0),"")

In C8 enter:

=REPLACE(VLOOKUP(B8,$A$2:$C$6,3,0),1,2,"")

which is the result cell.

 

[tygrrboi]

If you do not have a LOT of cells and you do not mind a long formula..

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}span.s7 {color: #fe4fdd}</style>=TRIM(CONCATENATE(
IF(A1=ROWS($A$1:$A1),B1,"")," ",
IF(A2=ROWS($A$1:$A1),B2,"")," ",
IF(A3=ROWS($A$1:$A1),B3,"")," ",
IF(A4=ROWS($A$1:$A1),B4,"")," ",
IF(A5=ROWS($A$1:$A1),B5,"")," ",
IF(A6=ROWS($A$1:$A1),B6,"")))


If you drag this formula down, the next cell will list all people from team 2, and team 3 etc.
but you do need to copy and paste the if function for each name you need to consider (each person in column B)

=TRIM(CONCATENATE(
IF(A1=ROWS($A$1:$A1),B1,"")," ",
IF(A2=ROWS($A$1:$A1),B2,"")," ",
IF(A3=ROWS($A$1:$A1),B3,"")," ",
IF(A4=ROWS($A$1:$A1),B4,"")," ",
IF(A5=ROWS($A$1:$A1),B5,"")," ",
IF(A6=ROWS($A$1:$A1),B6,"")," ",
IF(A7=ROWS($A$1:$A1),B7,"")," ",
IF(A8=ROWS($A$1:$A1),B8,"")," ",
IF(A9=ROWS($A$1:$A1),B9,"")," ",
IF(A10=ROWS($A$1:$A1),B10,"")," ",
IF(A11=ROWS($A$1:$A1),B11,"")," ",
IF(A12=ROWS($A$1:$A1),B12,"")," ",
IF(A13=ROWS($A$1:$A1),B13,"")," ",
IF(A14=ROWS($A$1:$A1),B14,"")," ",
IF(A15=ROWS($A$1:$A1),B15,"")," ",
IF(A16=ROWS($A$1:$A1),B16,"")," ",
IF(A17=ROWS($A$1:$A1),B17,"")," ",
IF(A18=ROWS($A$1:$A1),B18,"")," ",
IF(A19=ROWS($A$1:$A1),B19,"")," ",
IF(A20=ROWS($A$1:$A1),B20,"")," ",
IF(A21=ROWS($A$1:$A1),B21,"")," ",
IF(A22=ROWS($A$1:$A1),B22,"")," ",
IF(A23=ROWS($A$1:$A1),B23,"")," ",
IF(A24=ROWS($A$1:$A1),B24,"")))

for example.