• 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

    Hui...

  • 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

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

  • Question Sample.xlsx
    10.4 KB · Views: 8
Hi,

There is a chance you have the dynamic array functions available.
=UNIQUE(A3:B14,FALSE,FALSE) will spill the table you are after.
 
Select your range - Data Tab - Data Tools grouyp - remove duplicates - Check as necessary - OK -Done
 
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

  • Chandoo44718_Question Sample.xlsx
    11.8 KB · Views: 5
Hi Guido
A similar approach but with all the bits and pieces decently out of sight within Named Formulas!
70152
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

  • OldSchoolUnique.xlsx
    11.6 KB · Views: 4
Back
Top