colinheslop1984
Board Regular
- Joined
- Oct 14, 2016
- Messages
- 129
- Office Version
- 2016
Rank | Style Name | Colour | Units | Value |
1 | STAR JUMPER | BLU-NAVY | 3 | 148.5 |
2 | HAYWOOD CARDIGAN | YEL-GOLDEN HAZE | 3 | 143.55 |
3 | SUZIE SWING ROLL NECK | BLK-PHANTOM | 2 | 110 |
4 | CHARLOTTE CASHMERE JUMPER | PNK-SEASHELL | 1 | 98 |
5 | ALICIA HOODIE X | BLU-NAVY | 1 | 55 |
6 | ALICIA HOODIE X | BLU-NAVY | 1 | 55 |
7 | ALICIA HOODIE X | BLU-NAVY | 1 | 55 |
8 | ALICIA HOODIE X | BLU-NAVY | 1 | 55 |
9 | HAYWOOD CARDIGAN | PNK-ROSE ASH | 1 | 49.5 |
10 | HAYWOOD CARDIGAN | PNK-ROSE ASH | 1 | 49.5 |
11 | LIZZIE CARDI | GRN-BOTTLE GRN | 1 | 45 |
12 | HAYWOOD CARDIGAN | NAT-IVORY | 1 | 44.55 |
13 | HOLLY CHENILLE JUMPER | PPL-DATE | 1 | 42 |
14 | HOLLY CHENILLE JUMPER | PPL-DATE | 1 | 42 |
15 | CHARLOTTE CASHMERE JUMPER | GRY-GREY MARL | 1 | 39.2 |
<tbody>
</tbody>
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