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?
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.