Eliminating Or Merging Duplicate Rows In Numbers (#1437)

Eliminating Or Merging Duplicate Rows In Numbers (#1437)


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.

Leave a Reply

Your email address will not be published. Required fields are marked *