• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique Values With Related Column

GraH - Guido

Well-Known Member

There is a chance you have the dynamic array functions available.
=UNIQUE(A3:B14,FALSE,FALSE) will spill the table you are after.


Active Member
Select your range - Data Tab - Data Tools grouyp - remove duplicates - Check as necessary - OK -Done

GraH - Guido

Well-Known Member
I dont have Office 365 so the formula doesnt work @GraH - Guido
It was worth the try...

If you don't mind helper columns, I can get there using 2:
- [C3] = TEXTJOIN("|",FALSE,A3:B3) -> "joined values"
- [E3] = IFERROR(INDEX($C$3:$C$14, MATCH(0, COUNTIF($E$2:E2, $C$3:$C$14), 0)), "") -> unique list of "joined values"

Then extract before and after delimiter "|"
- [F3] = LEFT(E3,FIND("|",E3)-1)
- [G3] = 0+MID(E3,LEN(F3)+2,255)


Peter Bartholomew

Well-Known Member
Hi Guido
A similar approach but with all the bits and pieces decently out of sight within Named Formulas!
Similar to your solution in that it uses concatenation, but then I used MATCH rather than COUNTIF.
How I used to do it before salvation came in the form of Dynamic Arrays!