Macro to Audit Two Columns of Data for Errors
 

I am trying to write a macro that would audit two columns of data for input
errors. A small data set appears below:

Code Fee
A5569 161
A5569 161
A5569 171
A2168 265
A2168 265
A2168 265
B3389 199
B3389 199

The macro should scan the first column for the same code. For each group of
same codes, it should average the corresponding values. If the average value
differs from the first value in the group, the macro should highlight the
codes and values.

For example, the macro would first scan column 1 for the first code in the
column - code A5569. It should then average the values corresponding with
that code (average of 161, 161, and 171 = 164.33). Because 164.33 differs
from 161 or 171, it should highlight code A5569 and the corresponding values
161, 161, and 171.

Once finished with that group, it should move to the next and scan code
A2168. It should average the values corresponding with that code (average of
265, 265, and 265 = 265). Because that average equals the values, there is
no input error and the macro should loop to the next code in the column -
B3389 and perform the same process again.

I'm stuck on how to analyze the data within a group of same codes and then,
once finished, move on to the next group.

Thanks for any help that can be provided.

--
John Mansfield

Macro to Audit Two Columns of Data for Errors
 

You don't need a macro for this. Let's assume your code data is in A2:A10
and the Fee data is in B2:B10.

Select the entire range (A2:B10) starting at either A2 or B2.

Format - Conditional Format
Formula is

=SUMPRODUCT(--($A$2:$A$10=$A2),($B$2:$B$10))/COUNTIF($A$2:$A$10,$A2)<$B2

HTH,
Barb Reinhardt
"John Mansfield" wrote:

I am trying to write a macro that would audit two columns of data for input
errors. A small data set appears below:

Code Fee
A5569 161
A5569 161
A5569 171
A2168 265
A2168 265
A2168 265
B3389 199
B3389 199

The macro should scan the first column for the same code. For each group of
same codes, it should average the corresponding values. If the average value
differs from the first value in the group, the macro should highlight the
codes and values.

For example, the macro would first scan column 1 for the first code in the
column - code A5569. It should then average the values corresponding with
that code (average of 161, 161, and 171 = 164.33). Because 164.33 differs
from 161 or 171, it should highlight code A5569 and the corresponding values
161, 161, and 171.

Once finished with that group, it should move to the next and scan code
A2168. It should average the values corresponding with that code (average of
265, 265, and 265 = 265). Because that average equals the values, there is
no input error and the macro should loop to the next code in the column -
B3389 and perform the same process again.

I'm stuck on how to analyze the data within a group of same codes and then,
once finished, move on to the next group.

Thanks for any help that can be provided.

--
John Mansfield

Macro to Audit Two Columns of Data for Errors
 

In case you do need a macro, here is one:

Sub chdAvg()
Dim v As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Worksheets(1).Range("A1:A" & lr)
For Each c In myRange
If c < "" Then
aRng = c.Address
If Range(aRng).Value = Range(aRng).Offset(1, 0).Value Then
x = Range(aRng).Offset(0, 1).Address
Range(aRng).Activate
Do While ActiveCell = ActiveCell.Offset(1, 0)
ActiveCell.Offset(1, 0).Activate
Loop
y = ActiveCell.Offset(0, 1).Address
Set avRng = Range(x & ":" & y)
v = Application.WorksheetFunction.Average(avRng)
If v < Range(aRng).Offset(0, 1).Value Then
avRng.Interior.ColorIndex = 6
End If
End If
End If
Next
End Sub


"John Mansfield" wrote:

I am trying to write a macro that would audit two columns of data for input
errors. A small data set appears below:

Code Fee
A5569 161
A5569 161
A5569 171
A2168 265
A2168 265
A2168 265
B3389 199
B3389 199

The macro should scan the first column for the same code. For each group of
same codes, it should average the corresponding values. If the average value
differs from the first value in the group, the macro should highlight the
codes and values.

For example, the macro would first scan column 1 for the first code in the
column - code A5569. It should then average the values corresponding with
that code (average of 161, 161, and 171 = 164.33). Because 164.33 differs
from 161 or 171, it should highlight code A5569 and the corresponding values
161, 161, and 171.

Once finished with that group, it should move to the next and scan code
A2168. It should average the values corresponding with that code (average of
265, 265, and 265 = 265). Because that average equals the values, there is
no input error and the macro should loop to the next code in the column -
B3389 and perform the same process again.

I'm stuck on how to analyze the data within a group of same codes and then,
once finished, move on to the next group.

Thanks for any help that can be provided.

--
John Mansfield