Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tracking Errors | Excel Worksheet Functions | |||
Shared sheet errors | Excel Discussion (Misc queries) | |||
Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
conditional forming causing linking errors | Excel Worksheet Functions | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |