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])
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.