creating matching lists between sheets for comparison
 

Guys,

Hopefully someone can help with this. Basically I have two worksheets
that I wrote some code to compare the values for each item. Only I have
come across a problem when a row may be missing from the data. For
example:

SheetA
Customer1 Name X Y
Customer2 Name S G
Customer3 Name A B

SheetB
Customer1 Name X B
Customer2 NAME S G
Customer3 Name A B

Currently the macro outputs a sheet highlighting where the values between
the sheets in the respective cell does not match...
e.g. IF Customer1Name_ShtA=Customer1Name_ShtB then ShtC.cell.value =
"Error"

BUT I have a problem where there is sometimes a record missing from one
of the reports making the if statement run out of synch. This is a
problem as it means i have to manually go through some massive datasets
adding rows manually until the rows match between the two sheets.

Anyone got any good ideas how i could get excel to do this automatically.
Was thinking of buinding some sort of unique key and running comparisons
etc but this will adjust the sort. This is not necessary and could cause
problems due to the source data and i simply want to add blank rows until
the next row matches correctly. I hope this has been made clear.

Cheers - Chris

Email: take out the obvious sections of the email address.

creating matching lists between sheets for comparison
 

Chris,

From your example, it appears the data is mismatched, but all three
customers are on both sheets. So, I am not sure what you mean by a row
is missing.

Alan

Chris Shaw wrote:
Guys,

Hopefully someone can help with this. Basically I have two worksheets
that I wrote some code to compare the values for each item. Only I have
come across a problem when a row may be missing from the data. For
example:

SheetA
Customer1 Name X Y
Customer2 Name S G
Customer3 Name A B

SheetB
Customer1 Name X B
Customer2 NAME S G
Customer3 Name A B

Currently the macro outputs a sheet highlighting where the values between
the sheets in the respective cell does not match...
e.g. IF Customer1Name_ShtA=Customer1Name_ShtB then ShtC.cell.value =
"Error"

BUT I have a problem where there is sometimes a record missing from one
of the reports making the if statement run out of synch. This is a
problem as it means i have to manually go through some massive datasets
adding rows manually until the rows match between the two sheets.

Anyone got any good ideas how i could get excel to do this automatically.
Was thinking of buinding some sort of unique key and running comparisons
etc but this will adjust the sort. This is not necessary and could cause
problems due to the source data and i simply want to add blank rows until
the next row matches correctly. I hope this has been made clear.

Cheers - Chris

Email: take out the obvious sections of the email address.