I had to compare two worksheets with identical columnar data with approximately 500 rows in each worksheet. I searched for solutions and although I didn't persist, all I could find was add-ins or third-party software.
I found a way fo comparing two worksheets with small differences in data. Mostly text for numbers; extra spaces or decimal places, etc.
The method invloves looping three steps until satisfied. In my case I had to repeat the process four times, and, I had great success.
The three steps are:
1. Filtering unique values
2. Keeping duplicates and removing unique values
3. Identifying differences; fixing them and repeat the process over again.
I gave different background to both sets of data. One light blue, the other yellow. Only for sanity.
Brought in data from one sheet to the other. Performed Advanced filter with Unique values. That left truly uniques values and dulplicate rows that had dissimilar values.
Sorted by Primary Key.
Used a macro to delete truly unique values in the range leaving behind duplicates. Simple logic against a Primary key.
If Cells(r,1).Value <> Cells(r-1,1).Value AND Cells(r,1).Value <> Cells(r+1,1).Value Then
Cells(r,1).EntireRow.Delete
Highlight two rows with duplicate values, Use Goto, Special, Column differences two identify differences. Fix the differences (for me it was mainly Format, Cells kind of thing).
Repeat.
Total time for a Total of 1000 rows = 30 mins, Four iterations of the loop, found three rows with real problems.
I did only a couple of duplicate rows at a time to identify differences, hence the four iterations.
Hope this is helpful for someone.
Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents
I found a way fo comparing two worksheets with small differences in data. Mostly text for numbers; extra spaces or decimal places, etc.
The method invloves looping three steps until satisfied. In my case I had to repeat the process four times, and, I had great success.
The three steps are:
1. Filtering unique values
2. Keeping duplicates and removing unique values
3. Identifying differences; fixing them and repeat the process over again.
I gave different background to both sets of data. One light blue, the other yellow. Only for sanity.
Brought in data from one sheet to the other. Performed Advanced filter with Unique values. That left truly uniques values and dulplicate rows that had dissimilar values.
Sorted by Primary Key.
Used a macro to delete truly unique values in the range leaving behind duplicates. Simple logic against a Primary key.
If Cells(r,1).Value <> Cells(r-1,1).Value AND Cells(r,1).Value <> Cells(r+1,1).Value Then
Cells(r,1).EntireRow.Delete
Highlight two rows with duplicate values, Use Goto, Special, Column differences two identify differences. Fix the differences (for me it was mainly Format, Cells kind of thing).
Repeat.
Total time for a Total of 1000 rows = 30 mins, Four iterations of the loop, found three rows with real problems.
I did only a couple of duplicate rows at a time to identify differences, hence the four iterations.
Hope this is helpful for someone.
Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents