See if this helps


Sub MatchQuery()

Set OctSht = Sheets("October")
Set NovSht = Sheets("November")
Set OctOnly = Sheets("Oct Only")
Set NovOnly = Sheets("Nov Only")
Set MatchBoth = Sheets("Match")

ID = 123456

Set FoundOct = OctSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

Set FoundNov = NovSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

If FoundOct Is Nothing Then

If FoundNov Is Nothing Then
MsgBox ("ID : " & ID & " is not found")
Else
'found in November only
With NovOnly
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
NovSht.Range("A" & FoundNov.Row & ":I" & FoundNov.Row).Copy _
Destination:=.Range("A" & NewRow)
End With
End If
Else
If FoundNov Is Nothing Then
'Found in October Only
With OctOnly
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
OctSht.Range("A" & FoundOct.Row & ":I" & FoundOct.Row).Copy _
Destination:=.Range("A" & NewRow)
End With
Else
'Found in both October and November
With MatchBoth
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
OctSht.Range("A" & FoundOct.Row & ":I" & FoundOct.Row).Copy _
Destination:=.Range("A" & NewRow)
NovSht.Range("A" & FoundNov.Row & ":I" & FoundNov.Row).Copy _
Destination:=.Range("J" & NewRow)
End With
End If
End If

End Sub


"Neon520" wrote:

Hi Everyone,

I need to do a Query in Excel that I used to do in Access before.
I'm using Office 2004 for Mac, so I don't have Access to do the Query.

There are two set of data, let's say Oct and Nov, they both have Key Field
(ID#). what I need is to find out if the Key Field are matched in both
sheets, then copy the whole line let's say Column A-I to a separate sheet
called "Match", Column A-I is Oct and Append Nov in Col K-S, so they are side
by side in that sheet.

If the Key field is found in Oct and Not in Nov then copy that lines to a
separate sheet called "Oct Only" and the same thing for "Nov Only" if only
found in Nov.

Thanks a lot for any help with this.
Oh, just a tag-on question, is there a way to find UNIQUE record in Excel
just like in Access with the "Key Field" property? If so, how?

Neon520