Labeling Ranking Numbers

[SomeGuyHere]

I'm trying to create ranking numbers next to my product, but I want to skip like-labeled items. Is there a formula that can be applied across column J in the example image? Thank you.

 

[duggie33]

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

 

[SomeGuyHere]

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

Excel 2021. I updated my details.

 

[duggie33]

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

 

[SomeGuyHere]

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

Thanks!