Share this content
32

Compare 2 lists

Compare 2 lists

Didn't find your answer?

I have 2 lists of numbers.  Many of them are the same, but I want to find out which are missing from list A.  So they might look something like:

List A:

12232

15353

54684

51564

List B:

12232

54684

99853

55887

All I want to see is if there are numbers in list B that aren't on list A.  I'm not worried about the reverse - it can appear on list A and not B and that doesn't matter.  How can I easily compare the two lists? So in the above example, I want 99853 & 55887 somehow highlighted or conditionally formatted so that I can add them to list A.

I am using Excel 2013 and don't really want to use a macro as the workbook needs to be shared and this causes problems with other users.

Thanks in advance!

Sharon

Replies (32)

Please login or register to join the discussion.

avatar
By Old Greying Accountant
26th Oct 2015 16:40

Beyond my ken ...

... but there is a Excel forum under the discussions tag where you may get a quicker response.

Thanks (0)
avatar
By v.knazevs
26th Oct 2015 16:45

V-llokup maybe?
V-lookup maybe?

Thanks (0)
By cheekychappy
26th Oct 2015 16:56

Vlookup

Cell Detail

A1 List A:

A2 12232

A3 15353

A4 54684

A5 51564

A6 List B:

A7 12232

A8 54684

A9 99853

A10 55887

 

Vlookup in B2:

=vlookup(A2,$A$7:$A$10,1,0)

 

If it returns the same number it means that it is in List B.

 

I'm sure there are more elegant ways of achieving the same result.

   

Thanks (0)
avatar
By v.knazevs
26th Oct 2015 17:25

if function?
=if(value"list A" = value"listB", "yes", "no")

upd: will work only in some cases

Thanks (0)
Out of my mind
By runningmate
26th Oct 2015 17:52
Thanks (0)
avatar
By paulwakefield1
26th Oct 2015 17:43

One option

is to set up a conditional format in list B in the form:

 

=COUNTIF($A$1:$A$5,C1)=0

 

where A1:A5 is List A and C1 is the first cell in List B.

 

Thanks (0)
avatar
By jimmercy
26th Oct 2015 20:17

Has

to be 2 vlookups, 1st looking up an item in list A in list B, and the second looking up an item in list B in list A

Thanks (0)
avatar
By thehaggis
27th Oct 2015 00:46

It is built in to 2013

Under conditional formatting there is a pre-set option: Go to Highlight cell rules  --> Duplicate values"

Thanks (1)
avatar
By paulsaville
27th Oct 2015 07:25

There is a function
Put the lists on top of one another in the same column, no spaces
Click on one of the cells
Then go to the Data tab
Remove duplicates
Job done!

Thanks (1)
avatar
By Weald Accountant
29th Oct 2015 12:45

paulsaville - With your method the

paulsaville - With your method of 'Remove duplicates' the resulting list would also include numbers in List A that were not in List B, so the result would not be what the OP requested. They only want to know what numbers from list B are NOT in list A.

Thanks (0)
avatar
By paulwakefield1
27th Oct 2015 07:43

I really like the

last two solutions above. With thehaggis's idea, choose the "Unique" values option so only the missing ones are highlighted. It is available from XL2007. (Memo to self: I really must experiment more with the presets instead of defaulting to writing my own formulae).

Paulsaville's is a great solution if you don't need to review the items before adding them to the list.

Thanks (0)
Brunel
By Brunel
27th Oct 2015 08:22

MS Access for bigger lists

Microsoft Access has a Mismatched Query Wizard, useful when the lists to compare are huge and Excel runs out of steam (or exceeds the maximum row count), it also has an import from Excel feature, but it's a steep learning curve.

Thanks (1)
Simon Hurst
By Simon Hurst
27th Oct 2015 08:39

Power Query/ Get & Transform

If you only need to highlight the non-matches then many of the above suggestions are useful, although I'm not sure they all distinguish between which list the numbers are missing from. A different approach is possible using Power Query (or if you have Excel 2016 Get & Transform). Load List A and List B as separate queries. Merge the two queries with List B as the first query matching to List A and not choosing only to show matching rows. Expand List A and select only 'nulls'. If I've got the logic round the right way, that should show all the B items where there is no match in A. Rather than just mark the non-matches, this will create a separate list of non-matches that can be refreshed to reflect changes in either list.

Thanks (0)
avatar
By James_Whitelegg
29th Oct 2015 12:05

