ACCESS Query in EXCEL

N

Neon520

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
 
J

Joel

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
 
N

Neon520

ID is not only one, they are in both the OCT and NOV.
Here is a data set, maybe you can make sense of this.
OCT sheet
Col A (Date) | Col B (ID) | Col C (Amount) | (row 1 heading)
10/01/08 | 1234 | $147 (row 2)
10/02/08 | 1235 | $258 (row 3)
10/03/08 | 1236 | $369 (row 4)
10/04/08 | 1237 | $138 (row 5)
10/04/08 | 1238 | $138 (row 6)

NOV sheet
Col A (Date) | Col B (ID) | Col C (Amount) | (row 1 heading)
11/01/08 | 1234 | $145 (row 2)
11/02/08 | 1235 | $228 (row 3)
11/03/08 | 1236 | $349 (row 4)
11/04/08 | 1239 | $138 (row 5)
11/04/08 | 12310 | $138 (row 6)

As you can see that each sheet has ID column in them, in this case, lines
that have ID 1234 to 1236 (row 2 to 4) should be copied to Match and put the
data set side by side to compare the amount.

Row 5 & 6 of OCT sheet should be copied to Oct Only because ID 1237 & 1238
don't exist in NOV sheet.
Row 5 & 6 of NOV sheet should be copied to Nov Only because ID 1239 & 1238
don't exist in OCT sheet.

Please keep in mind that IDs don't match line by line as in this example,
that's why I can't compare them line by line in the first place.

Thank you,
Neon520
 
Top