Multiple cell concatenation based on lookup value

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have a worksheet with a list of names and an integer of
anywhere between 1 to 50 next to them, I have a worksheet
that has a column with cells 1 to 50; I want to
concatenate names that have the same integer into the
appropriate cell.

eg.

Worksheet 1.
John 1
Mary 3
Dave 1
Matt 3
Dean 1

Worksheet 2.
1 John, Dave, Dean
2
3 Mary, Matt
4
5

I would really appreciate some help on this,

Regards,
Rob.
 
Rob

How about a User Defined Function (UDF). If your names are
in Column A and the Number in column B, then varRange will
cover the items in Column B. If you put this function in
row 1 of the output sheet, then copy down it will pick up
the correct row.

Tony

Function Grouper(varRange, y)
Grouper = ""
First = True
For Each ce In varRange
If Not First And ce = y Then
Grouper = Grouper & ", " & ce.Offset(0, -1).Value
End If
If First And ce = y Then
Grouper = ce.Offset(0, -1).Value
First = False
End If

Next ce

End Function
 
Rob said:
. . . I want to concatenate names that have the same integer
into the appropriate cell.

eg.

Worksheet 1.
John 1
Mary 3
Dave 1
Matt 3
Dean 1

Worksheet 2.
1 John, Dave, Dean
2
3 Mary, Matt
4
5
Click to expand...

This sort of thing can't be done *generally* without some sort of add-in
function because Excel provides no array/range consolidating concatenation
operator or function.

The best solution involves downloading and installing Laurent Longre's
MOREFUNC.XLL add-in, available at

http://longre.free.fr/english/

It provides many useful functions, but its MCONCAT functions is needed for
this task. If the two-column range in the first worksheet were named Src,
and your list of numbers were in A1:A50 in the second worksheet, then enter
this array formula in cell B1 of the second worksheet.

=SUBSTITUTE(MCONCAT(IF(INDEX(Src,0,2)=A1,", "&INDEX(Src,0,1),"")),", ","",1)

Then fill down into B2:B50.

If you can't use add-ins, VBA would be next best. If you can't use VBA,
you're stuck with monster array formulas like (for B1)

=IF(COUNTIF(INDEX(Src,0,2),A1)<1,"",INDEX(Src,
MATCH(A1,INDEX(Src,0,2),0),1))
&IF(COUNTIF(INDEX(Src,0,2),A1)<2,"",", "&INDEX(Src,
SMALL(IF(INDEX(Src,0,2)=A1,ROW(Src)-CELL("Row",Src)+1,1000000),2),1))
&IF(COUNTIF(INDEX(Src,0,2),A1)<3,"",", "&INDEX(Src,
SMALL(IF(INDEX(Src,0,2)=A1,ROW(Src)-CELL("Row",Src)+1,1000000),3),1))
&IF(COUNTIF(INDEX(Src,0,2),A1)<4,"",", "&INDEX(Src,
SMALL(IF(INDEX(Src,0,2)=A1,ROW(Src)-CELL("Row",Src)+1,1000000),4),1))
&IF(COUNTIF(INDEX(Src,0,2),A1)<5,"",", "&INDEX(Src,
SMALL(IF(INDEX(Src,0,2)=A1,ROW(Src)-CELL("Row",Src)+1,1000000),5),1))

and this only pulls the first 5 corresponding names.
 
Rob,

If you don't want to use an add-in or VBA, another way would be to have 50
additional columns (extreme I know), set up as follows:

In row 1 (starting in column C) put the numbers 1 to 50. In row 2 use this
formula:

=IF($A2=C$1,$B2,"")

In the remaining cells (C3:AZ500 or whatever) use this one:

=C2&IF($A3=C$1,IF(LEN(C2)," ","")&$B3,"")

Now, on sheet 2, against your integers, use this array formula (hold
Ctrl+Shift when pressing Enter)

=OFFSET(Sheet1!$B$2,MAX(IF(Sheet1!$A$2:$A$101=$A2,
ROW(Sheet1!$A$2:$A$101)-CELL("ROW",Sheet1!$A$2:$A$101))),$A2)


HTH
Steve D.
 
Back
Top