Comparing two column 7 on 2 worksheets in same Workbook

[whassan]

Hiya

I am new to VBA coding and seeking help to acheive column match based on the information below.
I have two worksheets "Sheet1" and "Sheet2" and Sheet3 in same workbook.
There are 26 column on Sheet1 and 26 columns on Sheet2.
On both sheets my data starts from line 3 (A3:Z), line 2 is header on both sheets. Approximate 10k lines of data.
Column 7 on both sheets is Amount(Principal) and there are more than 1 identical amount on each column on both sheets.
I need to compare column 7 (Sheet1) with column 7 (Sheet2) cell by cell.
For example in attached 2 screen shots, Sheet1,Col 7 has 16 x 48016.29 but sheet2,Col 7 has 17 x 48016.29.
In this scenario, Sheet2,Col 1 has one extra 48016.29.
I would like to highlighte extra 48016.29 on Sheet2, copy whole line include header, paste on Sheet3 range A1 and delete copied from line from Sheet2.
Any help in this regard will be highly appreciated.
Thanks.

 

[whassan]

Hiya

I am new to VBA coding and seeking help to acheive column match based on the information below.
I have two worksheets "Sheet1" and "Sheet2" and Sheet3 in same workbook.
There are 26 column on Sheet1 and 26 columns on Sheet2.
On both sheets my data starts from line 3 (A3:Z), line 2 is header on both sheets. Approximate 10k lines of data.
Column 7 on both sheets is Amount(Principal) and there are more than 1 identical amount on each column on both sheets.
I need to compare column 7 (Sheet1) with column 7 (Sheet2) cell by cell.
For example in attached 2 screen shots, Sheet1,Col 7 has 16 x 48016.29 but sheet2,Col 7 has 17 x 48016.29.
In this scenario, Sheet2,Col 1 has one extra 48016.29.
I would like to highlighte extra 48016.29 on Sheet2, copy whole line include header, paste on Sheet3 range A1 and delete copied from line from Sheet2.
Any help in this regard will be highly appreciated.
Thanks.

Hi Everyone

While I am waiting for a response, I am trying to find a solution by searching in forums on the website but still no luck.
I have following macro which works to match data on two sheets cell by cell.
But since I specifically need to match 2 columns cell by cell on two sheets this macro is not meant for that.
Looking forward for a response. cheers


Sub checked()
Dim mycell As Range
Dim mydiff As Integer
Dim shtSheet1 As Worksheet
Dim shtSheet2 As Worksheet
Set shtSheet1 = Worksheets("Sheet1")
Set shtSheet2 = Worksheets("Sheet2")
For Each mycell In shtSheet2.UsedRange
If Not mycell.Value = shtSheet1.Cells(mycell.Row, mycell.Column).Value Then
mycell.Interior.Color = vbRed
mydiffs = mydiffs + 1
End If
Next
End Sub

 

[whassan]

Hi All
I have managed to find the below code which matches column 7 cell by cell on both sheets but doesn't
work on more than one identical value. for examples if col. 7 on sheet1 has number 100 ten times and number 100 eleven times in col. 7
on sheet2 - this VBA code doesn't highlight 11th 100 on sheet2.
Can someone kindly help me?

Sub Compare_Column()

Dim arrSheet1 As Variant
Dim arrSheet2 As Variant
Dim lastrow_Sheet1 As Long
Dim lastrow_Sheet2 As Long
Dim i As Long

lastrow_Sheet1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lastrow_Sheet2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

' Read column 7 values from Sheet1 into an array
arrSheet1 = Sheets("Sheet1").Range("G3:G" & lastrow_Sheet1).Value

' Read column 7 values from Sheet2 into an array
arrSheet2 = Sheets("Sheet2").Range("G3:G" & lastrow_Sheet2).Value

' Loop through the arrays and highlight any differences
For i = LBound(arrSheet1) To UBound(arrSheet1)
If arrSheet1(i, 1) <> arrSheet2(i, 1) Then
Sheets("Sheet1").Cells(i + 2, 7).Interior.Color = RGB(255, 0, 0)
Sheets("Sheet2").Cells(i + 2, 7).Interior.Color = RGB(255, 0, 0)
End If
Next i

End Sub

 

[whassan]

Hi all,
I really need someone's help here please as I need to complete this task sooner due to a deadline approaching.
Please can someone help. Thanks

 

[whassan]

Hello everyone,
I am not sure if my message is being relayed to wider audience and the relevant member of these groups as I still have not got a response.
Can someone from admin team please advise or, correct me if my question is not relevant to the group I am posting to.
Best Regards,

 

[Joe4]

Without seeing exactly what your data looks like, it can be hard to visualize it.
You may have more success if you post some simple examples of your data and expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[whassan]

I appreciate you guiding me in right direction. When I next ask for assistance in the forum, I'll abide by these guidelines.
I was able to find solution to my query I first posted. Please mark this topic as completed.

Best Regards,

 

[Joe4]

I appreciate you guiding me in right direction. When I next ask for assistance in the forum, I'll abide by these guidelines.
I was able to find solution to my query I first posted. Please mark this topic as completed.

Best Regards,

If you post what your solution was, then you can mark it as the solution, as described here: Mark as Solution