# Unique Values With Related Column

#### Kenshin

##### Member
Glad to see you again Ninjaz, Im here need assistance again for the unique values problem, please take a look at the file

thank you in advanced

#### Attachments

• 10.4 KB Views: 8

#### GraH - Guido

##### Well-Known Member
Hi,

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

#### Kenshin

##### Member
I dont have Office 365 so the formula doesnt work @GraH - Guido

#### pecoflyer

##### 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)

#### Attachments

• 11.8 KB Views: 5

#### 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!

#### Attachments

• 11.6 KB Views: 4

#### p45cal

##### Well-Known Member
Do you have to use formulae?
Advanced filter will do it in a jiffy: