Here's how to compare two tables to see which rows are missing from one or the other. You can also compare two tables using multiple columns. Conditional highlighting can show you which rows don't match.
You can also watch this video at YouTube.
Watch more videos about related subjects: Numbers (197 videos).
You can also watch this video at YouTube.
Watch more videos about related subjects: Numbers (197 videos).
Want to know more about how to use Numbers on your Mac?Video Transcript
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.
Excellent and creative use of Countif and countifs. I was not familiar with the second one but will have use for it in the future. Thanks
In reference to this tutorial and your 12/02/2020 tutorial "How To Find Duplicates In Numbers", is there a method to delete filtered rows without deleting any unfiltered/hidden rows? For example, filtering a table to show only "Duplicates" and deleting only those and not rows in between? MS Excel has the feature to delete "only visible rows" and I am not seeing this option with Numbers (v11.2).
Eric: Instead of filtering, sort. Sort by that column that indicates duplicates so then all of the duplicates are together. Select those and delete those rows.
Thank you Gary. I tried that as well, however, I am looking to keep the original order of the list which happens to be randomized and not in a sorted pattern. If I sort the list, delete the 'Duplicates', can I then return the list to its originally intended order?
Eric: You can sort by the true/false duplicate column only, then delete. The remaining ones should still be in the same order they were before. Try it and see. You can always undo.
That was it! Thank you. I used your formula from the 12/02/2020 tutorial; =IF(MATCH($A2,A,-1)=ROW(),"","Duplicate of row "&MATCH($A2,A,−1)). I place that formula in B2 next to A2 that has the suspected duplicates and copied down the column of over 5,000 rows. I then performed Organized > Sort > Sort Selected Rows > Row B > Ascending. Found the duplicates at the bottom and deleted the rows. None of my random cell data in column A was out-of-place or sorted which is what I wanted. Thank you!