compring two data sets

[Utkarsh]

Hi

Currently I am using Excel to compare two datasets like

Sheet Sheet1
Name Date Type Amount
arc 01-01-2010 x 100
b 01-01-2007 y 25
a 01-03-2008 x 124
a 01-03-2010 x 76

and

Sheet Sheet2
Name Date Type Amount
arc ltd 01-01-2010 x 100
b 01-01-2007 y 20
a 01-03-2008 x 124
d 01-05-2007 x 100
a 01-02-2010 x 76
arc 01-01-2010 x 100

and generating an output like below in Sheet2

Name Date Type Amount All All but Value All but date All but name
Original value Original date Original Name
arc ltd 01-01-2010 x 100 $A$2 arc
b 01-01-2007 y 20 $A$3 25
a 01-03-2008 x 124 $A$4
d 01-05-2007 x 100
a 01-02-2010 x 76 $A$5 01-03-2010
arc 01-01-2010 x 100 $A$2

Wtih nerly 20,000 rows Excel takes a large amount of time to process
this. I m told that this can be done very fast with Access. The row
locations can be substituted by record number as track. Please advise
as to how I cn go about doing this in Access.

My VBA code for ready reference:

*************
Sub compare()
'clear columne
Sheets("Sheet2").Range("E2:K65536").ClearContents

'Find the last row to be evaluated for each sheet
Sheets("Sheet1").Select
lastrow1 = Range("A65536").End(xlUp).Row
Sheets("Sheet2").Select
lastrow2 = Range("A65536").End(xlUp).Row

Sheets("Sheet1").Select
For i = 2 To lastrow1
'Transfer each foled for a row into a variable
rec1 = Sheets("Sheet1").Cells(i, 1).Value
rec2 = Sheets("Sheet1").Cells(i, 2).Value
rec3 = Sheets("Sheet1").Cells(i, 3).Value
rec4 = Sheets("Sheet1").Cells(i, 4).Value
'MsgBox rec1 & rec2 & rec3 & rec4

Sheets("Sheet2").Select
For j = 2 To lastrow2

If Cells(j, 1).Value = rec1 And Cells(j, 2).Value = rec2 And _
Cells(j, 3).Value = rec3 And _
Cells(j, 4).Value = rec4 Then

Cells(j, 5).Value = Sheets("Sheet1").Range("A" & i).Address
Exit For
End If
If Cells(j, 1).Value = rec1 And Cells(j, 2).Value = rec2 And _
Cells(j, 3).Value = rec3 And _
Cells(j, 4).Value <> rec4 Then

Cells(j, 6).Value = Sheets("Sheet1").Range("A" & i).Address
Cells(j, 9).Value = Sheets("Sheet1").Range("D" & i).Value
Exit For
End If

If Cells(j, 1).Value = rec1 And Cells(j, 2).Value <> rec2 And _
Cells(j, 3).Value = rec3 And _
Cells(j, 4).Value = rec4 Then

Cells(j, 7).Value = Sheets("Sheet1").Range("A" & i).Address
Cells(j, 10).Value = Sheets("Sheet1").Range("B" & i).Value
Exit For
End If

If Cells(j, 1).Value <> rec1 And Cells(j, 2).Value = rec2 And _
Cells(j, 3).Value = rec3 And _
Cells(j, 4).Value = rec4 Then

Cells(j, 8).Value = Sheets("Sheet1").Range("A" & i).Address
Cells(j, 11).Value = Sheets("Sheet1").Range("A" & i).Value
End If


Next j
Next i

End Sub

 

[John W. Vinson]

Hi

Currently I am using Excel to compare two datasets like

Sheet Sheet1
Name Date Type Amount
arc 01-01-2010 x 100
b 01-01-2007 y 25
a 01-03-2008 x 124
a 01-03-2010 x 76

and

Sheet Sheet2
Name Date Type Amount
arc ltd 01-01-2010 x 100
b 01-01-2007 y 20
a 01-03-2008 x 124
d 01-05-2007 x 100
a 01-02-2010 x 76
arc 01-01-2010 x 100

and generating an output like below in Sheet2

Name Date Type Amount All All but Value All but date All but name
Original value Original date Original Name
arc ltd 01-01-2010 x 100 $A$2 arc
b 01-01-2007 y 20 $A$3 25
a 01-03-2008 x 124 $A$4
d 01-05-2007 x 100
a 01-02-2010 x 76 $A$5 01-03-2010
arc 01-01-2010 x 100 $A$2

Wtih nerly 20,000 rows Excel takes a large amount of time to process
this. I m told that this can be done very fast with Access. The row
locations can be substituted by record number as track. Please advise
as to how I cn go about doing this in Access.

A Query in Access would probably indeed work well. But your post is confusing:
"row locations can be substituted by record number as track" is meaningless to
me! What's "track"? How can you determine (based on the *CONTENT* of the
record, not its position) which record needs to be compared with which other
record? Did your Sheet2 example word wrap?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Utkarsh]

To explain it more clearly there are two data sets separated by time
(let us say, OLD and NEW). I am trying to track changes between the
two data sets. Unfortunately, there is nothing that uniquely
identifies record and hence I need to compare based on content rther
than postion. That is why it is sueful for me to know which record in
OLD matches which record in NEW. Thanks

 

[John W. Vinson]

An Access table is an unordered "heap" of records, so position is irrelevant
in any case.

My question is: if you had two stacks of index cards with the information
printed on them, how would you match the cards? You say there is nothing that
uniquely identifies the record, and it seems that any one of the fields can be
erroneous. How can Access - or any computer program, or any person - identify
which record in Pile A should be compared with a given record in Pile B?

You can join by any field, or by any combination of fields; but there must be
SOME way (using those fields) to unambiguously match records, before you can
compare the values in those records.

To explain it more clearly there are two data sets separated by time
(let us say, OLD and NEW). I am trying to track changes between the
two data sets. Unfortunately, there is nothing that uniquely
identifies record and hence I need to compare based on content rther
than postion. That is why it is sueful for me to know which record in
OLD matches which record in NEW. Thanks

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com