Excel : compare lists 2

[Pat1703]

Something looking at first sight simple but I've not found an easy way to perform this yet. X-)

I've two lists of numbers as separate colums in an excel sheet. I need to find out the numbers contained in the fist column which are not in the 2nd.

Thanks for letting me know !

Pat

 

[JVFriederick]

If you want simple ..........

Assume:
Column A contains the more extensive list.
Column B contains some numbers not in column A

In column C, paste the formula
=VLOOKUP(A1,$B$1:$B$1000,1,FALSE)

Then copy to as many rows as within column A

The #N/A will result for all values not found in column B.

 

[DaleWatson123321]

Hi Pat,

The option to VLOOKUP numbers is NOT an option, because it returns the number IMMEDIATELY LESS than the number being looked up. Plus, the numbers are required to be sorted in ascending order.

A potential alternative, to use the TEXT function to convert the numbers to TEXT, and then use the VLOOKUP function, is also NOT a viable option. The VLOOKUP function, in looking up TEXT, acts like the FIND function - i.e. it will return text which is included WITHIN a number, instead of returning "N/A" because the ACTUAL number is NOT in the table.

For example, looking up number "6559", will return "1" or TRUE even if column2 does NOT include "6559", so long as column2 includes a number such as "65593" or "6559467".

For the above reasons, I decided to use the "DCOUNT" function which DOES WORK. I needed to create a small working model which I am prepared to send you, once you provide me with your email address, and to anyone else who is interested.

I am including the VBA code, preceeding with a descriptio of what the code does, but I suggest you are better off in receiving the actual file, as it will be easier to grasp the process.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

===========
DESCRIPTION
===========

The VBA code attached to the button performs the following:

1) Goes to the top of the "exist" column (A15), then to A16

2) Copies the value from the next column ("column1&quot to the cell named "num".

3) "Num" is part of a "criteria" range (called "crit&quot which is used by the database formula located in the cell named "dform".

4) The "dform" formula determines whether the number exists in the second column (with field name of "column2&quot.

5) The VBA code places the result of the "dform" formula into the current cell. Being a DCOUNT formula, if there is NO number, "0" will be entered, and if there is more than 1 idential number, the count will be entered (1 or more).

6) Using the exist column, and the "exist_crit" criteria, the code then extracts the numbers which do NOT exist (those with a "0" in column1) to the "output" range. REMINDER: All data immediately below the "output" range is automatically deleted with each extraction.

========
VBA CODE
========

Dim test As Variant

Sub Do_Extraction()
Application.ScreenUpdating = False
Range("exist_top&quot.Select
ActiveCell.Offset(1, 0).Select
Test_Exist_Column
Extract_Non_Existing
Range("A1&quot.Select
Application.ScreenUpdating = True
End Sub

Sub Test_Exist_Column()
Do
test = ActiveCell.Offset(0, 1).Value
Range("num&quot.Value = ActiveCell.Offset(0, 1).Value
If Range("num&quot.Value = "" Then Exit Sub
ActiveCell.Value = Range("dform&quot.Value
ActiveCell.Offset(1, 0).Select
'Loop Until test = ""
Loop
End Sub

Sub Extract_Non_Existing()
Range("alldata&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("exist_crit&quot, _
CopyToRange:=Range("output&quot, Unique:=False
End Sub

===========
END OF CODE
===========

Again, if you want a copy of the actual file, don't hesitate to ask ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JVFriederick]

Dale - Your advice is not correct. Please read the following from the help file :

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

The formula =VLOOKUP(A1,$B$1:$B$1000,1,FALSE) will work just fine.

 

[nat101]

JVF, can you please explain what column index of 1 accomplishes here?
Thanks

 

[JVFriederick]

It means the column offset. Since your reference range is only a single column, is must be 1. If you were using a reference range say A1:J1000, it could be 1 through 10.

Another way to describe =VLOOKUP(A1,$B$1:$B$1000,1,FALSE) is to say "Using column 1 of range B1:B1000, find an exact match with cell A1, and B1:B1000 does not need to be sorted"

You can sort the results when finished to group the missing values.

Hope this helps.

 

[DaleWatson123321]

Jim Friederick,

Thanks for correcting me on the VLOOKUP function. I obviously was "still thinking in the past" when the "FALSE/TRUE" argument did not exist.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JVFriederick]

I understand. The "prior life" functions of 123 can be hard to forget.