Solution needed to create top list with criteria

colinheslop1984

Board Regular
Joined
Oct 14, 2016
Messages
129
Office Version
  1. 2016
RankStyle NameColourUnitsValue
1STAR JUMPERBLU-NAVY3148.5
2HAYWOOD CARDIGANYEL-GOLDEN HAZE3143.55
3SUZIE SWING ROLL NECKBLK-PHANTOM2110
4CHARLOTTE CASHMERE JUMPERPNK-SEASHELL198
5ALICIA HOODIE XBLU-NAVY155
6ALICIA HOODIE XBLU-NAVY155
7ALICIA HOODIE XBLU-NAVY155
8ALICIA HOODIE XBLU-NAVY155
9HAYWOOD CARDIGANPNK-ROSE ASH149.5
10HAYWOOD CARDIGANPNK-ROSE ASH149.5
11LIZZIE CARDIGRN-BOTTLE GRN145
12HAYWOOD CARDIGANNAT-IVORY144.55
13HOLLY CHENILLE JUMPERPPL-DATE142
14HOLLY CHENILLE JUMPERPPL-DATE142
15CHARLOTTE CASHMERE JUMPERGRY-GREY MARL139.2

<tbody>
</tbody>
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
26DUNGAREE - MID BLUE936503DENIMDEN-DENIM59.001259.00
26WINTER WOODLAND LEGGING937704NIGHTWEARBLU-NAVY29.502359.00
28ORGANIC COTTON BRETON TEE932758JERSEY TOPSBLU-MIDNIGHT28.002256.00
29ALICIA HOODIE X935641KNITWEARBLU-NAVY55.001055.00
29ALICIA HOODIE X935641KNITWEARGRN-BOTTLE GRN55.001055.00
29ANNABELLE EMBROIDERED JUMPER935847KNITWEARBRN-CHESTNUT55.001255.00
29KENDAL ZIP HOODIE936798SWEATSBLU-DARK CYAN55.001155.00
29SOPHIE CARDI936280KNITWEARGRY-GREY MARL55.001255.00
34HOLLIE STRIPE LS TEE938423JERSEY TOPSBLU-NAVY25.002050.00
35DEDEE CORD PINAFORE DRESS936317DRESSESGRN-BOTTLE GRN49.501149.50

<colgroup><col span="29"></colgroup><tbody>
</tbody>

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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top