Asap utilities has excellent facilities for handling duplicates

Thanks (0)
avatar
By SHCTax
29th Oct 2015 12:09

Countif by far the easiest

Paulwakefield1 has the quickest and easiest answer. Several of the responses above simply don't work in a lot of cases (all those using the 'duplicates' function, for a start).

If list A is in column A and list B in column B, putting the formula suggested by paulwakefield1 above:

=countif(A:A,B2)=0

in cell C2 (assuming your lists have headers) and copying the formula down the column would give you all cells in list B that don't have a match in list A. Then simply put a filter at the top and filter for all 'True' in column C would give you the list (in column B).

Power Query might work, but for someone that couldn't solve the OP problem I suspect it would be too advanced and prone to error. Countif is very simple and might be something the OP could use in future without assistance.

Thanks (2)
avatar
By paulwakefield1
29th Oct 2015 12:56

Leaping to paulsaville's defence

the OP wants to add items only on List B to List A. As long as no checking is required, the suggested method does work as you end up with a complete unique list.

Thanks (1)
avatar
By Weald Accountant
29th Oct 2015 13:14

You are right to point out that the OP

paulwakefield1 wrote:

the OP wants to add items only on List B to List A. As long as no checking is required, the suggested method does work as you end up with a complete unique list.

You are right to point out that the OP does indeed want to end up with just one longer list. But the ‘Remove Duplicates’ function as described would not allow them to identify specifically what items in list B are missing from List A. This may or may not be important, we don't know. I guess I focused more on the ‘find unique values’ bit rather than the ‘one long list’ bit.

However the ‘remove duplicates’ method suggested would not achieve this end goal without the additional step of adding all the 'unique' values from the original 'remove duplicates' action to the bottom of list A, and then once again performing the 'remove duplicates' action again (some list A values would be in the 'unique values' from the first action, and therefore be duplicated by adding them to the bottom of the list again).

I think the OP has plenty of options now, hopefully one or more is suitable for what they need to do.

David.

Thanks (0)
avatar
By Weald Accountant
29th Oct 2015 13:05

I seem to do this daily, and automatically go to my default function that I use virtually in my sleep which is

 =VLOOKUP(B1,A:A,1,False)

where list A is in column A, List B is in column B and the function is entered in column C in every cell next to values in Column B. People often forget to put 'False' at the end to ensure it is looking for exact matches only.

The numbers you are looking for in column B will be highlighted with #N/A in the corresponding cell in column C which is fine for just trying to identify items for yourself (eg. I often use this if a VAT transaction report doesn't match transactions posted to VAT account in the nominal, so I need to locate the error), but can be a little messy if you want to share the work with someone else. If you wanted column C to literally show nothing unless you there is a missing value, and you want it to say ‘missing from list A’ or some other comment then you can easily do this as follows:

=IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"Missing from list A","")

This appears more complicated but is straight forward once you have used it a few times and does allow you to make the analysis on your lists more presentable and understandable to other users/reviewers.

For quick and easy, I do also like the =COUNTIF method suggested above and the Conditional formatting to highlight duplicates also mentioned (but on longer lists this may be less use other than again ensuring it is presented well for other users to easily see unique numbers).

Good luck.

 

David.

Thanks (0)
Simon Hurst
By Simon Hurst
29th Oct 2015 13:16

and leaping to Power Query's defence...

I wouldn't disagree with most of what SHCTax added which is why I suggested Power Query as an alternaive rather than a preferred solution. However, I would argue about the 'error prone' issue. The Power Query is not without risks, particularly as the need to refresh queries requires additional discipline compared to automatic formula recalculation. On the other hand, a query based solution has the potential to be a lot less error prone then a 'traditional' cell-based approach. Rather than possibly thousands of individual formula cells, each of which could have an error, or could be accidentally overwritten, using a query involves 3 or 4 procedural steps and no need for the user to construct any formula at all.

I think it would be a shame if Excel users just assumed that database techniques were more difficult than cell-based formulae and thereby missed out on the opportunity to make creating and using spreadsheets quicker, easier and safer.

Thanks (0)
avatar
By paulwakefield1
29th Oct 2015 13:20

Surely though

you append the list first and then remove duplicates so only one action?

Anyway it all goes to show that with Excel there are usually multiple ways to achieve a result!

Thanks (0)
avatar
By Weald Accountant
29th Oct 2015 13:23

