Trouble with formula not working any more with new entries

Question

Level 1

17 points

Trouble with formula not working any more with new entries

I'm having some trouble with counting duplicates excluding the first occurrence. It's all fine if I don't touch the table any more (add new entries), but as soon as I add something new and then re-order a column, some cells loose the reference.

So here's what I used:

=IF(COUNTIF($A$2:A2,A2)>1,"Yes","") in the first cell of a column (let's say column B) and then dragged formula down the column, to display YES next to an entry of column A if it is a duplicate, excluding the first occurrence.

The above worked well for my existing entries, but as soon as I add a new entry to the end of my table and then re-order the table alphabetically and the new entry happens to be at the very top now because of its name, the cell where it's supposed to show YES (or nothing) for that new entry gives me an error and where the $A$2:A2 part used to be, it's now #REF! in the formula.

How can I make sure the formula get's passed along to new lines added ABOVE the initial first line? It's all fine "down", so if a new line is added to the end of the table, the formula gets passed down it works fine.

I'm sure I'm missing something here. Any ideas are highly appreciated!

MacBook Pro (2020 and later)

Posted on Apr 10, 2022 6:17 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,432 points

Apr 10, 2022 8:13 AM in response to Kreiselblitz

Sorting will mess up your formula. It is a long-standing bug in the app, reported at least 18 months (and several versions) ago. When sorted, most of the range references will be correct but one or more will be reference errors. I believe it depends on where the cell started and where it ended up after the sort.

One way to correct it is by creating the range reference using OFFSET. Assuming row 1 is a header row (so it will not move during the sort):

=IF(COUNTIF(OFFSET(A$1,1,0,ROW()−1,1),A2)>1,"yes","")

You can also use the new filtering rules to find duplicates. "Is a duplicate" will show the rows that are duplicates. Note that it includes the first occurrence of each versus your formula that lets the first one pass and flags only the ones found lower in the table. There are other filters for duplicates to try out. "Is distinct" will show the first occurrence and hide all the duplicates found lower in the table.

Reply

Answer 2

SGIII

Level 8

37,550 points

Apr 10, 2022 8:00 AM in response to Kreiselblitz

I think you have encountered the notorious "sort bug" in some of the Numbers functions, where you get unexpected results after sorting.

You can use the OFFSET function to create a "sort-safe" formula. It's awkward, but it still works if you then sort this table on column A so that the new A entry pops up to the top:

=IF(COUNTIF(OFFSET(A$1,1,0,ROW(cell)−1,1),A2)>1,"Yes","")

This is a special form of the OFFSET function that constructs an "array" of values in memory, much like the value of a range of cells. See OFFSET and Help > Formulas and Functions Help for more on the function.

Of course, if you want to save some work you can skip formulas altogether and just add a category for column A.

The duplicates with then pop out immediately.

You can delete duplicate rows right within Categories and when you're finished use turn the feature off under Orgazize > Categories in the panel at the right.

SG

Reply

Answer 3

SGIII

Level 8

37,550 points

Apr 11, 2022 8:51 AM in response to Kreiselblitz

Kreiselblitz wrote:

I sort of understand how it works (not quite sure why SUM of all the values equals number if items…)

Ah, you may be overthinking it!

=IF(COUNTIF(B,B2)>0,1/COUNTIF(B,B2),1)

COUNTIF(B,B2) counts how many times that box occurs in the entire column. If there is no box the count is 0, the IF is false and that final 1 gets inserted in the cell.

If there is a box then the IF is true and the 1/COUNTIF(B,B2) in the formula divides the number of times that box occurs into 1 and puts that result in the cell instead of a 1. So suppose you had three movies all with the same box in column B. The formula would insert 1/3 in column C for each movie in that box. Then you add up all the 1s for "standalone" movies and the fractions for movies in a boxed set to get the total number of items.

SG

Reply

Answer 4

Kreiselblitz Author

Level 1

17 points

Apr 10, 2022 8:34 AM in response to Badunit

Thank you very much, to both of you! As you may have noticed, I'm not very good with Numbers (or Excel) and I need to take small steps. I will definitely try your suggestions and see if I can get them to work.

I should mentioned (maybe) why I'm looking for duplicates. I have another cell that shows me the total number of duplicates, which will be then be subtracted from another number. Here's why:

I'm trying to create a catalog of all my movies. Mainly because I want to keep track of how many movies I actually own, but I also want to see how many items there are on the shelves. An item can be a box set with several movies. So for example, I have two single Blurays and two boxsets with three movies each. Number of movies is 8, number of items is 4. I have a column in my table "Belongs to box/collection). So I will have each box set listed three times.

The total number of items in my collection is simply "total number of movies minus the duplicates from the "Belongs to…" column" (took me a while to realize this). So I want to count the duplicates (by counting all the "YES", which I have figured out) and subtract this number from the total number of movies. That's also no problem. Only that step in between is missing.

As I said, I will see how far I get with the OFFSET feature. It will likely take me some time, but at least I keep learning this way.

Thanks for pointing out this is actually a bug! At least I know it's not only my fault ;)

Reply

Answer 5

Kreiselblitz Author

Level 1

17 points

Apr 10, 2022 8:57 AM in response to Kreiselblitz

Yep, that did it! Guys/Girls, to me you are geniuses :) Really, thank you for the quick and helpful replies! I wish I could mark both replies as "Solved my question", as you both had the same idea.

Now I can finally go on. My way of getting the number of items may not be the most elegant one and I am sure there is a way to get this all in one formula, but this wouldn't be something I understand. I came up with this, it works, it's fine. I can hide the "Is duplicate" column, so it won't be in the way.

Also, thank you for taking the time to actually give me the formula, which I just need to adjust to my columns!

Reply

Answer 6

SGIII

Level 8

37,550 points

Apr 10, 2022 9:27 AM in response to Kreiselblitz

Kreiselblitz wrote:

I'm trying to create a catalog of all my movies. Mainly because I want to keep track of how many movies I actually own, but I also want to see how many items there are on the shelves.

Here's an idea for handling that. It doesn't require OFFSET. And it also isn't affected by sorting.

The formula in C treats items in a box as a fraction of an item. It divides 1 by the number of times that a Box name appears in the entire column. If there is no Box it inserts a 1. So you can just SUM the values in that column to get the count of items on the shelf.

You can get the count of individual movies in the usual way, by using COUNTA.

SG

Reply

Answer 7

Kreiselblitz Author

Level 1

17 points

Apr 11, 2022 12:02 AM in response to SGIII

Awesome, that sounds less 'awkward' indeed. I would have never come up with this, but I sort of understand how it works (not quite sure why SUM of all the values equals number if items…) and I think I will use it from now on. I used COUNTA for the total number of movies already :)

I also wanted to point out that mentioning categories was really helpful as well: it gives me a nice overview of all items in a box set, if applied to the "Belongs to…" column!

Again, thank you all very much! I hope this thread helps someone in the future :)

Reply

Answer 8

Kreiselblitz Author

Level 1

17 points

Apr 12, 2022 5:18 AM in response to SGIII

Thank you for taking the time to explain this to me!

The best thing is: it worked, I actually understand it now. Turns out I WAS overthinking it.

By the way: I am fully aware that there apps out there, designed to quickly scan movies via their barcode and to add them to your collection. I still prefer the the Numbers or Excel table, as none of the apps I tried were really working out for me. So your help is even more appreciated :)

Reply

Trouble with formula not working any more with new entries

Similar questions