Home

Return Multiple UNIQUE Match Values in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-533404%22%20slang%3D%22en-US%22%3EReturn%20Multiple%20UNIQUE%20Match%20Values%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533404%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20only%20imagine%20that%20this%20topic%20has%20been%20covered%20before.%20It%20is%20not%20the%20easiest%20to%20search%20down%20exactly%20what%20I%20need%2C%20so%20please%20forgive%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20followed%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F34444%22%20target%3D%22_blank%22%3E%40Leila%20Gharani%3C%2FA%3E's%20tutorial%20%22Return%20Multiple%20Match%20Values%20in%20Excel%22%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Freturn-multiple-match-values-in-excel%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%5B%20LINK%20%5D%3C%2FA%3E%2C%20and%20succeded%20in%20transforming%20it%20to%20fit%20into%20my%20scope.%20Thank%20you%20Leila%2C%20for%20a%20great%20video!%3C%2FP%3E%3CP%3ENow%2C%20I%20need%20to%20do%20a%20small%20adjustment%20to%20make%20sure%20the%20same%20value%20is%20not%20returned%20multiple%20times.%26nbsp%3BHere%20is%20my%20scope%3A%3CBR%20%2F%3EI%20have%20a%20Bill%20of%20Materials%2C%20where%20all%20material%20number%20has%20a%20reference%20designation.%20This%20is%20a%20sample%20of%20the%20list%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EReference%20designator%3C%2FTD%3E%3CTD%3EMaterial%20No.%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-U1%3C%2FTD%3E%3CTD%3E193819%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-U1%3C%2FTD%3E%3CTD%3E193666%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-V1%3C%2FTD%3E%3CTD%3E092008%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-V1%3C%2FTD%3E%3CTD%3E29082290%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-V1%3C%2FTD%3E%3CTD%3E29082290%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-V1%3C%2FTD%3E%3CTD%3E29082290%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-V1%3C%2FTD%3E%3CTD%3E1343567674%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-V1%3C%2FTD%3E%3CTD%3E092008%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-W1%3C%2FTD%3E%3CTD%3E29064039%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-W1%3C%2FTD%3E%3CTD%3E29064039%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-W2%3C%2FTD%3E%3CTD%3E29078487%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EAs%20you%20can%20see%2C%20some%20of%20the%20reference%20designations%20has%20multiple%20material%20numbers.%3C%2FP%3E%3CP%3ENow%2C%20in%20another%20sheet%2C%20I%20need%20to%20do%20a%20list%2C%20following%20this%20principle%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3E(A)%26nbsp%3BReference%20designator%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E(B)%26nbsp%3BMat.%20No.%20%231%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E(C)%26nbsp%3BMat.%20No.%20%232%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E(D)%26nbsp%3BMat.%20No.%20%233%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E-200-08-01-V1%3C%2FTD%3E%3CTD%3E092008%3C%2FTD%3E%3CTD%3E29082290%3C%2FTD%3E%3CTD%3E1343567674%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20succeeded%20in%20getting%20the%20first%20match%20from%20the%20top%20in%20column%20B%2C%20the%20second%20in%20column%20C%20and%20third%20in%20column%20D.%20But%20in%20this%20particular%20case%2C%20match%20number%202%20and%203%20from%20the%20top%20is%26nbsp%3B29082290.%20So%20I%20need%20the%20formula%20to%20include%20a%20check%20if%20the%20value%20in%20(C)%20is%20the%20same%20as%20(B)%20and%20then%20skip%20it%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20describes%20well%20enough%2C%20what%20I'm%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%2C%20I've%20included%20some%20sample%20data.%20In%20sheet%20%22Mapping%22%2C%20the%20relevant%20coloumns%20is%20marked%20with%20green%2C%20and%20the%20lookup%20is%20don%20in%20%22PDMLink%20BOM%22%2C%20coloum%20D%20%26amp%3B%20E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20very%20much%20forward%20to%20hear%20if%20any%20of%20you%20have%20any%20inputs!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3EMikael%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-533404%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
brusgaard
Occasional Visitor

Hi all,

 

I can only imagine that this topic has been covered before. It is not the easiest to search down exactly what I need, so please forgive me.

 

I have followed @Leila Gharani's tutorial "Return Multiple Match Values in Excel" [ LINK ], and succeded in transforming it to fit into my scope. Thank you Leila, for a great video!

Now, I need to do a small adjustment to make sure the same value is not returned multiple times. Here is my scope:
I have a Bill of Materials, where all material number has a reference designation. This is a sample of the list:

Reference designatorMaterial No.
-200-08-01-U1193819
-200-08-01-U1193666
-200-08-01-V1092008
-200-08-01-V129082290
-200-08-01-V129082290
-200-08-01-V129082290
-200-08-01-V11343567674
-200-08-01-V1092008
-200-08-01-W129064039
-200-08-01-W129064039
-200-08-01-W229078487

As you can see, some of the reference designations has multiple material numbers.

Now, in another sheet, I need to do a list, following this principle:

(A) Reference designator(B) Mat. No. #1(C) Mat. No. #2(D) Mat. No. #3
-200-08-01-V1092008290822901343567674

 

I have succeeded in getting the first match from the top in column B, the second in column C and third in column D. But in this particular case, match number 2 and 3 from the top is 29082290. So I need the formula to include a check if the value in (C) is the same as (B) and then skip it and so on.

 

I hope this describes well enough, what I'm trying to do.

 

In the attached, I've included some sample data. In sheet "Mapping", the relevant coloumns is marked with green, and the lookup is don in "PDMLink BOM", coloum D & E.

 

I'm looking very much forward to hear if any of you have any inputs!

 

Best regards,

Mikael

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
7 Replies