asked on # Macro VBA excel to validate 2 sheets

Hi

I have an excel file with 2 sheets

Sheet1 and Sheet2.

What i would like to do is a macro that would validate the 3 first column of all the rows in Sheet1 compare to the same rows and column on Sheet2.

Ex:

Sheet1. row 1 Column A: Ted

Sheet1. row 1 Column B: 2

Sheet1. row 1 Column C: Golf

In Sheet2. row 1 Column A: Ted

Sheet2. row 1 Column B: 6

Sheet2. row 1 Column C: Golf

The result i want would show in Sheet3 in the same row number in the 3 columns

Sheet2. row 1 Column A:**Pass**

Sheet2. row 1 Column B:**Fail**

Sheet2. row 1 Column C:**Pass**

So since Row 1 column A on Sheet1 and Sheet2 are equal, that the result is:**PASS**

Since Row 1 column B on Sheet1 and Sheet2 are not equal, than the result should be:**FAIL** ...

I may have 30000 rows of data in both Sheets.

How can i do that?

Thanks again for your help

I have an excel file with 2 sheets

Sheet1 and Sheet2.

What i would like to do is a macro that would validate the 3 first column of all the rows in Sheet1 compare to the same rows and column on Sheet2.

Ex:

Sheet1. row 1 Column A: Ted

Sheet1. row 1 Column B: 2

Sheet1. row 1 Column C: Golf

In Sheet2. row 1 Column A: Ted

Sheet2. row 1 Column B: 6

Sheet2. row 1 Column C: Golf

The result i want would show in Sheet3 in the same row number in the 3 columns

Sheet2. row 1 Column A:

Sheet2. row 1 Column B:

Sheet2. row 1 Column C:

So since Row 1 column A on Sheet1 and Sheet2 are equal, that the result is:

Since Row 1 column B on Sheet1 and Sheet2 are not equal, than the result should be:

I may have 30000 rows of data in both Sheets.

How can i do that?

Thanks again for your help

Microsoft ExcelVisual Basic Classic

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Hi Wilder,

*> ...that many formula becomes crazy to handle*

Please explain how it becomes crazy to handle. What's the difficulty?

Please explain how it becomes crazy to handle. What's the difficulty?

It freeze everything just because all formulas search at the same time.

60000 records in each columns

60000 records in each columns

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Please hold while I test this with Excel 2003 on my Pentium 4...

You might find that the same freezing issue happens when you get a macro to do it.

You might find that the same freezing issue happens when you get a macro to do it.

Hi again Wilder,

Having created a test spreadsheet in Excel 2003 with 65536 rows on a Pentium 4 with 1 GB of RAM, it took only a few seconds to evaluate the formulas on Sheet3.

Please unzip the attached sample spreadsheet, open it on your PC, and tell me what happens.

Also, please tell us:

a) What version of Excel you are using?

b) What operating system (and version) you are running?

c) The spec's of your PC or Mac (CPU and RAM)?

Thanks.

EE1.zip

Having created a test spreadsheet in Excel 2003 with 65536 rows on a Pentium 4 with 1 GB of RAM, it took only a few seconds to evaluate the formulas on Sheet3.

Please unzip the attached sample spreadsheet, open it on your PC, and tell me what happens.

Also, please tell us:

a) What version of Excel you are using?

b) What operating system (and version) you are running?

c) The spec's of your PC or Mac (CPU and RAM)?

Thanks.

Ok, i will test it shortly.

But i was still hoping to do it in a macro.

I'm doing some test, but so far, no good result:

But i was still hoping to do it in a macro.

I'm doing some test, but so far, no good result:

```
Dim a As Integer
Dim b As Integer
With Sheets("Sheet1")
For a = 1 To .Range("A65000").End(xlUp).Row
If .Cells(a, 1) <> "" Then
With Sheets("Sheet2")
b = .Range("A65000").End(xlUp).Row
If .Cells(b, 1) = Sheets("Sheet1").Cells(a, 1) Then
.Cells(b, 6) = "Pass"
Else
.Cells(b, 6) = "fail"
End If
End With
End If
Next
End With
```

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

I found how to do it in a macro

```
Dim A As Integer
For A = 1 To Sheets("SHEET1").Range("A65000").End(xlUp).Row
If Sheets("SHEET1").Cells(A, 1) = Sheets("SHEET2").Cells(A, 1) Then
Sheets("SHEET3").Cells(A, 1) = "PASS"
Else
Sheets("SHEET3").Cells(A, 1) = "FAIL"
End If
If Sheets("SHEET1").Cells(A, 2) = Sheets("SHEET2").Cells(A, 2) Then
Sheets("SHEET3").Cells(A, 2) = "PASS"
Else
Sheets("SHEET3").Cells(A, 2) = "FAIL"
End If
If Sheets("SHEET1").Cells(A, 3) = Sheets("SHEET2").Cells(A, 3) Then
Sheets("SHEET3").Cells(A, 3) = "PASS"
Else
Sheets("SHEET3").Cells(A, 3) = "FAIL"
End If
Next
```

Well done, Wilder. That should give you a smaller spreadsheet.

Of course you'll have to re-run the macro if any of those fields on Sheet1 or Sheet2 are ever updated. That's the down-side.

What version of Excel are you using, or what kind of machine (PC/Mac, RAM, CPU), and what happened when you opened my sample spreadsheet?

Of course you'll have to re-run the macro if any of those fields on Sheet1 or Sheet2 are ever updated. That's the down-side.

What version of Excel are you using, or what kind of machine (PC/Mac, RAM, CPU), and what happened when you opened my sample spreadsheet?

I'm using Excel 2010 on windows 7 and an Intel i5 core.

You spreadsheet is pretty quick also, even with all the formulas.

When i run the macro, here is the time results

Start time End time Run time Records FAIL records

22:24:12 22:24:25 0:00:13 65509 109180

You spreadsheet is pretty quick also, even with all the formulas.

When i run the macro, here is the time results

Start time End time Run time Records FAIL records

22:24:12 22:24:25 0:00:13 65509 109180

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

OK.

I wonder why you had the freezing issue when you tried to create the spreadsheet with formulas.

Is this a one-off job where the data on Sheets 1 & 2 will not change in future, or will it change?

I wonder why you had the freezing issue when you tried to create the spreadsheet with formulas.

Is this a one-off job where the data on Sheets 1 & 2 will not change in future, or will it change?

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Hi tel2

This is something i do every weeks but with updated values.

Hi Rob Henson

Actuallu, i always sort the 2 sheets before i start the match lookup.

I will try to see today why it freeze on my master file, and i will let you know

Thanks again for your help

This is something i do every weeks but with updated values.

Hi Rob Henson

Actuallu, i always sort the 2 sheets before i start the match lookup.

I will try to see today why it freeze on my master file, and i will let you know

Thanks again for your help

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Do you have to use "Pass" and "Fail" values? You might apply conditional formatting to the True/False values to change the cell color to Green/Red.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Thanks a lot for all your help. These are good information.

Actually, that's what i was already doing. But when you have 60000 rows of data, that many formula becomes crazy to handle.

That's why i would like to create a macro that would do the job and only put the results instead of the formula.