Extract Unique Text Values based on Multiple Criteria

Hi,

I am new to this forum, having lurked for some time - but have finally hit a wall in which I cannot seem to rely on any old post solving my issues. I hope someone will be able to help !

My Issue:
I have a set of data that contains <1500 rows of individual products, which all belong to a certain BRAND and have corresponding columns stating which TYPE1 and TYPE2 of a product it is, if it is BIB or NON BIB etc, what SIZE it has (8 different possibilities). The different ranges (columns) are named in excel, via a nifty vba code I found - which names the ranges based on the value column header.

In the most basic form, my need is to be able to specify values of TYPE1, TYPE2, BIB and SIZE and have excel return the brands that pertains to these criteria. If it is possible in ranked order - based on the summation of the ValSal1 values of that brand's products that fulfill the criteria.

The solution I figure must either be a Array Formula or a VBA code that populates it.

I hope this makes sense. I have included a sample workbook (or at least I hope so)

=IF(($B$2:$B$12=B16)*($C$2:$C$12=B17)*($F$2:$F$12=B18),D2:D12,"") option-1 in b20
=INDEX($D$2:$D$12,MATCH($B$16&$B$17&$B$18,$B$2:$B$12&$C$2:$C$12&$F$2:$F$12,0)) option-2 in b20 but not based on ranked order.

Punnam

Thank you gentlemen for replying so quickly.
@Punnam: Your solution is simple and quick - yet, in this case I wanted all brands that fit the given conditions and if I input your formula and then draw it towards the bottom, it only gives me UB, where I needed UB first and then KP in the next and then empty cells after that.

@nflsales
Thank you for providing the answer in a file. I have succesfully applied your formula with my 'real life example' (e.g. using the correct names from the named range).
However, what I need is just one instance of any brand that has products that meets the criteria. If I change the last figure in your formula to 5, I can get it to list the BRAND - however, if I draw it towards the bottom, it gives me an instance of the Brand name for each of the products it has that meets the criteria. I only need one instance of each brand that has a product that meets the criteria, and then empty cells from there.

The adapted formula: =IFERROR(INDEX(myData;MATCH(SMALL(IF((TYPE1=$B$492)*(TYPE2=$C$492)*(BIB=$B$494);ValSal1+ROW(ValSal1)*0,00000000001);ROW(2:2));IF((TYPE1=$B$492)*(TYPE2=$C$492)*(BIB=$B$494);ValSal1+ROW(ValSal1)*0,00000000001);0);5);"")

Thanks again - much appreciated !
Soren

@ Kattenhove ,

Provide your expected results for a particular case in the solution provided by nflsales.

Punnam

Yes of course - thanks again Punnam for followin up.

It is included. The main issue is as stated that I need only available brands listed, not individual products. If in ranked order, then simply perfect.
The file should show my wishes better and below is the formula I adapted to the "real life setting" from first round of help (nflsales)

Kindly
Soren

Hi XOR LX
Thanks for joining - sorry to now have provided a better example - I hope v3 does the trick.
The table was filtered so I was sure what the expected result was !

Hi,

as per the data provided in column G , i didn't found value 605 & 540,

But found 500,350,300 40

Punnam

@XOR LX
The expected result was is in this instance, given the conditions in B19:B21 A32: KK A33: JOHN

@Punnam
It's because the products pertaining to the brand KK has a combined value of 605 (B34+B35)and 540 for JOHN (B32+B35).
At the moment the formulae is finding the individual products and listing their brand names in A32:A35. I need it to list the BRANDS involved in the category, not the products - if at all possible in a ranked manner.
E.g the expected result here is that A32=KK (Since on a brand level, the combined value of the products that is listed with this brand has the biggest ValSal1, 605) Then A33=JOHN, since it is a brand that has products in the category, but listed second, as the combined value of its products in the category is 540 (e.g. <605)

Hope this clarifies it! Thanks again !

This is perfect nflsales! Thank you so much! Much appreciated - to all of you!