Compare two sets of data

Hi all,

I have two sets of data, one set on sheet1 and the other on sheet2, both
containing the same ID numbers and the same fields that contain long
integers.

The trouble is that sheet1 contains about 45,000 records, and sheet 2
contains about 46,000 records, and each record does not correspond to the
data on the other sheet (i.e a record with ID_no 221 may start from A234 in
sheet1, but starts from A236 from sheet2).

Sheet2 contains records that are not in Sheet1, so if I sort both sets of
data by ID_no, the data will correspond to the same cell references.

What i want to do is compare the two sets of data, using the ID_no as the
unique ID to identify which records are identical and which records contain
slightly different data (i.e. ID_no 1 in sheet1 contains the integer 500 in
cell B2, but in sheet2, the figure is 501 for ID_no 1.

Is there a piece of code i can run that outputs all the non-identical
records on another worksheet?

Hope I have explained the problem well enough, any help would be much
appreciated.

Many thanks,


Pinda187

Use the vlookup function to find matching ID's.

"Bhupinder Rayat" wrote:

> Hi all,
>
> I have two sets of data, one set on sheet1 and the other on sheet2, both
> containing the same ID numbers and the same fields that contain long
> integers.
>
> The trouble is that sheet1 contains about 45,000 records, and sheet 2
> contains about 46,000 records, and each record does not correspond to the
> data on the other sheet (i.e a record with ID_no 221 may start from A234 in
> sheet1, but starts from A236 from sheet2).
>
> Sheet2 contains records that are not in Sheet1, so if I sort both sets of
> data by ID_no, the data will correspond to the same cell references.
>
> What i want to do is compare the two sets of data, using the ID_no as the
> unique ID to identify which records are identical and which records contain
> slightly different data (i.e. ID_no 1 in sheet1 contains the integer 500 in
> cell B2, but in sheet2, the figure is 501 for ID_no 1.
>
> Is there a piece of code i can run that outputs all the non-identical
> records on another worksheet?
>
> Hope I have explained the problem well enough, any help would be much
> appreciated.
>
> Many thanks,
>
>
> Pinda187

a start would be to look at the techniques on Chip Pearson's page on
Duplicates and Uniques

http://www.cpearson.com/excel/duplicat.htm

You could probably cobble something together from what is written there.


You could also search google groups

http://groups.google.com
go to advanced search and search on compare in

Microsoft.public.excel.programming

--
Regards,
Tom Ogilvy

"Bhupinder Rayat" <[email protected]> wrote in
message news:[email protected]...
> Hi all,
>
> I have two sets of data, one set on sheet1 and the other on sheet2, both
> containing the same ID numbers and the same fields that contain long
> integers.
>
> The trouble is that sheet1 contains about 45,000 records, and sheet 2
> contains about 46,000 records, and each record does not correspond to the
> data on the other sheet (i.e a record with ID_no 221 may start from A234
in
> sheet1, but starts from A236 from sheet2).
>
> Sheet2 contains records that are not in Sheet1, so if I sort both sets of
> data by ID_no, the data will correspond to the same cell references.
>
> What i want to do is compare the two sets of data, using the ID_no as the
> unique ID to identify which records are identical and which records
contain
> slightly different data (i.e. ID_no 1 in sheet1 contains the integer 500
in
> cell B2, but in sheet2, the figure is 501 for ID_no 1.
>
> Is there a piece of code i can run that outputs all the non-identical
> records on another worksheet?
>
> Hope I have explained the problem well enough, any help would be much
> appreciated.
>
> Many thanks,
>
>
> Pinda187

Thanks tom, but pearson's formulas and code are no help to what i'm trying to
do. I'll keep pluggin away, thanks again.

Pinda187

"Tom Ogilvy" wrote:

> a start would be to look at the techniques on Chip Pearson's page on
> Duplicates and Uniques
>
> http://www.cpearson.com/excel/duplicat.htm
>
> You could probably cobble something together from what is written there.
>
>
> You could also search google groups
>
> http://groups.google.com
> go to advanced search and search on compare in
>
> Microsoft.public.excel.programming
>
> --
> Regards,
> Tom Ogilvy
>
> "Bhupinder Rayat" <[email protected]> wrote in
> message news:[email protected]...
> > Hi all,
> >
> > I have two sets of data, one set on sheet1 and the other on sheet2, both
> > containing the same ID numbers and the same fields that contain long
> > integers.
> >
> > The trouble is that sheet1 contains about 45,000 records, and sheet 2
> > contains about 46,000 records, and each record does not correspond to the
> > data on the other sheet (i.e a record with ID_no 221 may start from A234
> in
> > sheet1, but starts from A236 from sheet2).
> >
> > Sheet2 contains records that are not in Sheet1, so if I sort both sets of
> > data by ID_no, the data will correspond to the same cell references.
> >
> > What i want to do is compare the two sets of data, using the ID_no as the
> > unique ID to identify which records are identical and which records
> contain
> > slightly different data (i.e. ID_no 1 in sheet1 contains the integer 500
> in
> > cell B2, but in sheet2, the figure is 501 for ID_no 1.
> >
> > Is there a piece of code i can run that outputs all the non-identical
> > records on another worksheet?
> >
> > Hope I have explained the problem well enough, any help would be much
> > appreciated.
> >
> > Many thanks,
> >
> >
> > Pinda187
>
>
>

Try following.

It's a general routine that's very fast and convenient
(the input arrays must contain unique ID's.)

Be aware that the returned arrays are 0 based.
ubound = -1 when empty.


Sub DemoMatchCols()
Dim vMatches
vMatches = ArrayMatcher(Range("a:a"), Range("b:b"))

If UBound(vMatches(0)) > -1 Then
Range("d1").Resize(1 + UBound(vMatches(0))) = _
Application.Transpose(vMatches(0))
End If
If UBound(vMatches(1)) > -1 Then
Range("e1").Resize(1 + UBound(vMatches(1))) = _
Application.Transpose(vMatches(1))
End If
If UBound(vMatches(2)) > -1 Then
Range("f1").Resize(1 + UBound(vMatches(2))) = _
Application.Transpose(vMatches(2))
End If
End Sub


Function ArrayMatcher(ByVal List1 As Variant, _
ByVal List2 As Variant, _
Optional bIgnoreCase As Boolean = True)
'compares the values from 2 arrays
'and returns an array of 3 arrays of
'unique items(items left, items both, items right)
'
'author keepITcool excel.programming aug 9th,2005

'requires a reference to Microsoft Scripting Runtime
Dim dic(3) As Scripting.Dictionary
Dim itm, key, res
Dim i As Integer

For i = 0 To 3
Set dic(i) = New Dictionary
dic(i).CompareMode = IIf(bIgnoreCase, TextCompare, BinaryCompare)
Next

If Not IsArray(List1) Then Exit Function
If Not IsArray(List2) Then Exit Function
If Not IsArray(List1) Then Exit Function
If Not IsArray(List2) Then Exit Function
If TypeName(List1) = "Range" Then List1 = _
Intersect(List2.Parent.UsedRange, List1).Value
If TypeName(List2) = "Range" Then List2 = _
Intersect(List2.Parent.UsedRange, List2).Value

On Error Resume Next
'loop List1 and add all unique items to dic(3)
'dic(3) will be discarded later
For Each itm In List1
dic(3).Add CStr(itm), itm
Next

'loop List2:
'If found in dic(3) then add to dic(1) else add to dic(2)
For Each itm In List2
If dic(3).Exists(CStr(itm)) Then
dic(1).Add CStr(itm), itm
Else
dic(2).Add CStr(itm), itm
End If
Next

'loop dic(3):
'if not found add to dic(0)
For Each key In dic(3)
If Not dic(2).Exists(key) Then
dic(0).Add key, dic(3)(key)
End If
Next
Set dic(3) = Nothing
dic(2).Remove (vbNullString)
dic(1).Remove (vbNullString)
dic(0).Remove (vbNullString)

ReDim res(2)
res(0) = dic(0).Items
res(1) = dic(1).Items
res(2) = dic(2).Items
ArrayMatcher = res

End Function




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bhupinder Rayat wrote :

> Hi all,
>
> I have two sets of data, one set on sheet1 and the other on sheet2,
> both containing the same ID numbers and the same fields that contain
> long integers.
>
> The trouble is that sheet1 contains about 45,000 records, and sheet 2
> contains about 46,000 records, and each record does not correspond to
> the data on the other sheet (i.e a record with ID_no 221 may start
> from A234 in sheet1, but starts from A236 from sheet2).
>
> Sheet2 contains records that are not in Sheet1, so if I sort both
> sets of data by ID_no, the data will correspond to the same cell
> references.
>
> What i want to do is compare the two sets of data, using the ID_no as
> the unique ID to identify which records are identical and which
> records contain slightly different data (i.e. ID_no 1 in sheet1
> contains the integer 500 in cell B2, but in sheet2, the figure is 501
> for ID_no 1.
>
> Is there a piece of code i can run that outputs all the non-identical
> records on another worksheet?
>
> Hope I have explained the problem well enough, any help would be much
> appreciated.
>
> Many thanks,
>
>
> Pinda187