Formula to only show multiple occurances only once

[Raidon]

Hi all,

I am in search of a formula which will extract the name of a supplier, which appears multiple times in a list, only once.

Please have a look at the attached file if you are interested in helping.

Many thanks

Raidon

[benishiryo]

hi Raidon, try copying this into the formula bar of I18:
=IFERROR(INDEX($B$12:$B$42,MATCH(0,COUNTIF($H$17:H17,$B$12:$B$42),0)),"")

press CTRL + SHIFT + ENTER to confirm

[FDibbins]

try this. in H12:H42, use this..
=IF(B12=B11,"",1+MAX($H$11:H11))

then in I18 down, use this...

=IFERROR(INDEX($B$12:$H$42,MATCH(ROW(A1),$H$12:$H$42,0),1),"")

[Raidon]

Thank you both for your attempts.

FDibbins, your solution is very close, but not quite right, since the suppliers listed is not listed alphabetically, but listed in the order that they were originally entered unto the spreadsheet. Therefore your formula has a slight flaw if a supplier appears later in the list.

benishiryo, your array-formula seems to work, but only for the first supplier in the list. Also, I don't completely understand your formula. Why is the COUNTIF part searching for entries in column H, since column H does not contain any information or formulas?

[benishiryo]

sorry, change it to column I instead. i did a test using column H previously.
=IFERROR(INDEX($B$12:$B$42,MATCH(0,COUNTIF($I$17:I17,$B$12:$B$42),0)),"")

the COUNTIF will give you a bunch of 0s in the 1st place because i'm counting if I17 (a blank cell) appeared in column B. the results is:
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
so the MATCH of lookup_value "0" gives you the 1st position, which is "Itelligence"

the COUNTIF in I18 will now count if I17 & I18 appeared in column B. the results is:
{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
so it appeared twice & the "0" now is in the 2nd position. hence it returns "Genoa Metal Terminal" now.

does that help?

[Raidon]

sorry, change it to column I instead. i did a test using column H previously.
=IFERROR(INDEX($B$12:$B$42,MATCH(0,COUNTIF($I$17:I17,$B$12:$B$42),0)),"")

the COUNTIF will give you a bunch of 0s in the 1st place because i'm counting if I17 (a blank cell) appeared in column B. the results is:
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
so the MATCH of lookup_value "0" gives you the 1st position, which is "Itelligence"

the COUNTIF in I18 will now count if I17 & I18 appeared in column B. the results is:
{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
so it appeared twice & the "0" now is in the 2nd position. hence it returns "Genoa Metal Terminal" now.

does that help?

Genius! That works perfectly!! Thank you very much for the explanation. I appreciate it tremendously.

Raidon