Comparing 2 worksheets, A1 to Worksheet Column B?

[ph34r]

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.

 

[mumps]

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]

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]

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]

TEST.xlsm
	A	B	C
1
2	AHU01.DX.BPDMPR1.ACT.DevAlrt.Alrt	DevAlrt.01
3	AHU01.DX.BPDMPR1.ACT.PosFb	AV
4	AHU01.DX.BPDMPR1.ACT.PosFbAi	AI	2..10 VDC
5	AHU01.DX.BPDMPR1.ACT.PosSig	AV
6	AHU01.DX.BPDMPR1.ACT.PosSigAo	AO	2..10 VDC
7	AHU01.DX.BPDMPR2.ACT.DevAlrt.Alrt	DevAlrt.01
8	AHU01.DX.BPDMPR2.ACT.PosFb	AV
9	AHU01.DX.BPDMPR2.ACT.PosFbAi	AI	2..10 VDC
10	AHU01.DX.BPDMPR2.ACT.PosSig	AV
11	AHU01.DX.BPDMPR2.ACT.PosSigAo	AO	2..10 VDC
AB

TEST.xlsm
	A	B	C
3	AHU01.EA.TEMP1.Fb
4	AHU01.EA.TEMP1.FbAi
5	AHU01.EA.TEMP1.ChnlAlrt.Alrt	BV
6	AHU01.EA.TEMP1.Fb	AV
7	AHU01.EA.TEMP1.FbAi	AI	4..20 mA
8	AHU01.EA.TEMP2.ChnlAlrt.Alrt	BV
9	AHU01.EA.TEMP2.Fb	AV
10	AHU01.EA.TEMP2.FbAi	AI	4..20 mA
11	AHU01.ECH.BPDMPR.ACT.ChnlAlrt.Alrt	BV
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
Sheet2

TEST.xlsm
	A	B	C	D	E	F
1	Row	Name	I/O Type	Signal Range	Engineering Units	Trending Interval
2		AHU01.DX.BPDMPR1.ACT.DevAlrt.Alrt != AHU01.EA.TEMP1.ChnlAlrt.Alrt	DevAlrt.01 !=
3		AHU01.DX.BPDMPR1.ACT.PosFb != AHU01.EA.TEMP1.Fb	AV !=
4		AHU01.DX.BPDMPR1.ACT.PosFbAi != AHU01.EA.TEMP1.FbAi	AI !=	2..10 VDC !=
5		AHU01.DX.BPDMPR1.ACT.PosSig != AHU01.EA.TEMP1.ChnlAlrt.Alrt	AV != BV
6		AHU01.DX.BPDMPR1.ACT.PosSigAo != AHU01.EA.TEMP1.Fb	AO != AV	2..10 VDC !=
7		AHU01.DX.BPDMPR2.ACT.DevAlrt.Alrt != AHU01.EA.TEMP1.FbAi	DevAlrt.01 != AI	!= 4..20 mA
8		AHU01.DX.BPDMPR2.ACT.PosFb != AHU01.EA.TEMP2.ChnlAlrt.Alrt	AV != BV
9		AHU01.DX.BPDMPR2.ACT.PosFbAi != AHU01.EA.TEMP2.Fb	AI != AV	2..10 VDC !=
10		AHU01.DX.BPDMPR2.ACT.PosSig != AHU01.EA.TEMP2.FbAi	AV != AI	!= 4..20 mA
11		AHU01.DX.BPDMPR2.ACT.PosSigAo != AHU01.ECH.BPDMPR.ACT.ChnlAlrt.Alrt	AO != BV	2..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]

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.