Finding errors

Hi

I posted this question a couple of weeks ago but could not work out how to
use the response or it did not work, so I guess I may have not explained it
well enough.

I have a list of Items, all coded, so column A is the codes and column B is
the item description.

I have a second list of coded items where column C is the code and column D
is the item description.

The two sets of columns do not have to match, some items appear on one set
but not the other, my problem is that the codes, if they appear need to have
the exact same item description.

Each column is several thousand cells long..
Is there some way I can search the four columns and tell when a code
appearing in A and C has a different Item description.

write a small program in VBA. To do so hit Alt+F11 to open up the
editor. Then goto insert->add module. Copy the following:

Sub findDifferent()

Const MaxRowsA = 3000
Const MaxRowsC = 3000

Dim CodeA(1 To MaxRowsA)
Dim DescriptionA(1 To MaxRowsA)

For i = 1 To MaxRowsA
'get the code from row i, column A
CodeA(i) = Cells(i, 1)
'and get the corresponding description from row i, column B
DescriptionA(i) = Cells(i, 2)
Next

'now go through the codes in column C
For j = 1 To MaxRowsC
codeC = Cells(j, 3)
descriptionC = Cells(j, 4)
'now see if this code occurs somewhere in the first column
For k = 1 To MaxRowsA
If (CodeA(k) = codeC) Then
'the code does match so see if the description matches
If (DescriptionA(k) <> descriptionC) Then
'highlight the discrepency
Cells(j, 4).Interior.Color = RGB(255, 0, 0)
Exit For
End If
End If
Next
Next

End Sub


then run this by clicking the play button. (you should set MaxRowsA and
MaxRowsC to however long these rows are first) It will obviously take a
long time since for each item in C, it has to look through every code
in A to see if it matches. However you could speed it up by using the
code as the index of the array (if this works for your dataset). For
example say Description(Cells(i,1)) = Cells(i,2) and then later if
Description(Cells(i,3)) <> Cells(i,4) then ... (you have a discrepency)

Thank you so much for your help, you have saved me several hours of work.
It's all done, it took a few seconds.

regards

"[email protected]" wrote:

> write a small program in VBA. To do so hit Alt+F11 to open up the
> editor. Then goto insert->add module. Copy the following:
>
> Sub findDifferent()
>
> Const MaxRowsA = 3000
> Const MaxRowsC = 3000
>
> Dim CodeA(1 To MaxRowsA)
> Dim DescriptionA(1 To MaxRowsA)
>
> For i = 1 To MaxRowsA
> 'get the code from row i, column A
> CodeA(i) = Cells(i, 1)
> 'and get the corresponding description from row i, column B
> DescriptionA(i) = Cells(i, 2)
> Next
>
> 'now go through the codes in column C
> For j = 1 To MaxRowsC
> codeC = Cells(j, 3)
> descriptionC = Cells(j, 4)
> 'now see if this code occurs somewhere in the first column
> For k = 1 To MaxRowsA
> If (CodeA(k) = codeC) Then
> 'the code does match so see if the description matches
> If (DescriptionA(k) <> descriptionC) Then
> 'highlight the discrepency
> Cells(j, 4).Interior.Color = RGB(255, 0, 0)
> Exit For
> End If
> End If
> Next
> Next
>
> End Sub
>
>
> then run this by clicking the play button. (you should set MaxRowsA and
> MaxRowsC to however long these rows are first) It will obviously take a
> long time since for each item in C, it has to look through every code
> in A to see if it matches. However you could speed it up by using the
> code as the index of the array (if this works for your dataset). For
> example say Description(Cells(i,1)) = Cells(i,2) and then later if
> Description(Cells(i,3)) <> Cells(i,4) then ... (you have a discrepency)
>
>