Compare and sort VBA

[DRExcel515]

Okay I have two sheets in an excel file. Each sheet is from a different database dump that I need to compare and find differences between the two. In a perfect world I'd like to be able to copy and paste the values into the two sheets and have a macro that will sort the two separate sheets first A-Z in column A, then largest to smallest in column F. Then have the macro go through find any differences in the two sheets and highlight the differences. Lastly would it be possible to somehow after it has found the differences put all the rows with differences on the bottom and any without any highlighted differences on the top. I can do a little VBA and have a code that currently just highlights the differences (Pasted Below). Any help would be much appreciated!

Public Sub CompareSheets()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cell As Range, rng As Range
Set ws1 = Worksheets("FA_Extract_Security_FADS")
Set ws2 = Worksheets("FA_Extract_security_Prod2")
Set rng = ws1.Range("A2:U5528")
For Each cell In rng
Celladdress = cell.Address
If cell <> ws2.Range(Celladdress) Then
cell.Interior.Color = vbYellow
ws2.Range(Celladdress).Interior.Color = vbYellow
End If
Next cell

End Sub

 

[Ken Cowen]

I would do this without VBA, at least at first. You may want to automate with VBA as a second generation effort, but, to start I think you need to:

1. Add a formula to the right of all your data columns that concatenates all the columns. Do this on both sheets. If your data goes to from column a to column u, you have 21 columns to combine; no big deal.

2. Add a formula to the right of the big concatenation formula that MATCHes the concatenated column in one sheet in the other sheet. Require and exact match; that way you will not only know that a matching data row exists or not, but you will know where to find it if it does exist

3. Add conditional formatting to highlight the rows that have errors in the MATCH formula column.

4. Sort by the MATCH formula column to get all the non-matching rows together.

Once you have a system working, you may want to automate this process with some code.

I assumed you are checking the data row vs row. If that is incorrect and you are really checking cell by cell, then, of course this won't work. But then I think you need to explain a little more about what a match is; does it have to be the same column or can the data be anywhere or whatever.

I hope this helps.

Ken