Forum Discussion
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 in the column
I did this with the help of rank and countif function with variable array.
However, when I use this variable array in an table, the calculated formula goes wrong on table expansion
Is there a way to solve this?
I'm attaching a sample excel. If you can solve the problem in it, it would be much appreciated.
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])
14 Replies
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.
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 thisI 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
