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
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
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
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
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),"")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
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?
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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks