Vlookup/Index that returns all distinct values, but returns in a row?

[Dogboydan]

I'm looking for help setting up a formula that will allow me to have all distinct values for a lookup value in a vertical dataset, but I need the output to be in a horizontal output. The lookup range is a vertical set of emails, with the same email appearing multiple times, but potentially having the same or different results in the columns next to it. What I need is all distinct values related to that email from a column next to it, but returned in a single row.

I have found two array based formulas that accomplish to parts of this, but I can't seem to figure out a way to get it to do it together.

Distinct Values formula:
=IFERROR(INDEX('BL Department'!$G$2:$G$10000,MATCH(0,COUNTIF($M$4:M4,'BL Department'!$G$2:$G$10000)+('BL Department'!$A$2:$A$10000<>$A$5),0)),"")
or
IFERROR(INDEX(return_range, MATCH(0,COUNTIF(column_range, return_range)+(lookup_range<>lookup_value),0)),"")

This formula does what I want in that it returns only the distinct values, but it returns them in a column format.

Row All Results formula:
=IFERROR(INDEX('BL Department'!$G$2:$G$10000,SMALL(IF($A5='BL Department'!$A$2:$A$10000,ROW('BL Department'!$G$2:$G$10000)- MIN(ROW('BL Department'!$G$2:$G$10000))+1,""),COLUMN()-6)), "")
or
IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(lookup_range) - MIN(ROW( lookup_range))+1,""),COLUMN() - n)), "")

This formula returns the values in a row, but returns all of them.

Is there a way to modify the distinct values formula so that it returns on a row rather than a column?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

What version of excel are you using? Please update your profile so the forum can give an answer suitable to the version you have.
Also, if you can provide a small sample of data and an expected output, it would be of help.

Mr. Excel has a tool called xl2bb add in that allows you to post mini worksheets of your workbook. Liink below. If you cannot use the add in, then please post a table.

 

[Dogboydan]

Sorry! I'm using Excel 2016. I reminded myself to include that in the post, but then spaced.

 

[Fluff]

Thanks for that.
If you put your 1st formula in N4 & drag across, it should work.

 

[Dogboydan]

Hmm, that didn't seem to work sadly. I think because technically the formula is in M5 and N5 as for some reason it needs a to look above or it errors. I can't install the add-in due to restrictions, but I've made a sample table below of what the source data kinda looks like (Assume A1-A3 for headers).

Email	Department	Division
sampleemail@gmail.com	Sales	Appliances
sampleemail@gmail.com	Sales	Tech
sampleemail@gmail.com	Repair	Computer
anotheremail@gmail.com	Sales	Vacuums
anotheremail@gmail.com	Management	Floor
anotheremail@gmail.com	Management	Warehouse

Expected output results would be something like:

Email	Department 1	Department 2	Division 1	Division 2	Division 3
sampleemail@gmail.com	Sales	Repair	Appliances	Tech	Computer
anotheremail@gmail.com	Sales	Management	Vacuums	Floor	Warehouse

 

[Fluff]

If the formula is in M5 change the $M$4:M4 to $L$5:L5

 

[Dogboydan]

If the formula is in M5 change the $M$4:M4 to $L$5:L5

Thank you, that worked! I always am amazed at how simple the solutions are after smashing my head against it for so long haha.

 

[Fluff]

You're welcome & thanks for the feedback.