Identifying Most to Least with up to 8 duplicates

The Excel warning about omitting adjacent cells is nothing to worry about in this case...it's intentional.

Hi Nick,

Thanks for taking the time to look at this. Wouldn't be the 1st time I blindly stumble onto/into an answer. "It's not a bug it's a feature !!!"

So far this has been the only way I've figured out to do it, which really means nothing. I'm not an Excel Rock Start like so many of you generous people here.

Thanks,

Bambu

To explain a bit more, for your tiebreaker, the formula =D3+COUNTIF($D3:D$151,D3)/1000 copied down as you have used works fine. Personally I find it easier to follow to have the "locked" cell at the top rather than the bottom, so I would use =D3+COUNTIF(D$3:D3,D3)/1000 copied down instead. Or another way I often do it is to use the row, e.g. =D3+ROW(D3)/1000. Or if you want it to start counting from 1 then =D3+(ROW(D3)-ROW($D$3)+1)/1000.

As far as I understand though, the sheet you have is already giving you the desired results - if that's not correct then let us know

Hi Nick,

I agree

so I would use =D3+COUNTIF(D$3:D3,D3)/1000 copied down instead

but like I said if you make the top absolute the other formulas (RANK, MATCH, and INDEX) break.

Is it (why the formulas break) because when using MATCH the data should be sorted in a particular way (I think I read something like that NOT recently but in my travels) ???
If that's the case all of this sorting and resorting kind of seems like a pivot table thing but that's just a guess/thought.

I've used ROW to do my bidding before but I guess the fact that the data is horizontal is throwing me (we would do Range(Cell(Row,C), Cell(Row,C)) in VBA so ). I will have to try it when I circle back on this particular issue.

I was thinking about it last night once I started writing the code for this "I probably should just get the info from a pivot table !!!" It's already in a TABLE and pivot tables do make slicing and dicing data easier. The con is that pivot tables come with their own set of problems and limitations.

In closing, my formula (tiebreaker) appears to work but I don't trust it nor do I think it's the best (most efficient if even accurate) formula(s) to get what in the end is an IndexMatch for my pokemon collection.

I have to test your formulas Nick (they look like they will work perfectly) when I get a moment but without getting into the weeds . I'm working on a few different projects right now and had to jump back on a project I put down to work on this one.

In your example for most cells you have the bottom cell absolute e.g. $D4:D$151 in E4. This means the range always goes from the current row to the bottom of the range. Where you have (I think) tried to change it in row 3 you have used $D$3:D151. This is incorrect and should be $D$3:D3. That's why the formulae break - as you have it the whole range (rows 3 to 151) are always counted (along with some more rows off the bottom as you copy it down) which means it does not break ties, which leads to the errors.

If you're using horzontal data you can just use COLUMN instead of row, if that helps.

Yes, it is somewhat overcomplicated...you could use this in I3 to sort the count:

=LARGE($D$3:$D$151,C3)

And this in H3 to get the collection name:

=INDEX($B$3:$B$151,SMALL(IF($D$3:$D$151=I3,$C$3:$C$151),COUNTIF($I$3:I3,I3)))

Both copied down.

The tiebreakercount, rank, match columns then become redundant.