Hi,

Here a simple code that will do.

Considerations:
You have a 2 sheet: "OCT" and "NOV" (Variable S1 and S2)
The ID is in column B in both
Data is in col A-B-C only in both

It will take data with same ID from NOV and copy it in COL E-F-G of the
sheet NOV on the row with the same ID.
It will add "MATCH" in col F of NOV if it find a MATCH.

If your file is longer then 500, you can change it in the code (2
instances)

Sub Match()
Dim x1 As Integer
Dim x2 As Integer
Dim id As String
Dim S1 As String
Dim S2 As String

'Sheets to compare
S1 = "OCT"
S2 = "NOV"

For x1 = 2 To 500
Sheets(S1).Select
id = Trim(Cells(x1, 2))
If Len(id) < 0 Then
Sheets(S2).Select
For x2 = 2 To 500
If Trim(Cells(x2, 2)) = id Then
Range("A" & x2 & ":C" & x2).Select
Selection.Copy
Cells(x2, 6) = "MATCH"
Sheets("OCT").Select
Range("E" & x1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Exit For
End If
Next x2
End If
Next x1

End Sub

Charlie
'Opener Consulting Home' (http://www.OpenerConsulting.com)

Neon520;172376 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


--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47732