Avatar of Wilder1626
Wilder1626
Flag for Canada 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
Microsoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
Wilder1626

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
tel2

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
Sign up - Free for 7 days
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
Wilder1626

ASKER
Hi

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.
tel2

Hi Wilder,

> ...that many formula becomes crazy to handle

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

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

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
tel2

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.
tel2

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
Wilder1626

ASKER
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:

    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

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Wilder1626

ASKER
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

Open in new window

tel2

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?
Wilder1626

ASKER
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
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
tel2

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?
SOLUTION
Rob Henson

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
Sign up - Free for 7 days
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
Wilder1626

ASKER
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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aikimark

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.
Wilder1626

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