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])
While working with Table, this is what you need to do:
=[@Date]&[@Fruit]&COUNTIFS(INDEX([Fruit],1):[@Fruit],[@Fruit])
I would strongly discourage using relative referencing while working with Tables.
I have also attached the file.