Create Comma Separated List In Excel

0
votes

I have two workbooks that I need to pull data from workbook1, to workbook2. The identifier to achieve such is empID Now for eachempID I need to show what location(s) they worked. So sample data looks like this
Workbook1
empID.....Name....Address...City...State....Zip
1
2
3
4
5

Workbook2
empID.......locationworked
1 12
2 33
1 11
4 22
3 9
1 55
5 19
2 76
1 99

I have used this formula to return the data to a different cell for each empID

=IFERROR(INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1))),"" )

But I want to create a Comma Separated list and put everything in one cell, like so

1   11,12,55,99
2   33,76

etc

Is there a way to modify the syntax so that a comma separated list is created like in my desired output?

2
excelexcel-formulaformulaexcel-2013worksheet-function
If you have access to Excel 2016, you could use =TEXTJOIN(",",TRUE,TRANSPOSE(IF(Empl=A2,Location,""))), entered with ctrl + shift + enter in column C of Workbook 2 (assuming your data is in columns A & B; then filter to just show the unique Emp ID's, but not sure how to do it in 2013 - Ron Rosenfeld
@RonRosenfeld - unfortunately I do not have access to Excel 2016. - StarsFlyFree FromCozyNights

2 Answers

1
votes

In workbook 2, I added this formula to column C 

=IFERROR(VLOOKUP(A1,A2:$C$50,3,0)&","&B1,B1).

This assumes that your data goes as far down as row 50. Replace $C$50 with whatever row is last in your spreadsheet.

If this is a variable list, use 

=INDIRECT("A2:C"&MATCH(TRUE,D:D="",0),1)

in place of the

A2:$C$50

however don't forget to use Ctrl + Shift + Enter to set the formula to an array.

Next, copy this formula down all rows. The VLOOKUP will work up the sheet. Then you can reference this list from your report sheet (I believe in this case its Sheet 1) with a VLOOKUP. it will automatically pick the first instance of each employee ID which contains the csv list.

0
votes

I'd like to point out that whilst bad_neighbor's solution is quite accurate and reusable for future data changes, it is often preferable to avoid lookups where possible, and to store calculated results as values, since these aren't perfectly efficient and tend to slow down the sheet something awful given a larger quantity of data, for example when filtering / unfiltering. It's worse in older versions.

So, if this list formatting were part of a manual operation, and assuming the requirement is for each list item to be in ascending order (per the question's output), I'd do the following instead:

  1. If workbook2's order is important, add an index of the rows (D1 := 1; D2 := D1 + 1; paste values).
  2. Sort workbook2 by [A ascending, B descending], including index if present.
  3. Apply this formula to column C - a fillup version of the lookup.
C1 := IF(A1=A2,C2&", "&B1,B1)
  1. Copy-paste special values column C.
  2. Lookup from workbook1 + copy-paste special values.
  3. Optionally sort back according to original index (D) in workbook2.