Something you may need to do in Numbers from

time to time is to find duplicates. So say you have a list. I’ve got a list here with fruits in one column

and then the number of them in the other. But there are some duplicates. If you look through the list there is apple

several times, cherries several times, and a few other duplicates. So how can you find them? Well, first let’s sort by the column that

has fruit in it. Now you may have other columns you want to

sort, say the date or something like that, but for now let’s sort everything by the column

that has duplicates. Sure enough I can see that apples are there

three times, cherries are there three times, and if I look enough I’ll find some other

duplicates here as well. There’s orange twice. Now if the list is huge you’re not going to

want to look through it manually. So how can you get it to be done automatically

or semi-automatically so you can check and see where the duplicates are and get rid of

them easily. So we’re going to use a bit of logic and a

simple function to figure this out. If this here, A3, is the same as A2 then we

know that A3 is a duplicate. So we’ll start here in the second cell, because

it doesn’t make sense to do it in the first cell because there is nothing above it to

compare it to. We’ll start here in the second row, row three

in this case, and we’ll say, start a formula and say if this equals this. We’re not using the if statement. We’re just actually using the equals sign

between the two. We hit return and we see it says true. If we copy and paste that here we’ll see it

says true for the next one because the third on is also a duplicate. We’ll go here where it starts with apricot

and that’s false. Then true because it’s the second apricot. False because avocado is by itself. Banana is by itself. Blackberry is by itself. Etc. I can select all of the cells here, I just

did that by double clicking on C column header, paste it in, and then we’ll get trues and

falses depending upon what’s there. Of course it’s not going to apply to the first

one but we can leave it there because it says false at least. So everywhere there’s a true we know we have

a duplicate column. Now we could make it a little nicer. Instead of just using this formula here and

having it be a true or false we could use the if statement there, make the comparison

the first part of the if statement and say if it is true put the word duplicate maybe

with an exclamation point. If it’s not just a blank. So two quotes there, so a blank piece of text. Now when we copy and paste that throughout

we could see it’s easier to tell where the duplicates are. We can see all of the duplicate rows very

easily and we can go ahead and check them out and eliminate them. Say you want to take it a step further. Say you don’t just want to eliminate the extra

rows, you want to merge them. So you want to have the total count of all

the apples, 202+19+121. Now you can do that manually every time you

see the word duplicate you can add them together. But it would be nice to have a formula to

do it automatically. So let’s increase by one more column here

so we can work with this column. Let’s put a formula here that’s basically

going to be the total. We’re going put equals and then we’re going

to say the count, so the same number, then we’re going to use plus then use an if statement

to say if the value in the first column is equal to the value of the second column; in

other words the next one is a duplicate. Then take the value of that. Now we don’t want to take it from here. We want to actually take it from the same

column that will add up successive rows because if you took it from here it would only add

nineteen and it would never add the 121. We want it to add the nineteen but we want

to have this one also have the 121. We’ll see how it works in a second. So we say D3, the row below. Then the other option is zero. The zero is when the next one is not the same. When we go from apple to apricot or cranberry

to date then it’s going to add a zero to it so it will not add the next one. We close the parentheses there. So now we go and you can see it says 202 still. You think it would add the nineteen. But it’s trying to add this one. So we want to copy this and paste it everywhere. So now we see 342 which is the correct count. What is it doing? Well this first one here, the last row of

apples, says 121 and the 192 does not match because apricot is different than apple. But here it’s saying okay take the 19 and

then apple and apple are the same so add that 121 and 140. Now here take the 202, then since apple and

apple are the same add 140 to that you get 342 for the total. So now what I want to do is I want to, instead

of these beings the results of formulas, I want them to be actual real numbers that are

in there. The same thing here. I don’t want these to be results of formulas

either. I going to move things around and sort them. So I’m going to select both of these columns,

I’m going to do Edit, Copy, and then Edit, Paste Formula Results. So now this is really the word duplicate. This is really the number 342. They’re not results of formulas. Now the cool thing I can do is I can sort

by the row C here in ascending order and at the bottom I’ll get all the duplicate rows. I can select all of those, Delete them, and

I’m left with all of the rows that have the correct count here in this column D here. I could then simply Copy and Paste it over

here so I have updated count numbers or I could delete this column and let this on replace

it. Delete these right here and now I’ve got everything

there once with the complete total merged counts.