List Multiple Occurrences and then move one, Repeat Name as Many Times as it Exists

[budfox310]

Hope I describe this well enough, but I've attached an example file.

I have a large data set of every employee and the locations they work at, some employees work at multiple locations. I want a formula that will pull in all the employees associated with a location entered in a cell, which I know how to do, but I also want to list all the locations that those employees work out of which I don't know how to do. So I need a formula that will repeat the employee # as many times as it appears in the data set and then go on to the next employee, and also a formula that will do the same for the locations associated with the employee (like an index match that won't repeat).

Data Set
Employee ID # Location #
123 A
123 B
150 C
160 C
170 A
170 B
170 C
180 C

Pull In all Employees at A (I use = Small(IF(Location = A, Empolyees),Row()-Row())
123
170

All Locations that Employees at A work at
123 A
123 B
170 A
170 B
170 C

[CARACALLA]

E4=IFERROR(AGGREGATE(15,6,$B$4:$B$100/($C$4:$C$100=$E$2),ROWS($E$4:E4)),"")

Copy down


I4=IFERROR(INDEX(B$4:B$100,AGGREGATE(15,6,ROW($B$4:$B$100)-ROW(B$4)+1/(ISNUMBER(MATCH($B$4:$B$100,$E$4:$E$12,0))),ROWS($B$4:B4))),"")

Copy across and down


formulas is manual in your Excel change in automatic

[ChemistB]

i used a helper column F. Set F3 = 0. In F4

Please Login or Register  to view this content.

In K4 copied down

Please Login or Register  to view this content.

In L4 copied down

Please Login or Register  to view this content.

[budfox310]

Wow, this is great, I've never used the aggregate function before so I'll need to figure out exactly how it works. The data is on a different tab with 5000 rows so I just switched the cell references accordingly and it works well.