Why does my index column formula break when sorting the table?

Question

Level 1

8 points

Why does my index column formula break when sorting the table?

I have a table with approximately 1900 rows in it that has a category column (A) and an index column (G) that uses the following formula (as per this thread and others):

IF(COUNTIF(A$2:A2,A2)=1,MAX(G$1:G1)+1,””)

I fill that formula down the G column and it works correctly no matter how the table is sorted to begin with:

If I then sort the table by another column, my index column breaks. It doesn't matter what column I sort by - if the order of the rows changes, the index column breaks:

I've looked this thing over many times and I have no idea why I can't sort the table - I am not a spreadsheet person but as far as I can see my formula should allow sorting to happen. Right after sorting there is a flash where the index column isn't broken but hasn't updated, then a second or two later the column breaks. Not every row is broken, but most are and I see no rhyme or reason as to why most are broken and a few (1 out of 20?) aren't.

Is it simply that there are too many rows for the app to keep up? It takes a few seconds to calculate that row when I first paste the formula into the column... I'm not doing anything extraordinary and would hope that isn't the case.

Any help would be appreciated! Thank you!

Posted on Aug 3, 2021 5:20 AM

Reply

Answer 1

Badunit

Level 7

30,426 points

Aug 3, 2021 6:43 AM in response to Mr.Fixins

I feel for you. There was a bug introduced by Apple a few versions back that has not been fixed in any of the updates and I have to say I am completely disappointed it has gone on this long. I suspect if you click on any of those error triangles you will see an "invalid reference" error and COUNTIF will have a #REF! reference in it. This bug breaks every one of the COUNTIF solutions like this that we have provided over the past years/decade+ when the table is sorted. I do not know if MAX has the same problem. Would you please look at the cells with the error triangles you can see if MAX also has #REF! references in it.

A workaround is to use OFFSET to create the range. I like to have it offset from a header row (because they stay anchored during a sort) but you don't necessarily have to.

COUNTIF(OFFSET(A$1,1,0,ROW()−1,1),A2)

Reply

Answer 2

SGIII

Level 8

37,550 points

Aug 3, 2021 6:36 AM in response to Mr.Fixins

I think your formula should work after a sort and that a bug has been introduced into Numbers in a recent version.

You might want to report the suspected but to Apple via Numbers > Provide Numbers Feedback in your menu, choosing bug from the Feedback Type dropdown.

It seems the intent of your index is to construct a list of distinct values from column A. Have you considered just using the built-in Categories functionality instead? No formulas! Just a few clicks.

The visible cells can then be copy-pasted into a destination table.

SG

Reply

Answer 3

SGIII

Level 8

37,550 points

Aug 3, 2021 6:56 AM in response to Mr.Fixins

So the complete sort-safe version of your formula in G2, filled down, would be:

=IF(COUNTIF(OFFSET(A$1,1,0,ROW()−1,1),A2)=1,MAX(OFFSET(G$1,0,0,ROW()−1,1))+1,"")

Pretty ugly! And keep in mind that OFFSET is a so-called "volatile" function, which fires every time you make a change in your document, which can make larger documents sluggish. That's another reason to take advantage of built-in functionality of Categories.

SG

Reply

Answer 4

Mr.Fixins Author

Level 1

8 points

Aug 4, 2021 10:31 AM in response to SGIII

SG Thanks so much for taking the time to reply in such a detailed fashion. I agree that is a pretty ugly formula - it doesn't help that common practice is to keep all the formulas on one line. I'm a numbers newb (but am a programmer) and seeing all these formulas without any formatting makes them basically illegible. I find it helps me to break things up in some fashion:

IF(

COUNTIF(

OFFSET(

A$1,1,0,ROW()−1,1

),A2

)=1,MAX(

OFFSET(

G$1,0,0,ROW()−1,1

)

)+1,""

)

While working on my tables I did see categories as a tool but wasn't sure how that would intersect with pivot tables and also wanting to sort table for viewing by different columns and so I figured old school was the way to go.

I wish I could mark both yours and bad unit's answers as solving my problem (you consolidated badunit's partial formula into the larger one) but I think technically he gets the solve for answering the original question.

Thanks again for answering - I'm always impressed when someone takes the time to answer a question in such a detailed fashion and I see you've been doing it for quite some time - I just used another one of your answers about INDEX MATCH in another part of my spreadsheet.

You're great!

Reply

Answer 5

Mr.Fixins Author

Level 1

8 points

Aug 4, 2021 10:36 AM in response to Badunit

Thanks Badunit - that works great and has solved my problem. You're correct that MAX also had #REF! problems. THANKS!

Reply

Why does my index column formula break when sorting the table?

Similar questions