ACCESS Query in EXCEL

[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

 

[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

 

[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

 

[Joel]

Add a new worksheet ID which is where I put the unique ID numbers. I now
have 2 macros. One to get the unique IDs and the 2nd to perform the original
request

Sub GetIDS()

Set OctSht = Sheets("October")
Set NovSht = Sheets("November")
Set IDSht = Sheets("ID")

IDSht.Cells.ClearContents

'Copy October IDs to ID Sheet
OctSht.Columns("B").Copy _
Destination:=IDSht.Columns("A")
'Delete Header Row from ID Sheet
IDSht.Rows(1).Delete

LastRow = IDSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'Copy November IDs to ID Sheet, skip Header
LastRow = NovSht.Range("A" & Rows.Count).End(xlUp).Row
NovSht.Range("B2:B" & LastRow).Copy _
Destination:=IDSht.Range("A" & NewRow)

'sort ID's
With IDSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
key1:=.Range("A1"), _
order1:=xlAscending, _
header:=xlNo

'Remove duplicate IDs
RowCount = 1
Do While .Range("A" & RowCount) <> ""
If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then
.Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End With
End Sub
Sub MatchQuery()

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

LastRow = IDSht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
ID = IDSht.Range("A" & RowCount)

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

Set FoundNov = NovSht.Columns("B").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
Next RowCount
End Sub

 

[Neon520]

Thank you very much, Joel. Your code works great.
I see that in your code, you have to copy ALL the id to one sheet for the
OCT and NOV to compare from there. Is there a way to COMPARE OCT and NOV
without having to creat ID sheet?

Another question that is related to that is: What if the ID in Either sheet
is not unique, I'm not saying all of them but let's say there are a few bad
apples but mix in with duplicate ID or even worst show up more than twice.
How would you address this type of problem?

Is there a way to check and see if the code didn't actually miss out any
data in OCT or NOV? I'm a little panicking when dealing with large volume of
data and this code magically do all the work, but I'm not so sure if it
misses anything, for example skip any lines.

By the way, do you have a website that is http://www.joelonsoftware.com/?
Just curious whether you're the same guy, that's all.

Thank you,
Neon520

 

[Joel]

Usually what most people want with this type of problem is to put everything
in one sheet. First put Oct results in columns A-I then take Nov results and
check if the ID matches and put the data in J+ if there is a match. If there
is no match the add a new row and put Nov results in J+ in the new row. You
don't need to create an ID sheet doing it this way.

You also can do it the way you requested but the code become more
complicated because you are doing a lot of checking.