Return Multiple matches horizontally from vertical data

[AllMB]

I am working in Excel 365.

I have a list of names that work at the same location and other work at multiple locations. I need to return the locations horizontally based on the name. I will then run a basic function to find those that are working at different locations.

If possible could the formula only return unique values and not duplicates? ie, those working at the same location only return one value and only return multiple values if they work at several locations.

I have worked with iferror, index, aggregate and running into issues since I could not get the filter function to work. Below is an example of what I would be working with but with up to 5,000 rows.

Anything you can do to help point me in the right direction would be appreciated.

 

[Fluff]

Can you post your data, including expected results, using the XL2BB add-in.
Also when you say you cant get the filter function to work, do you mean you don't yet have that function, or you couldn't get it to give the correct result?

 

[AllMB]

Fluff,

I can post the expected results but the data is considered sensitive (the return value would be a number). I don't think the filter function is on/in excel for our work computers. Getting it added by IT would be ridiculous since any changes have to be approved by IT.

Below is what the results should look like, hopefully.

 

[Fluff]

I was asking you to post your sample data, not your actual data & the reason for using the XL2BB add-in is to save me having to recreate your data.

If you type =uni into a cell is one of the options UNIQUE?

 

[AllMB]

I was asking you to post your sample data, not your actual data & the reason for using the XL2BB add-in is to save me having to recreate your data.

If you type =uni into a cell is one of the options UNIQUE?

Unique is not one of the formula options I have available. Let me get on my personal computer and create the data quickly.

 

[Fluff]

No need I have done it, as there wasn't much to create
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1				Name	Location	Location	Location	Location	Location
2	Name 1	Location 10		Name 1	Location 10	Location 11	Location 12
3	Name 1	Location 10		Name 2	Location 10
4	Name 1	Location 11		Name 3	Location 11
5	Name 1	Location 12		Name 4	Location 15
6	Name 2	Location 10
7	Name 2	Location 10
8	Name 2	Location 10
9	Name 2	Location 10
10	Name 3	Location 11
11	Name 3	Location 11
12	Name 3	Location 11
13	Name 4	Location 15
14	Name 4	Location 15
15	Name 4	Location 15
16
Data

Cell Formulas
Range	Formula
E2:I5	E2	=IFERROR(INDEX($B$2:$B$50,AGGREGATE(15,6,(ROW($A$2:$A$50)-ROW($A$2)+1)/($A$2:$A$50=$D2)/ISNA(MATCH($B$2:$B$50,$D2:D2,0)),1)),"")
D2:D10	D2	=IFERROR(INDEX($A$2:$A$50,AGGREGATE(15,6,(ROW($A$2:$A$50)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$50,$D$1:$D1,0))*($A$2:$A$50<>"")),1)),"")

 

[AllMB]

Fluffy,

It worked on the test data, I will now apply it to the real data and let you know how it goes.

 

[Fluff]

 

[AllMB]

Fluffy,


Worked perfectly. I will have to do some more research into the aggregate function.

 

[Fluff]

You're welcome & thanks for the feedback.