SOLVED

Is it possible to Rank repeating strings without repeating same ranks in an Excel Table?

%3CLINGO-SUB%20id%3D%22lingo-sub-2026709%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026709%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20automate%20batch%20no.%20for%20concatenated%20items%20in%20a%20column.%3CBR%20%2F%3EThe%20problem%20is%20if%20the%20concatenation%20is%20not%20unique%2C%20I%20want%20to%20add%20a%20serial%20no.%20to%20the%20batch%20no.%26nbsp%3B%3CBR%20%2F%3Eon%20the%20item%20which%20is%20repeated%20later%20in%20the%20column%3CBR%20%2F%3E%3CBR%20%2F%3EI%20did%20this%20with%20the%20help%20of%20rank%20and%20countif%20function%20with%20variable%20array.%3CBR%20%2F%3EHowever%2C%20when%20I%20use%20this%20variable%20array%20in%20an%20table%2C%20the%20calculated%20formula%20goes%20wrong%20on%20table%20expansion%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20solve%20this%3F%3CBR%20%2F%3EI'm%20attaching%20a%20sample%20excel.%20If%20you%20can%20solve%20the%20problem%20in%20it%2C%20it%20would%20be%20much%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2026709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026758%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3BIf%20you%20are%20willing%20to%20add%20a%20helper%20column%20to%20your%20table%20that%20counts%20the%20occurrence%20of%20each%20fruit%2C%20then%20the%20picture%20below%20might%20work%20for%20you.%20It's%20perhaps%20not%20the%20most%20elegant%20solution%2C%20but%20it's%20easy%20and%20it%20works.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-01-02%20at%2016.23.41.png%22%20style%3D%22width%3A%20625px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243697iB8D2F33320478D66%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-01-02%20at%2016.23.41.png%22%20alt%3D%22Screenshot%202021-01-02%20at%2016.23.41.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026811%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20same%20idea%20that%20could%20be%20slightly%20modified%20initial%20formula%20(without%20helper%20column)%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%5B%40Date%5D%26amp%3B%5B%40Fruit%5D%26amp%3BCOUNTIFS(%24C%242%3A%24C2%2C%5B%40Fruit%5D%2C%24B%242%3A%24B2%2C%5B%40Date%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EHowever%2C%20batch%20number%20is%20changing%20if%20transform%20the%20table%2C%20e.g.%20to%20sort%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026862%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026862%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20I've%20tried%20this%20already.%3CBR%20%2F%3EFor%20some%20unknown%20reason%20when%20we%20expand%20a%20variable%20range%20like%20%24C%242%3AC4%3CBR%20%2F%3EAs%20soon%20as%20table%20expands%20by%20one%20row%3CBR%20%2F%3Eit%20becomes%20%24C%242%3AC6%20instead%20of%20%24C%242%3AC5%3CBR%20%2F%3Eand%20the%20formula%20doesn't%20work%20anymore%3CBR%20%2F%3E%3CBR%20%2F%3EI%20don't%20know%20if%20it's%20a%20glitch%20or%20something.%3CBR%20%2F%3ELet%20me%20know%20if%20you%20can%20find%20a%20solution%20to%20this%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026863%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026863%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%2C%20I'm%20good%20with%20adding%20helper%20column.%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20For%20some%20unknown%20reason%20when%20we%20expand%20a%20variable%20range%20like%20%24C%242%3AC4%3CBR%20%2F%3EAs%20soon%20as%20table%20expands%20by%20one%20row%2C%20it%20becomes%20%24C%242%3AC6%20instead%20of%20%24C%242%3AC5%3CBR%20%2F%3Eand%20the%20formula%20doesn't%20work%20anymore%3CBR%20%2F%3E%3CBR%20%2F%3EI%20don't%20know%20if%20it's%20a%20glitch%20or%20something.%3CBR%20%2F%3ELet%20me%20know%20if%20you%20can%20find%20a%20solution%20to%20this%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026899%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026899%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20see.%20Excel%20Table%20%22remembers%22%20that%2C%20for%20example%2C%20C5%20is%20in%20the%20last%20row%20of%20the%20table.%20If%20you%20expand%20the%20table%20it%20adjust%20formula%20in%20row%205%20such%20way%20it%20reference%20on%20the%20last%20row%20of%20expanded%20table.%20Thus%20you%20have%20two%20formulas%20with%20C6%20an%20no%20one%20with%20C5.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWorkaround%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%5B%40Date%5D%26amp%3B%5B%40Fruit%5D%26amp%3B%0A%20%20COUNTIFS(%0A%20%20%20%20%24C%242%3AINDEX(%5BFruit%5D%2CROW()-ROW(Table1%5B%5B%23Headers%5D%2C%5BFruit%5D%5D))%2C%0A%20%20%20%20%5B%40Fruit%5D%2C%0A%20%20%20%20%24B%242%3AINDEX(%5BDate%5D%2CROW()-ROW(Table1%5B%5B%23Headers%5D%2C%5BDate%5D%5D))%2C%0A%20%20%20%20%5B%40Date%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2027019%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027019%22%20slang%3D%22en-US%22%3E%3CP%3EWoah.%20Mind%20blown%3CBR%20%2F%3EThat%20row()%20-%20header%20row%20is%20inspired%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EOne%20doubt%20though%2C%20why%20did%20you%20subtract%20the%20header%20Row%3F%3CBR%20%2F%3E%22%20ROW()-ROW(Table1%5B%5B%23Headers%5D%2C%5BFruit%5D%5D)%22%3CBR%20%2F%3EWhy%20don't%20we%20just%20use%20%22%20ROW()%20-%201%20%22%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tried%2C%20it's%20working%3CBR%20%2F%3EAny%20particular%20reason%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2027149%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20Rank%20repeating%20strings%20without%20repeating%20same%20ranks%20in%20an%20Excel%20Table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20usual%20reason%20is%20to%20safeguard%20against%20the%20possibility%20that%20someone%20might%20insert%20a%20row%20above%20the%20tables%20at%20some%20later%20date%20and%20not%20notice%20that%20the%20record%20numbers%20are%20now%20out%20of%20step.%26nbsp%3B%20It%20is%20a%20more%20robust%20style%20of%20programming%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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.

