SomeGuyHere
New Member
- Joined
- Aug 24, 2023
- Messages
- 13
- Office Version
- 2021
- Platform
- Windows
What version of Excel are you using?
Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using, as the best solution often varies by version. Don’t forget to scroll down to save your changes.
Doug
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Rank | Item | Sales | |||||||
2 | 1 | Zebra Green | 100 | Zebra | Zebra Green | 100 | 1 | |||
3 | 2 | Xray | 99 | Xray | 99 | 2 | ||||
4 | 3 | Yak | 87 | Yak | 87 | 3 | ||||
5 | 4 | Monkey Purple | 81 | Monkey | Monkey Purple | 81 | 4 | |||
6 | 5 | Elephant | 80 | Elephant | 80 | 5 | ||||
7 | Elephant Red | 53 | Elephant | Fox | 64 | 6 | ||||
8 | Elephant Green | 22 | Elephant | Gorilla | 44 | 7 | ||||
9 | Elephant Purple | 5 | Elephant | Orange Blue | 30 | 8 | ||||
10 | 6 | Fox | 64 | |||||||
11 | Fox Purple | 49 | Fox | |||||||
12 | Fox Orange | 15 | Fox | |||||||
13 | 7 | Gorilla | 44 | |||||||
14 | 8 | Orange Blue | 30 | Orange | ||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D14 | D2 | =LET(rng,B2:B14,mult_word,SEARCH(" ",rng,1),first_word,IFERROR(LEFT(rng,mult_word-1),""),first_word) |
F2:G9 | F2 | =LET(counts,IF(D2#="",1,COUNTIF(D2#,D2#)),filt,FILTER($B$2:$C$14,counts=1),filt) |
H2:H9 | H2 | =RANK.AVG(INDEX(F2#,,2),INDEX(F2#,,2)) |
Dynamic array formulas. |
If there is a clean way to do this, it is beyond me...
Book1
A B C D E F G H 1 Rank Item Sales 2 1 Zebra Green 100 Zebra Zebra Green 100 1 3 2 Xray 99 Xray 99 2 4 3 Yak 87 Yak 87 3 5 4 Monkey Purple 81 Monkey Monkey Purple 81 4 6 5 Elephant 80 Elephant 80 5 7 Elephant Red 53 Elephant Fox 64 6 8 Elephant Green 22 Elephant Gorilla 44 7 9 Elephant Purple 5 Elephant Orange Blue 30 8 10 6 Fox 64 11 Fox Purple 49 Fox 12 Fox Orange 15 Fox 13 7 Gorilla 44 14 8 Orange Blue 30 Orange Sheet2
Cell Formulas Range Formula D2:D14 D2 =LET(rng,B2:B14,mult_word,SEARCH(" ",rng,1),first_word,IFERROR(LEFT(rng,mult_word-1),""),first_word) F2:G9 F2 =LET(counts,IF(D2#="",1,COUNTIF(D2#,D2#)),filt,FILTER($B$2:$C$14,counts=1),filt) H2:H9 H2 =RANK.AVG(INDEX(F2#,,2),INDEX(F2#,,2)) Dynamic array formulas.
Hope it helps,
Doug