Oh yes, sorry my mistake you are right on that. I know what I got muddled with but not worth explaining as it was just some hunger induced idiocy!

Thanks (0)
avatar
By BroadheadAccountants
29th Oct 2015 14:17

Close Excel

Easier.....Close Excel and open Access!

 

You've two tables and then run a query to update etc.

 

Granted Excel can do the job but that doesn't make it the most suited tool.

 

I agree with PaulWakefield1 on using Countif.  VLookup is useful but requires the range to be ordered ascending.

Thanks (0)
avatar
By Ben Lauritson
30th Oct 2015 15:17

VLOOKUP

BroadheadAccountants wrote:

I agree with PaulWakefield1 on using Countif.  VLookup is useful but requires the range to be ordered ascending.

That's only the case (regarding VLOOKUP) if the last parameter is set to TRUE (or any other representation thereof), as then it's looking for approximate matches which is why it needs the range sorted in ascending order. If you have the last parameter set to FALSE (or 0) then the sort order makes no difference because it's only looking for exact matches.

EDIT: Sorry, I see SHCTax has already mentioned this. I can't even claim we were posting at the same time, I just wasn't paying proper attention. Bad Ben.

Thanks (0)
avatar
By SHCTax
30th Oct 2015 14:31

Duplicates methods are assuming

If you use a 'duplicates' based method, you are going to run into problems if there are duplicates in list B or if there are (wanted) duplicates in list A.

I don't think that can really be assumed from the OP although it may in fact be the case.

Edit: Oh and VLOOKUP doesn't require the range to be ascending unless you're using the 'fuzzy' version (i.e. with TRUE or 1 as the last parameter). VLOOKUP would work regardless of order if done correctly (although INDEX(MATCH) is superior to VLOOKUP in every way - but that's another story).

Thanks (1)
Simon Hurst
By Simon Hurst
30th Oct 2015 15:17

Fuzzy VLOOKUP()?

I wouldn't have called the 'True' version of VLOOKUP() fuzzy, it's actually very precise, choosing the largest item that's less than or equal to the lookup value, hence the need for sorting. If you do use the approximate version with an unsorted first column the results can be close to random.

Thanks (0)
avatar
By edhy
23rd Nov 2015 09:52

It is simple as pointed out by David.

=VLOOKUP(B1,A:A,1,False)

An improvement:

=ISNA(B1=VLOOKUP(B1,A:A,1,False),FALSE)

Now where the value is found you will get TRUE else FALSE.

 

Thanks (0)
avatar
By ecmcelhi
23rd Nov 2015 14:45

Put the 2 lists beside each other.

Sort each list numerically.

Assuming no headings, in 3rd column type formula =Exact(a1,b1).

If they don't agree, you'll get 'FALSE' as a result.

Then filter on the 'FALSE' responses.

 

Thanks (0)
avatar
By HASXX
23rd Nov 2015 15:08

Could you use the Match function on table B looking for a match in table A. If the exact match option is taken then if no match found it returns "#N/A" which are your missing entries.

 

Match(lookup value, table array, match function)

 

 

Thanks (0)
avatar
By edhy
25th Nov 2015 08:33

@ecmvelhi, will not work, for this elements of both list should be in the same place, consider list1: A, B, D, E, F and list2 A, B, C, D, E. From C on wards there will be mismatch for D and 

Thanks (1)
avatar
By Nicks9991
25th Nov 2015 10:31

Here's a good one if you want to know how many times something in list B is reoccurring in list A

=sumproduct(--(b1=$a$1:$a$100))

Assumes the numbers start in cells a1 and b1

Thanks (1)
avatar
By edhy
26th Nov 2015 08:28

sumproduct

@Nicks, It works!, would you explain the logic?

Thanks (0)
avatar
By paulwakefield1
26th Nov 2015 11:08

The logic is

that SUMPRODUCT works like an array formula and, in the example, compares each of the cells in A1 - A100 to B1 returning TRUE or FALSE for each one. The minus sign forces the TRUE/FALSE to be 1 or 0 respectively and the second minus sign makes the result positive rather than negative. SUMPRODUCT adds it all up.

One thing to be aware of is that SUMPRODUCT will use a lot more resource than, say, COUNTIF so, if the lists are long and/or the computer is not particulalry powerful, you may get extended calculation times.

Thanks (2)
Share this content

Related posts