PDA

View Full Version : Excel Comparison



IcePirate
09-05-2008, 07:27 AM
Hey,

Im working on a project that requires I VB code which Ive developed already, but this is how it works: We have 12 digit inventory numbers...There is one workbook and in sheet1 column A there is 2,000 lines of numbers; in sheet2, column A there is 30,000 lines of numbers. So on sheet three, I want to run a VB Script that takes sheet1 and the 2,000 numbers and compares them to sheet2 to the 30,000 lines and in sheet 3, column A I want to display the numbers that are there, and not display the the numbers that aren't there...

Or - I would like it to display all 2,000 numbers in sheet3, and in column B, put a yes, or no if the number was within the 30,000 lines or not...So I developed a VB code to do this...its below - but its not liking this line:
Application.ScreenUpdating = False

Here is the full script any suggestions?


Sub DupNumbers()
Dim LR1, LR2, LR3 As Integer
Dim x As Range
Dim yesno As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
LR3 = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
Sheets(1).Select
For Each x In Range("A" & LR1)
If IsError(Application.WorksheetFunction.Match(x, Sheets(2).Range("A1:A" & LR2), 0)) Then
yesno = "No"
Else
yesno = "Yes"
End If
Sheets(3).Select
LR3 = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & LR3).Value = x.Value
Range("B" & LR3).Value = yesno
Next x
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Bob Phillips
09-05-2008, 07:46 AM
Sheet1:A1: =IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Yes","No")

and copy down.

IcePirate
09-05-2008, 08:13 AM
Hey,

Can you explain that too me a little more

- Do I copy it on sheet3, where I want the results and fill down? If so its not working very well :(

Sheet1:A1: =IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Yes","No")


- Jeff

Bob Phillips
09-05-2008, 08:17 AM
Or - I would like it to display all 2,000 numbers in sheet3, and in column B, put a yes, or no if the number was within the 30,000 lines or not.

That is what I responded to, except I suggested putting it in Sheet1, why duplicate?

IcePirate
09-05-2008, 08:17 AM
Ook, I think I got it to compare, but - it just displayed "no" for all 30,000 lines, and I know some of the lines on sheets1 and 2 match so they can't all be no?

IcePirate
09-05-2008, 08:23 AM
I have to duplicate because of this reason:
One sheet is called - Technical Service, another sheet is called Master Index. The master index has 30,000 lines of code because it has more then just technical service on it, it has other departments material.

The reason Im doing this, is because some of the technical service lines have been inputted on the master list, some of them have not been inputted yet. So Im trying to determine which ones have been put in the master list, and which ones have not been put into the master list.

And I dont want to sit here and go through comparing it manually, so thats why I thought with a formula/vba script - I could just get the 2,000 numbers from sheet1 to be on sheet3 and a "yes" or "no" displayed in the next column to see which ones are in the master list and which ones arent

Bob Phillips
09-05-2008, 08:32 AM
Post the workbook for us to see.