Forum Discussion
Solved
Is it possible to Rank repeating strings without repeating same ranks in an Excel Table?
I want to automate batch no. for concatenated items in a column. The problem is if the concatenation is not unique, I want to add a serial no. to the batch no. on the item which is repeated later ...
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])
With same idea that could be slightly modified initial formula (without helper column)
=[@Date]&[@Fruit]&COUNTIFS($C$2:$C2,[@Fruit],$B$2:$B2,[@Date])However, batch number is changing if transform the table, e.g. to sort it.
Hi SergeiBaklan , I've tried this already.
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