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.

Finding errors
 

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)

Finding errors
 

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

" 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)