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.

Beyond my ken ...

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

V-llokup maybe?

V-lookup maybe?

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.

if function?

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

upd: will work only in some cases

How about this technique

https://youtu.be/pnjcMksnU9U

or

https://youtu.be/7XPS_c4XK2M

RM

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.

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

It is built in to 2013

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

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!

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.

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.

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.

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.

Asap utilities has excellent facilities for handling duplicates

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.

Leaping to paulsaville's defence

the OP wants to

additems 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

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.

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.

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.

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!

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!

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.

VLOOKUP

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.

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).

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.

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.

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.

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)

@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

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

sumproduct

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

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.