VBA for Cell to Cell comparision

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
81
Office Version
  1. 365
Platform
  1. Windows
Hi Team,

I need assistance with a Macro that can help me achieve a cell to cell comparison between two different worksheets within the same workbook and the corresponding score in the 3rd worksheet.

I have a workbook with 3 sheets.
1st sheet named - Prod
2nd sheet named - QC
3rd sheet named - Score

The data in sheet 'Prod' and sheet 'QC' will have the same column headers. Even the data size in both the sheet will be same. However the values may differ. I basically need a cell to cell comparison here.
Eg:
If Cell A1 in sheet Prod and Cell A1 in sheet QC matches, i need Cell A1 in Score sheet to give a value as 0. If it doesnt match then the value will be 1. Also please ensure that the values in the sheet 'Score' are values and should not be a formulae in the cell.


1615918497123.png


1615918534800.png


1615918607694.png



Please let me know if more information is needed.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
81
Office Version
  1. 365
Platform
  1. Windows
Keeping you informed here the data set is not limited until specific rows or columns... It may differ based upon the data.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,417
I assume that you want to compare more than one cell. It's 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 sheets that contain more data and are more representative of what your actual sheets look like.. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. 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).
 

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
81
Office Version
  1. 365
Platform
  1. Windows
Alright I'll try to do that.

I basically want to compare Apples to apples..Cell A2 in sheet 1 with cell A2 in sheet 2 and so on.

starting from Row 2 to nth and from Column A to Column nth.
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
98
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Do you mean something like this?

VBA Code:
Sub TS_TabToTabComp()
Dim ProdRNG As Range, QCRNG As Range, ScoreRNG As Range
Dim ProdWs As Worksheet, QCWs As Worksheet, ScoreWs As Worksheet
Dim Cell As Range, i As Integer

Set ProdWs = ThisWorkbook.Sheets("Prod"): Set QCWs = ThisWorkbook.Sheets("QC"): Set ScoreWs = ThisWorkbook.Sheets("Score")

Set ProdRNG = ProdWs.ListObjects(1).DataBodyRange
Set QCRNG = QCWs.ListObjects(1).DataBodyRange
Set ScoreRNG = ScoreWs.ListObjects(1).DataBodyRange
i = 1
For Each Cell In ProdRNG
    If Cell.Value = QCRNG.Cells(i).Value Then
        ScoreRNG.Cells(i).Value = 0
    Else
        ScoreRNG.Cells(i).Value = 1
    End If
    i = i + 1
Next

End Sub
 

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
81
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@Tupe77

Thanx.. That was a quick one..

seems to be working as intended for now.

Lemme thoroughly check to be sure and incase if any customization is required before I mark it as a solution.

Thanx again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,673
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Chefsohail()
   Dim p As String, q As String
   
   p = Sheets("Prod").ListObjects(1).DataBodyRange.Address(, , , 1)
   q = Sheets("QC").ListObjects(1).DataBodyRange.Address(, , , 1)
   With Sheets("Score").ListObjects(1).DataBodyRange
      .Value = .Worksheet.Evaluate("if(" & p & "=" & q & ",1,0)")
   End With
End Sub
 

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
81
Office Version
  1. 365
Platform
  1. Windows
@mumps

@Fluff

Thanks. But I am receiving an error message 'script out of range'.

@Tupe77
Thanks for the macro that you provided, it works but only for a limited number of entries and throws an error 'Overflow'.

May be to simplify it further lets do it this way -

1. Let the 'Prod' , 'QC' and 'Score' sheet be blank.
2. I will later paste the data in both the sheets.
3. The macro should copy the header row 1 from 'Prod' sheet and paste it to 'Score' sheet.
4. Then it will compare cell to cell from row 2 onwards until the end range (the data may not be continuous - as above)

so when we run the macro with no data, it should give 1 as a value in every cell.

Hope that helps.
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
98
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How many cells you have?
My VBA should work with 32000 cells
If there are more cells then change this row:

VBA Code:
Dim Cell As Range, i As Integer

To this:
VBA Code:
Dim Cell As Range, i As Long
 

Watch MrExcel Video

Forum statistics

Threads
1,133,826
Messages
5,661,139
Members
418,618
Latest member
MDOrchid

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