The Excel Match Function – Comparing two Excel Spreadsheets

I was asked today to perform a comparison of two spreadsheets containing stock reference numbers.  As it had been a while since i’d worked with excel macros, this triggered a little bit of research.
 
The simplest way to achieve this is by using the match function in Excel.  Here’s a couple of examples that match the ‘C’ column in two sheets.
 
Sheet 1
 
=IF(ISNA(MATCH(C2,Sheet2!C:C,0)),””Unique””,””in both sheets””)
 
Sheet 2
 
=IF(ISNA(MATCH(C2,Sheet1!C:C,0)),””Unique””,””in both sheets””)
 

An alternative way to achieve this is by writing a brief macro.  Here’s an example of one which would provide the same functionality, comparing two sheets and generating a report on the differences.
 
dim data1() as string
dim data2() as string
dim missing() as string
set fsob=createobject(“”scripting.filesystemobject””)
set txtfile=fsob.opentextfile(filepath,1)
x=0
while not txtfile.atendofstream
redim preserve data1(x)
application.statusbar=””reading data1 line “” & x
data1(x)=txtfile.readline
wend
txtfile.close
set txtfile=fsob.opentextfile(filepath2,1)’this is second sheet with missing data
x=0
while not txtfile.atendofstream
redim preserve data2(x)
application.statusbar=””reading data2 line “” & x
data2(x)=txtfile.readline
wend
txtfile.close
dim found as boolean
found=false
x=0
for looper=lbound(data1) to ubound(data1)
application.statusbar=””comparing data line “” & looper
for loopy=lbound(data2) to ubound(data2)
if data1(looper)=data2(loopy) then found=true
next loopy
if not found then
redim preserve missing(x)
missing(x)=data1(looper)
x=x+1
end if
found=false
next looper
‘now ouptut missing data
set txtfile=fsob.createtextfile(filepath3)’path to save missing data
for looper=lbound(missing) to ubound(missing)
application.statusbar=””outputting missing data line “” & looper
txtfile.writeline missing(looper)
next looper
txtfile.close
application.statusbar=false
 
 
 
More information about the match function….
 
The Excel Match Function
 
You have two or more lists in Excel and you want to find out whether a value in one list exists in another. The MATCH function is what you’re in need of.
 
MATCH

The MATCH function accepts 3 parameters: (1) lookup_value; this is the value you want to find. (2) lookup_array; this is the list that you want to search through. (3) match_type; this is optional and specifies if you want an exact match or a less/greater than match.
 
MATCH will try to find the lookup_value you specify in the list you specify (lookup_array) and return the relative position in the list if it finds a match. The relative position means that if your list starts at A5 and the MATCH function finds a match with the first value in your list, it will return a 1 not a 5. So it doesn’t return the row it found the match in, just the position within the list.
Match Type
 
In my experience, unless you’re working with numbers then you will usually want to set match_type to 0, which means only find exact matches. If you’re trying to match text then this is your best bet.
 
Another thing to note is that MATCH will return the relative position of the first match it finds, so if you have duplicate values in the list, MATCH will only return the position of the first one.
 
Example
 
Figure 1 shows a spreadsheet with two lists (List 1 & List 2). The formula for cell E3 in the Match? column is =MATCH(D3,B$3:B$8,0). This tells Excel to lookup the value in D3 (Kiwi) and check to see if it is in List 1 which goes from B3 to B8. In this case it doesn’t find a match so it returns #N/A.
However, in this example it does find two matches, Lime and Orange at positions 6 and 2 in the list. The formula in cell E5 is =MATCH(D5,B$3:B$8,0) which does find a match and returns a 6.
Hint: Make sure you include the $ when specifying the list to look in, in this case B$3:B$8. This will keep Excel from automatically changing the list when you copy and paste the formula to find the other values in List 2.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s