Function to replicate filter needed - time sensitive

[colinheslop1984]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A1 STYLE[/TD]
[TD]B DEPARTMENT[/TD]
[TD]C CATEGORY[/TD]
[TD]D COLOUR[/TD]
[TD]E VALUE[/TD]
[/TR]
[TR]
[TD]ADRIANA[/TD]
[TD]WOMENS[/TD]
[TD]JERSEY[/TD]
[TD]PLUM[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]MAIZEY[/TD]
[TD]WOMENS[/TD]
[TD]NIGHTWEAR[/TD]
[TD]TEAL[/TD]
[TD]205[/TD]
[/TR]
[TR]
[TD]JACOB[/TD]
[TD]MENS[/TD]
[TD]KNITWEAR[/TD]
[TD]NAVY[/TD]
[TD]178[/TD]
[/TR]
[TR]
[TD]HAYWOOD[/TD]
[TD]WOMENS[/TD]
[TD]KNITWEAR[/TD]
[TD]YELLOW[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]PENGUIN[/TD]
[TD]WOMENS[/TD]
[TD]NIGHTWEAR[/TD]
[TD]PURPLE[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]ORGANIC[/TD]
[TD]MENS[/TD]
[TD]JERSEY[/TD]
[TD]BLUE[/TD]
[TD]79[/TD]
[/TR]
</tbody>[/TABLE]












I have a table of data like the one above only larger. I want to create a top 10 list based on criteria, the criteria being column's B and C.

I would like the below table to populate itself in order of value if column B meets the desired criteria's, e.g. women's AND nightwear. In essence it will work just like a filter

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]DEPARTMENT CRITERIA (E.G. WOMENS)[/TD]
[TD]CATEGORY CRITERIA (E.G. NIGHTWEAR)[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]STYLE[/TD]
[TD]COLOUR[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]*FUNCTION NEEDED[/TD]
[TD]*FUNCTION NEEDED[/TD]
[TD]*FUNCTION NEEDED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









DESIRED RESULT BELOW

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[/TR]
[TR]
[TD]STYLE[/TD]
[TD]COLOUR[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]MAIZY[/TD]
[TD]TEAL[/TD]
[TD]205[/TD]
[/TR]
[TR]
[TD]PENGUIN[/TD]
[TD]PURPLE[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]AND SO ON[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







Any suggestions would be much appreciated

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	A1 STYLE	B DEPARTMENT	C CATEGORY	D COLOUR	E VALUE		Womens	A1 STYLE	D COLOUR	E VALUE
2	ADRIANA	WOMENS	JERSEY	PLUM	210		Nightwear	MAIZEY	TEAL	205
3	MAIZEY	WOMENS	NIGHTWEAR	TEAL	205			PENGUIN	PURPLE	105
4	JACOB	MENS	KNITWEAR	NAVY	178
5	HAYWOOD	WOMENS	KNITWEAR	YELLOW	150
6	PENGUIN	WOMENS	NIGHTWEAR	PURPLE	105
7	ORGANIC	MENS	JERSEY	BLUE	79
Jan2

Cell Formulas
Range	Formula
H2	{=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($B$2:$B$7=$G$1)*($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),1),"")}
I2	{=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($B$2:$B$7=$G$1)*($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),4),"")}
J2	{=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($B$2:$B$7=$G$1)*($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),5),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[colinheslop1984]

How would that formula look if I wanted to remove the criteria for column B (DEPARTMENT). SO only one criteria, that being the category column

 

[Fluff]

Just remove the part in red from each formula
=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($B$2:$B$7=$G$1)*($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),1),"")

 

[colinheslop1984]

=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($B$2:$B$7=$G$1)*($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),1),"")

What about this bit at the end in red?

 

[Fluff]

Leave that as is

 

[colinheslop1984]

That isn't showing me any data, the cells just appear empty

 

[Fluff]

Should work

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	A1 STYLE	B DEPARTMENT	C CATEGORY	D COLOUR	E VALUE			A1 STYLE	D COLOUR	E VALUE
2	ADRIANA	WOMENS	JERSEY	PLUM	210		Nightwear	MAIZEY	TEAL	205
3	MAIZEY	WOMENS	NIGHTWEAR	TEAL	205			PENGUIN	PURPLE	105
4	JACOB	MENS	KNITWEAR	NAVY	178			ORGANIC	BLUE	79
5	HAYWOOD	WOMENS	KNITWEAR	YELLOW	150
6	PENGUIN	WOMENS	NIGHTWEAR	PURPLE	105
7	ORGANIC	MENS	NIGHTWEAR	BLUE	79
Jan2

Cell Formulas
Range	Formula
H2	{=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),1),"")}
I2	{=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),4),"")}
J2	{=IFERROR(INDEX($A$2:$E$7,SMALL(IF(($C$2:$C$7=$G$2),ROW($A$2:$A$7)-1),ROW($G1)),5),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[colinheslop1984]

[TABLE="class: cms_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]

Still trying to figure this one out, please can you advise?

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

 

[Fluff]

Without being able to see your data, or your criteria, I've no idea.
Also as rows 5 to 8 are identical, how do you determine that they should only appear twice.