Forum Discussion
Is it possible to Rank repeating strings without repeating same ranks in an Excel Table?
I see. Excel Table "remembers" that, for example, C5 is in the last row of the table. If you expand the table it adjust formula in row 5 such way it reference on the last row of expanded table. Thus you have two formulas with C6 an no one with C5.
Workaround could be
=[@Date]&[@Fruit]& COUNTIFS( $C$2:INDEX([Fruit],ROW()-ROW(Table1[[#Headers],[Fruit]])), [@Fruit], $B$2:INDEX([Date],ROW()-ROW(Table1[[#Headers],[Date]])), [@Date])
Nishkarsh31 If you are willing to add a helper column to your table that counts the occurrence of each fruit, then the picture below might work for you. It's perhaps not the most elegant solution, but it's easy and it works.
Hi Riny_van_Eekelen , I'm good with adding helper column.
However, For some unknown reason when we expand a variable range like $C$2:C4
As soon as table expands by one row, it becomes $C$2:C6 instead of $C$2:C5
and the formula doesn't work anymore
I don't know if it's a glitch or something.
Let me know if you can find a solution to this
I see. Excel Table "remembers" that, for example, C5 is in the last row of the table. If you expand the table it adjust formula in row 5 such way it reference on the last row of expanded table. Thus you have two formulas with C6 an no one with C5.
Workaround could be
=[@Date]&[@Fruit]& COUNTIFS( $C$2:INDEX([Fruit],ROW()-ROW(Table1[[#Headers],[Fruit]])), [@Fruit], $B$2:INDEX([Date],ROW()-ROW(Table1[[#Headers],[Date]])), [@Date])A slightly modified solution I would recommend is here
=[@Date]&[@Fruit]& COUNTIFS( INDEX([Fruit],1):[@Fruit], [@Fruit], INDEX([Date],1):[@Date], [@Date])It pretty much achieves the same thing. But I recommend that we avoid cell referencing while working with Tables.
Woah. Mind blown
That row() - header row is inspired SergeiBaklan
One doubt though, why did you subtract the header Row?
" ROW()-ROW(Table1[[#Headers],[Fruit]])"
Why don't we just use " ROW() - 1 "
I tried, it's working
Any particular reason?Nishkarsh31 , in addition to PeterBartholomew1 comment - on development phase always think about the maintenance. More time you invest in development much more time you save on maintenance. Avoid hardcoding in formulas (in particular like here, use reference instead of constant 1), keep errors handling, etc - future life will be much easier.
All the best in new year!
