need macro to compare column in one sheet to another sheet and if part # is not found copy

**naunid** · 01-19-2013, 07:30 AM

Need help writing a macro to simplify my work for my business. My excel sheets are part numbers in a column with descriptions, reference numbers and pricing info in its corresponding line.

I need to be able to check column A in worksheet vendor part number against column A worksheet My part number and if vendor part number is not found in worksheet my part number then have it copy the new part number that wasnt found in worksheet my part number and all information in that line to worksheet new parts.

I have been just simply copying the two columns and comparing and manually copying the information and then using find to find the part number in the vendors and manually copying each over. I deal with over 10,000 parts. Very time consuming. I have worked with formulas but have not as of yet written a macro. Need help with this please.

Naunid

**jraj1106** · 01-19-2013, 07:47 AM

Hi Naunid.

Post a sample excel file.

**Richard Buttrey** · 01-19-2013, 07:53 AM

Hi,

Haven't got time just at the moment to offer a macro, and as JRAJ has said we'd need a workbook.
However as a stop gap measure check out the =MATCH() function. This allows you to find whether a value appears in another column. If not it will return a #VALUE result. So if you use an

Formula:

=MATCH(A1,Sheet2!A$1:A$10000,False)

type of formula and copy it down your 10000 rows, you can then filter the column for the #VALUE results and copy and paste the filtered values.

**naunid** · 01-19-2013, 08:25 AM

Please find attached a sample book of the worksheets.

Thank you so much for your quick response, greatly appreciated.

Naunid

**Richard Buttrey** · 01-19-2013, 10:00 AM

Hi,

Try the following macro. The reason AB33 finds the looping macro slow when used with hundreds of rows is because each time through the loop VB code has to jump back to Excel and then jump back to the VB environment. There is a time overhead with each jump which is why loops should be avoided at all costs for this sort of stuff. The fastest way I know to achieve this is to use Excel's standard data filtering functionality. So:

Sub CopyNewParts()
    Dim lLastrow As Long
    lLastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    Sheet1.Range("AF1") = "NewPart"
    Sheet1.Range("AF2") = "=IFERROR(MATCH(A2,'My Part Number'!A:A,FALSE),""New"")"
    Sheet1.Range("AF2").Copy Destination:=Sheet1.Range("AF2:AF" & lLastrow)
    Sheet1.Range("AF1").AutoFilter , Field:=32, Criteria1:="New"
    If Sheet1.Range("A" & Rows.Count).End(xlUp).Row > 1 Then
        Sheet1.Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(, 31).Copy
        Sheet3.Range("A" & Rows.Count).End(xlUp).Cells(2, 1).PasteSpecial (xlPasteAll)
    End If
    Sheet1.Range("A1").AutoFilter
End Sub

**naunid** · 01-19-2013, 11:28 AM

Hello Richard

Thank you for responding. Ok may need a little more help here

LOL. I created a new macro using what you had posted. I must of did something wrong. Please give me detailed instructions on how to put this in. I apparently did a boo boo LOL

Thanks
Naunid

**Richard Buttrey** · 01-19-2013, 11:38 AM

Hi,

See attached.

**AB33** · 01-19-2013, 08:49 AM

I have used this code for a number of times, but almost came to conclusion that it is not suitable for a large data as it is very slow, but have a go.

Sub find_copy()
Dim c2 As Range, c As Range

Application.ScreenUpdating = 0
With Sheets("Vendor Part Number")
    For Each c2 In .Range("A2:A" & .Cells(Rows.Count, 3).End(xlUp).Row)
        Set c = Sheets("My Part Number").Columns(1).Find(c2, , xlValues, xlWhole)
        If c Is Nothing Then
        c2.EntireRow.Copy Sheets("New Parts").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
   Next c2
 End With
 
 Application.ScreenUpdating = 1
End Sub

**AB33** · 01-19-2013, 01:17 PM

Richard,
You are right with the speed!

The find function is looking for each cell. Looping through an array of dictionary would be fast, but dictionary could not cope with duplcation and not as flexible as the find function.
I am aware filtering is much faster than looping, but I need to overcome my phobia of filtering

**jraj1106** · 01-19-2013, 10:41 PM

Hi Nauhid.

Just try this:

parts.xlsm

This is the code that does the task..

Sub CopyNewParts()
    Dim rsht1 As Long, rsht2 As Long, routput As Long, cnt As Long
    
    rsht1 = Sheets("Vendor Part Number").Range("A" & Rows.Count).End(xlUp).Row
    rsht2 = Sheets("My Part Number").Range("A" & Rows.Count).End(xlUp).Row
    routput = Sheets("New Parts").Range("A" & Rows.Count).End(xlUp).Row
    cnt = 0
    
    For i = 2 To rsht1
        For j = 2 To rsht2
            If Sheets("Vendor Part Number").Range("A" & i) <> Sheets("My Part Number").Range("A" & j) Then
                cnt = cnt + 1
            Else
                cnt = 0
                Exit For
            End If
        Next
        If cnt > 0 Then
            routput = routput + 1
            Sheets("Vendor Part Number").Rows(i).Copy (Sheets("New Parts").Range("A" & routput))
            cnt = 0
        End If
    Next
End Sub

**naunid** · 01-20-2013, 05:14 AM

Thank you Thank You Thank You

, What took me 14 hour days and Weeks just took 10 mins max.

All the help is and always will be appreciated. I dropped 10,000 parts and descriptions into the vendors, put all my part numbers into the my parts and it gave me all the new parts that I do not have.

As an owner of 2 online small businesses my time is very consumed and you just gave me time to breathe

LOL

thank you so much
the best to all of you that helped
Nauni

**jraj1106** · 01-20-2013, 09:40 AM

Happy to help Nauni..

Please click the star below

need macro to compare column in one sheet to another sheet and if part # is not found copy

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

need macro to compare column in one sheet to another sheet and if part # is not found copy

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Re: need macro to compare column in one sheet to another sheet and if part # is not found

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions