ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   ACCESS Query in EXCEL (https://www.excelbanter.com/excel-programming/422123-access-query-excel.html)

Neon520
ACCESS Query in EXCEL
 
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
ACCESS Query in EXCEL
 
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

Neon520
ACCESS Query in EXCEL
 
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" wrote:

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

joel
ACCESS Query in EXCEL
 
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" wrote:

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" wrote:

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

Charlie[_12_]
ACCESS Query in EXCEL
 

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

Neon520
ACCESS Query in EXCEL
 
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" wrote:

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" wrote:

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" wrote:

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

joel
ACCESS Query in EXCEL
 
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.

"Neon520" wrote:

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" wrote:

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" wrote:

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" wrote:

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


All times are GMT +1. The time now is 05:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com