14 Replies

@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.

Screenshot 2021-01-02 at 16.23.41.png

@Riny_van_Eekelen 

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 @Sergei Baklan , 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

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

best response confirmed by Nishkarsh31 (Contributor)
Solution

@Nishkarsh31 

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

Woah. Mind blown
That row() - header row is inspired @Sergei Baklan 

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 

The usual reason is to safeguard against the possibility that someone might insert a row above the tables at some later date and not notice that the record numbers are now out of step.  It is a more robust style of programming Excel.

@Nishkarsh31 , in addition to @Peter Bartholomew 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!

Genius, I appreciate it. @Peter Bartholomew 
One follow up question

I've always wondered that hardcoding values would save up on the calculation speed.

Writing 1 instead of referencing header row, would prevent one wheel of calculation, in case the
sheet is very heavy.
Is there any evidence of this?

Thank you so much sir @Sergei Baklan 

I have that same follow up question

I've always wondered that hardcoding values would save up on the calculation speed.

Writing 1 instead of referencing header row, would prevent one wheel of calculation, in case the
sheet is very heavy.
Is there any evidence of this?

@Nishkarsh31 

That's always a compromise - save few milliseconds on recalculation of few hours of your own time on maintenance. Depends on concrete case, if recalculation is really slow when it's worth to re-build formulae.

@Nishkarsh31 

If you are going to attempt to optimise a sheet for speed then I suggest you use tools to analyse the performance such as that offered by Charles Williams's FastExcel.  Otherwise, it is only too easy to commit too much effort to changing code that is not consuming significant resource in the first place.

 

Usually the first consideration is to select computationally efficient algorithms, then to program them to be as transparent as possible.  Rather than hard-wiring values which cannot be guaranteed to be constant, I would prefer to add the record sequence as a helper range; it would do little for speed but, given a suitable variable name, may add clarity to downstream formulas.

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 

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.

 

@Sergei Baklan