Comparing 2 worksheets, A1 to Worksheet Column B?

ph34r

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hey all,

I'm trying to compare a cell in Sheet1 to an entire column in Sheet2. If there is a match, I would like to place Sheet1 A1 and Sheet2 RowXColX into a different worksheet Col A and B and highlight them green. If different, highlight red.
IF they match, I would like to proceed to comparing everything else in that row to match. I've been struggling quite abit as I'm new to VBA.

As of right now, I'm able to compare 2 different worksheets column for column and comment on the same worksheet. Rows differ in both worksheets and I have 20k+ rows. Would appreciate anyhelp I can get and attached are an example of the type of data I'm trying to compare. Thank you in advance if anyone is able to help on this.
 

Attachments

  • Sheet1.PNG
    Sheet1.PNG
    6.3 KB · Views: 2
  • Sheet2.PNG
    Sheet2.PNG
    4 KB · Views: 3
  • code.PNG
    code.PNG
    20.4 KB · Views: 3

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your two sheets and the "different worksheet" . Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

ph34r

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I just read my explanation and it sounds a lil confusing so here's a breakdown.

Highlighted Green = Sheet1
Highlighted Red = Sheet2
3rd Image = Sheet 3

Compare Sheet1 Column A with a specific column in Sheet 2. (Right now it's both column A because I can't figure out how to compare otherwise)
If exact match is found, proceed to next column over to compare values in that cell. And then proceed to the next one over.
If all match, don't show in Sheet 3

If no match or one of the other cells don't match, input into a row in Sheet3 and obtain both the rows from Sheet1 and Sheet2 saying RowX vs RowX
 

ph34r

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Option Explicit
Sub comparews()
'
' Test Macro
Dim ws1row, ws2row, maxrow, row, erow, difference As Long
Dim ws1col, ws2col, maxcol, col As Integer
Dim colval1, colval2 As String
Dim Ws1, Ws2, report As Worksheet


Set report = Worksheets("Error")

Set Ws1 = ThisWorkbook.Worksheets("AB")
    With Ws1.UsedRange
        ws1row = .Rows.Count
        ws1col = .Columns.Count
    End With

Set Ws2 = ThisWorkbook.Worksheets("Sheet2")
    With Ws2.UsedRange
        ws2row = .Rows.Count
        ws2col = .Columns.Count
    End With

maxrow = ws1row
maxcol = ws2col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col

report.Activate
Cells.Clear


Range("A1") = "Row"
Range("A1").Font.Bold = True
Range("A1").Font.Size = 14

Range("B1") = "Name"
Range("B1").Font.Bold = True
Range("B1").Font.Size = 14


Range("C1") = "I/O Type"
Range("C1").Font.Bold = True
Range("C1").Font.Size = 14

Range("D1") = "Signal Range"
Range("D1").Font.Bold = True
Range("D1").Font.Size = 14

Range("E1") = "Engineering Units"
Range("E1").Font.Bold = True
Range("E1").Font.Size = 14

Range("F1") = "Trending Interval"
Range("F1").Font.Bold = True
Range("F1").Font.Size = 14


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row

difference = 0
For col = 1 To maxcol
        For row = 1 To maxrow
        colval1 = ""
        colval2 = ""
        colval1 = Ws1.Cells(row, col)
        colval2 = Ws2.Cells(row, col)

       
            If colval1 <> colval2 Then
                difference = difference + 1
                Cells(row, col).Offset(0, 1) = colval1 & " != " & colval2
                Cells(row, col).Interior.Color = 255
                Cells(row, col).Font.ColorIndex = 1
                Cells(row, col).Font.Bold = False
            End If
           
Next row
Next col

Worksheets("Error").Range("A:A,B:B,C:C,D:D,E:E,F:F").EntireColumn.AutoFit
MsgBox difference & " cells contain different data!", vbInformation, "Comparing Spec and Pts List"

   

End Sub
 

ph34r

New Member
Joined
Jul 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
TEST.xlsm
ABC
1
2AHU01.DX.BPDMPR1.ACT.DevAlrt.AlrtDevAlrt.01
3AHU01.DX.BPDMPR1.ACT.PosFbAV
4AHU01.DX.BPDMPR1.ACT.PosFbAiAI2..10 VDC
5AHU01.DX.BPDMPR1.ACT.PosSigAV
6AHU01.DX.BPDMPR1.ACT.PosSigAoAO2..10 VDC
7AHU01.DX.BPDMPR2.ACT.DevAlrt.AlrtDevAlrt.01
8AHU01.DX.BPDMPR2.ACT.PosFbAV
9AHU01.DX.BPDMPR2.ACT.PosFbAiAI2..10 VDC
10AHU01.DX.BPDMPR2.ACT.PosSigAV
11AHU01.DX.BPDMPR2.ACT.PosSigAoAO2..10 VDC
AB


