Unmatched Query
 

Hi,

My customer wants an "Unmatched Query" done in Excel. He doesn't want to
use MS Access to create this. Is there a Macro or inbuilt Excel function
that would let us do this?

Scenario:
In a single workbook, there are 3 spreadsheets - spr1, spr2, and spr3. spr1
and spr2 have just one columns of names in them. My customer wants to find
all the names in spr2 that does not match with the names in spr1 and transfer
the result in spr3.

Is this possible? Please help.

Unmatched Query
 

Sub unmatched()

spr3RowCount = 1
spr2RowCount = 1
With Sheets("spr2")
Do While .Range("A" & spr2RowCount) < ""
FindNum = .Range("A" & spr2RowCount)
With Sheets("spr1")
Set c = .Columns("A:A").Find(what:=FindNum, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Sheets("spr3").Range("A" & spr3RowCount) = FindNum
spr3RowCount = spr3RowCount + 1
End If

End With
spr2RowCount = spr2RowCount + 1
Loop
End With

End Sub


"vrk1" wrote:

Hi,

My customer wants an "Unmatched Query" done in Excel. He doesn't want to
use MS Access to create this. Is there a Macro or inbuilt Excel function
that would let us do this?

Scenario:
In a single workbook, there are 3 spreadsheets - spr1, spr2, and spr3. spr1
and spr2 have just one columns of names in them. My customer wants to find
all the names in spr2 that does not match with the names in spr1 and transfer
the result in spr3.

Is this possible? Please help.

Unmatched Query
 

There is not a single function that does this, but Chip Pearson illustrates
how to do this with a combination of 3 functions assembled into a formula.
Look at the 3rd example on this page for Extracting Elements From One List
Not On Another List.

http://www.cpearson.com/excel/ListFunctions.aspx

Once you have the list on spr3, select that list and Copy/PasteSpecial
Values to remove the formulas and keep the results. Now sort the results to
remove blank rows.

Mike F
"vrk1" wrote in message
...
Hi,

My customer wants an "Unmatched Query" done in Excel. He doesn't want to
use MS Access to create this. Is there a Macro or inbuilt Excel function
that would let us do this?

Scenario:
In a single workbook, there are 3 spreadsheets - spr1, spr2, and spr3.
spr1
and spr2 have just one columns of names in them. My customer wants to
find
all the names in spr2 that does not match with the names in spr1 and
transfer
the result in spr3.

Is this possible? Please help.

Unmatched Query
 

Both the solutions described on this thread gave me the solution that I was
looking for. Thank you both. Hope this would help others searching the
forum.



"Mike Fogleman" wrote:

There is not a single function that does this, but Chip Pearson illustrates
how to do this with a combination of 3 functions assembled into a formula.
Look at the 3rd example on this page for Extracting Elements From One List
Not On Another List.

http://www.cpearson.com/excel/ListFunctions.aspx

Once you have the list on spr3, select that list and Copy/PasteSpecial
Values to remove the formulas and keep the results. Now sort the results to
remove blank rows.

Mike F
"vrk1" wrote in message
...
Hi,

My customer wants an "Unmatched Query" done in Excel. He doesn't want to
use MS Access to create this. Is there a Macro or inbuilt Excel function
that would let us do this?

Scenario:
In a single workbook, there are 3 spreadsheets - spr1, spr2, and spr3.
spr1
and spr2 have just one columns of names in them. My customer wants to
find
all the names in spr2 that does not match with the names in spr1 and
transfer
the result in spr3.

Is this possible? Please help.