Solution needed to create top list with criteria

[colinheslop1984]

[TABLE="width: 599"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Style Name[/TD]
[TD]Colour[/TD]
[TD]Units[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]STAR JUMPER[/TD]
[TD]BLU-NAVY[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]148.5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]HAYWOOD CARDIGAN[/TD]
[TD]YEL-GOLDEN HAZE[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]143.55[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]SUZIE SWING ROLL NECK[/TD]
[TD]BLK-PHANTOM[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]CHARLOTTE CASHMERE JUMPER[/TD]
[TD]PNK-SEASHELL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]ALICIA HOODIE X[/TD]
[TD]BLU-NAVY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]ALICIA HOODIE X[/TD]
[TD]BLU-NAVY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]ALICIA HOODIE X[/TD]
[TD]BLU-NAVY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]ALICIA HOODIE X[/TD]
[TD]BLU-NAVY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]HAYWOOD CARDIGAN[/TD]
[TD]PNK-ROSE ASH[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]49.5[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]HAYWOOD CARDIGAN[/TD]
[TD]PNK-ROSE ASH[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]49.5[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]LIZZIE CARDI[/TD]
[TD]GRN-BOTTLE GRN[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]HAYWOOD CARDIGAN[/TD]
[TD]NAT-IVORY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]44.55[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]HOLLY CHENILLE JUMPER[/TD]
[TD]PPL-DATE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]HOLLY CHENILLE JUMPER[/TD]
[TD]PPL-DATE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]CHARLOTTE CASHMERE JUMPER[/TD]
[TD]GRY-GREY MARL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]39.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am trying to create a top 15 data table like above only I have hit bit of a snag.

I started off in Column E (Value column) by using =LARGE(IF('1924W'!$M:$M='Womens Knitwear (2)'!$C$3,'1924W'!$AB:$AB),A6) to gather the top 15 values based on criteria (c3)

Next in column C (Colour) I have used =INDEX('1924W'!A:V,MATCH(1,('1924W'!M:M=C$3)*('1924W'!AB:AB='Womens Knitwear (2)'!E6),0),16) This matches the colour of an item to the sales value in column E, still based on criteria C3.

In column B I use =INDEX('1924W'!A:V,MATCH(1,('1924W'!M:M=C$3)*('1924W'!AB:AB='Womens Knitwear (2)'!E6),0),4) Similar to above, this matches the name of the item to the sales value, based on criteria C3.

The problem I am having is that Column B will tend to have names repeating because there are items with same name, so I somehow need column C to remove duplicate colour but only where an item name in column B is repeated.

AND

I only want column B to repeat the item name the true amount of times. In the table above you can see an item repeated 4 times because it is basing its criteria on things that aren't unique enough. It should only be twice in this example

What can I add / do differently to get the results I need? Please add into my formula or rewrite

 

[Aladin Akyurek]

Care to post the output that you require (without forwarding any formulas)?

 

[colinheslop1984]

[TABLE="width: 2175"]
<colgroup><col span="29"></colgroup><tbody>[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]DUNGAREE - MID BLUE[/TD]
[TD][/TD]
[TD]936503[/TD]
[TD][/TD]
[TD]DENIM[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]DEN-DENIM[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]59.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]59.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]WINTER WOODLAND LEGGING[/TD]
[TD][/TD]
[TD]937704[/TD]
[TD][/TD]
[TD]NIGHTWEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]BLU-NAVY[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]29.50[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]59.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]ORGANIC COTTON BRETON TEE[/TD]
[TD][/TD]
[TD]932758[/TD]
[TD][/TD]
[TD]JERSEY TOPS[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]BLU-MIDNIGHT[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]28.00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]56.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]ALICIA HOODIE X[/TD]
[TD][/TD]
[TD]935641[/TD]
[TD][/TD]
[TD]KNITWEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]BLU-NAVY[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]55.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]55.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]ALICIA HOODIE X[/TD]
[TD][/TD]
[TD]935641[/TD]
[TD][/TD]
[TD]KNITWEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]GRN-BOTTLE GRN[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]55.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]55.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]ANNABELLE EMBROIDERED JUMPER[/TD]
[TD][/TD]
[TD]935847[/TD]
[TD][/TD]
[TD]KNITWEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]BRN-CHESTNUT[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]55.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]55.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]KENDAL ZIP HOODIE[/TD]
[TD][/TD]
[TD]936798[/TD]
[TD][/TD]
[TD]SWEATS[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]BLU-DARK CYAN[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]55.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]55.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]SOPHIE CARDI[/TD]
[TD][/TD]
[TD]936280[/TD]
[TD][/TD]
[TD]KNITWEAR[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]GRY-GREY MARL[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]55.00[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]55.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]HOLLIE STRIPE LS TEE[/TD]
[TD][/TD]
[TD]938423[/TD]
[TD][/TD]
[TD]JERSEY TOPS[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]BLU-NAVY[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]25.00[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]50.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]DEDEE CORD PINAFORE DRESS[/TD]
[TD][/TD]
[TD]936317[/TD]
[TD][/TD]
[TD]DRESSES[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]GRN-BOTTLE GRN[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]49.50[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]49.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My sales data looks something like this. Some items have equal sales value and the name appears twice because the same product comes in multiple colours.

I just need to build a top 15 list that works pretty much exactly as an autofilter would where conditions would be met and laid out as seen in the table in the first post.

The condition being knitwear, for example.

I need the list to acknowledge the repeating items (alicia hood as seen in the example), then match it to the colour and the sales value

 

[Aladin Akyurek]

Let A1:I100 of Sheet1 house the data with headers in A1:I1.


Let A1 of Sheet2 house knitwear, the condition. (KNITWEAR is value in column D of Sheet1.

Assuming that you want all of the records associated with KNITWEAR, do the following...

In A3 of Sheet2, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(Sheet1!$D$2:$D$100=$A$1,ROW(Sheet1!$D$2:$D$100)-ROW(Sheet1!$D$2)+1),ROWS($A$3:A3)),"")

In B3 of Sheet2 just enter, copy across, and down:

=IF($A3="","",INDEX(A$2:A$100,$A3))