TEST.xlsm
ABC
3AHU01.EA.TEMP1.Fb
4AHU01.EA.TEMP1.FbAi
5AHU01.EA.TEMP1.ChnlAlrt.AlrtBV
6AHU01.EA.TEMP1.FbAV
7AHU01.EA.TEMP1.FbAiAI4..20 mA
8AHU01.EA.TEMP2.ChnlAlrt.AlrtBV
9AHU01.EA.TEMP2.FbAV
10AHU01.EA.TEMP2.FbAiAI4..20 mA
11AHU01.ECH.BPDMPR.ACT.ChnlAlrt.AlrtBV
12AHU01.ECH.BPDMPR.ACT.CntrlSel.AutoSigAV
13AHU01.ECH.BPDMPR.ACT.CntrlSel.ManAutoBV
14AHU01.ECH.BPDMPR.ACT.CntrlSel.ManSigAV
15AHU01.ECH.BPDMPR.ACT.DevAlrt.AlrtBV
16AHU01.ECH.BPDMPR.ACT.DevAlrt.DlyAV
17AHU01.ECH.BPDMPR.ACT.DevAlrt.RstDbAV
18AHU01.ECH.BPDMPR.ACT.DevAlrt.SpAV
19AHU01.ECH.BPDMPR.ACT.PosFbAV
20AHU01.DX.BPDMPR2.ACT.PosFbAiAI2..10 VDC
21AHU01.DX.BPDMPR2.ACT.PosSigAV
22AHU01.DX.BPDMPR2.ACT.PosSigAoAO2..10 VDC
Sheet2


TEST.xlsm
ABCDEF
1RowNameI/O TypeSignal RangeEngineering UnitsTrending Interval
2AHU01.DX.BPDMPR1.ACT.DevAlrt.Alrt != AHU01.EA.TEMP1.ChnlAlrt.AlrtDevAlrt.01 !=
3AHU01.DX.BPDMPR1.ACT.PosFb != AHU01.EA.TEMP1.FbAV !=
4AHU01.DX.BPDMPR1.ACT.PosFbAi != AHU01.EA.TEMP1.FbAiAI != 2..10 VDC !=
5AHU01.DX.BPDMPR1.ACT.PosSig != AHU01.EA.TEMP1.ChnlAlrt.AlrtAV != BV
6AHU01.DX.BPDMPR1.ACT.PosSigAo != AHU01.EA.TEMP1.FbAO != AV2..10 VDC !=
7AHU01.DX.BPDMPR2.ACT.DevAlrt.Alrt != AHU01.EA.TEMP1.FbAiDevAlrt.01 != AI != 4..20 mA
8AHU01.DX.BPDMPR2.ACT.PosFb != AHU01.EA.TEMP2.ChnlAlrt.AlrtAV != BV
9AHU01.DX.BPDMPR2.ACT.PosFbAi != AHU01.EA.TEMP2.FbAI != AV2..10 VDC !=
10AHU01.DX.BPDMPR2.ACT.PosSig != AHU01.EA.TEMP2.FbAiAV != AI != 4..20 mA
11AHU01.DX.BPDMPR2.ACT.PosSigAo != AHU01.ECH.BPDMPR.ACT.ChnlAlrt.AlrtAO != BV2..10 VDC !=
12 != AHU01.ECH.BPDMPR.ACT.CntrlSel.AutoSig != AV
13 != AHU01.ECH.BPDMPR.ACT.CntrlSel.ManAuto != BV
14 != AHU01.ECH.BPDMPR.ACT.CntrlSel.ManSig != AV
15 != AHU01.ECH.BPDMPR.ACT.DevAlrt.Alrt != BV
16 != AHU01.ECH.BPDMPR.ACT.DevAlrt.Dly != AV
17 != AHU01.ECH.BPDMPR.ACT.DevAlrt.RstDb != AV
18 != AHU01.ECH.BPDMPR.ACT.DevAlrt.Sp != AV
19 != AHU01.ECH.BPDMPR.ACT.PosFb != AV
20 != AHU01.DX.BPDMPR2.ACT.PosFbAi != AI != 2..10 VDC
21 != AHU01.DX.BPDMPR2.ACT.PosSig != AV
22 != AHU01.DX.BPDMPR2.ACT.PosSigAo != AO != 2..10 VDC
Error
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Could you please explain in detail how you got "AHU01.DX.BPDMPR1.ACT.DevAlrt.Alrt != AHU01.EA.TEMP1.ChnlAlrt.Alrt" in cell B2 of the "Error" sheet? The first part of B2 ( "AHU01.DX.BPDMPR1.ACT.DevAlrt.Alrt) exists in the "AB" sheet but not in Sheet2 so there is no match and should be included in the "Error" sheet. I don't understand how you got the second part (AHU01.EA.TEMP1.ChnlAlrt.Alrt).
If no match or one of the other cells don't match, input into a row in Sheet3 and obtain both the rows from Sheet1 and Sheet2 saying RowX vs RowX
I think I need a more detailed explanation of the above statement, particularly the part that says "obtain both the rows from Sheet1 and Sheet2 saying RowX vs RowX". PLease be detailed in your description referring to specific cells, rows, columns and sheets.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,139
Messages
5,640,343
Members
417,140
Latest member
bdmprasenjit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top