Hi, this is Gary with MacMost.com. Let me show you how to compare
two tables in Numbers and find the differences.
MacMost is brought to you thanks to a great group of more than 1000 supporters.
Go to MacMost.com/patreon. There you can read more about the Patreon Campaign.
Join us and get exclusive content and course discounts.
So a common task that you may need to do with spreadsheets is to compare old data and new data.
So say you have two tables with names in them and you want to figure out which names are only
in one table and not the other. So here I've got two such tables. Now these are two tables in the
same sheet but they could be in separate sheets as long as they are in the same document. So you
can have formulas that work on both tables at the same time. Now the simplest way to do this
is to add a column to each table and use COUNTIF to figure out if that name is in the other table.
So let's add another column here. Then I'm going to add a formula here. This is going to be
COUNTIF and the first part of COUNTIF is the test array. So the list of things we're looking in. So
it will be this. The second part after the comma is the name it's looking for. That. So this will
give us a one if we can find one name that matches this one. It gives a zero if not. I'm going to
Copy that, double click here on the column heading so I select all of the cells except the Header
and Paste. Now I could see I have all ones except two zeros. These two names are missing from this.
I could do the same thing with this table adding the same formula here.
So COUNTIF and then we're going to look this time in this list of names for this name.
Then I'm going to Copy it and Paste it throughout. I could see there are two
here that are in this table but not in this one.
Now my next step might be to make this a little nicer than zeros and ones.
So I can go in here and I could say IF and then use this COUNTIF as the condition. So if it's one
it's true. Then nothing. So just two double quotes have nothing there. Otherwise I could say Not In
Table Two. Like that. If I Copy and Paste that you could see those two cells there have Not In
Table Two and I can expand it a bit and it's a really clear indicator of which ones are here,
but they're missing in this table. I can do the same thing here. So surround this with IF and
then a blank or Not In Table One like that.
Then I could Copy and Paste throughout. Make this column wider. It's pretty clear what's going on.
But what if we don't want to add another column, a comparison column, to each ones of these. We don't
have to. I'm going to Delete these columns and use another Table instead. So I'll move there next to
each other and I'm going to add a table that just has a Header row but no Header column. Like that.
I'm just going to have two cells there. This will be the comparison table here. We're going
to do the same formula but do it here. So I'm going to do IF and then I'm going to do COUNTIF
this list of names doesn't have this in it.
Then blank. Otherwise we can say something like, this name, then ampersand Is Not In Table Two.
Now let's expand this to have the same number of rows as the longest table. So if one of
these was a little longer than the other we have that number of rows. Now I can Copy
and Paste throughout here. You could see what it does here. Now instead of actually having
that text in there let's remove that and just have the name. So I'll Copy and Paste that throughout
and then I can just call this In One Not Two. Then I could do this one, In Two Not One. We
can do the same thing here. IF and then COUNTIF and then this time we're going to do this list,
this name, and then blank or the name that we're looking at. I'll Copy here and Paste there.
Now it gives us basically the same information and it lines up so we can easily figure out
where things are. But we can use this to put some conditional highlighting here.
Because notice the name is there and it's in the same position, Row 4 and Row 4. This
one here is in Row 10 and in Row 10. So it's always going to match the same row.
So if we want to do Conditional Highlighting we could. So let's select this cell here since
it's going to be one that matches and I'm going to do Conditional Highlighting, add a rule, and I'm
going to do Text Is and instead of typing something here I'm going to click this button
to refer to this cell. Of course these do match. So since they match we'll have it change to Red
Text. We could see that this changes to Red Text to indicate that it's missing
from the other table. Now all we need to do is use that rule throughout. So I'll click Done here.
I'm going to select all of the cells here by double clicking A and I'm going to go to Show
Highlighting Rules. Then Combine Rules. Then Done. You could see this one now is Red as well. So
for each one of these it actually moves the value here to be the one that's directly across from it.
So we could do the same for this table here. I'll select this first one. I'll
do Conditional Highlighting. Add a Rule. Text Is and then I'm going to click here,
select this cell, and then change it to be Red Text. Done. Now if I select all the cells here,
show Highlighting Rules and Combine the Rules you could see it highlights those. This table
here I don't actually need to look at. It will update just fine. So if I were to actually
change something here so it didn't match anymore you can see how now
both of these are highlighted because both of these are missing from the other list.
So I could just move this aside or maybe move it to another sheet where it's out of the way.
So you could see here I've got Ages as well as Names. What if I wanted to make a comparison
between multiple values. Well, you can do that using COUNTIFS, that's IFS with an S.
So we have COUNTIF here. Let's start over again but this time let's use COUNTIFS and
this time it's Test Values Condition. Test Values Condition, etc. As many as we want.
So I could do IF as before and then COUNTIFS and then let's do Test Values. So let's do the names
and compare it to the name, and let's do the ages and compare it to the age right here.
Now it's going to have to match both of those. Then if it matches, nothing.
If it doesn't match then we're going to use the name as before, like that.
So now we can select and Paste it in here. Now we can see that Somera Phelps doesn't
match anymore because the ages are different. Let's go and do the same thing for this one.
IF COUNTIFS and this time we're looking to compare those names with this name
and those ages with this age. Then blank and then the name. Like that. Copy
and Paste throughout. You could see the Conditional Highlighting still holds. It's still
doing the same thing here. But now, thanks to the COUNTIFS, it's actually comparing multiple things.
One thing to note is that if you change these tables, so you get new data, you're going to
have to update this table here. It's pretty easy to do. You just need to remember to do it.
So, for instance, let's add a new row here and let's add a name and an age.
You could see it doesn't correctly figure out that this one is missing from that.
I need to repopulate this. The first thing is to make sure I've got enough rows here. So I
need 20 because this one is now 20. The largest one. I need to select those two cells there,
Copy and Paste throughout. Now you can see it matches perfectly. It gives me an error message
here because there is no 20th row there. But that's fine. That doesn't get in the way of things
and now we just need to update the Conditional Highlighting again. So one way to do that is,
assuming that nothing changed in the first row, just select all of these. Show Highlighting Rules
and remove it. The same thing for this. Show Highlighting Rules, remove it,
and then go back again. This time selecting the first row as well as all the rest, and then
Show Highlighting Rules and Combine and the same thing here. Show Highlighting Rules and Combine.
Now with those reapplied the rows will then match across
and the highlighting will be in effect again.
That's one method of comparing two
Tables in Mac Numbers. Hope you found this useful. Thanks for watching.