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:

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

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

Hello Richard,

Thank you, I guess I needed to give you a little more info. My Bad. I am using 2003 Excel, I noticed yours is 2007. Not sure if it makes a difference but it is not working for me.

thank you
naunid

**Richard Buttrey** · 01-19-2013, 12:05 PM

Sorry, my mistake I didn't spot that. Actually I'd looked but was looking at AB33's profile and saw 2010.

Try the attached.

**naunid** · 01-19-2013, 12:34 PM

Hello Richard

Thank you it worked great. Very happy. but I created this one to put up here. I need to just plug my part numbers in Row A of the 2nd work sheet and it check the first sheet row A against mine and then if not found copy the first sheet part number and its row information onto the last sheet.

When I tried to do that it didnt work. It seemed that it was looking for identical information across the rows on sheet 2. My information is a little different than the vendors.

But I must say I am so excited about this, I spend hours, days and weeks working on the parts and descriptions

Thank you
Naunid

**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

**Richard Buttrey** · 01-19-2013, 01:39 PM

Originally Posted by naunid

Hello Richard
.....It seemed that it was looking for identical information across the rows on sheet 2. My information is a little different than the vendors.

Naunid

In that case, and if you still experience problems then post instances of your ACTUAL data and maybe we can help further. For instance you can use the wild card character to find instances of one string within another (longer) string.

**naunid** · 01-19-2013, 01:49 PM

Sounds good, I keep getting a debug error that goes back to the line "Sheet1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(, 31).Copy"

I will put the parts book back up, there is not much that changes except sheet two only has my part numbers. The new parts are exported to a software.

ok here it is

sheet 1 has the vendors part numbers, info and etc, sheet 2 is my part number which is the same as the vendors, sheet 3 is to capture all part numbers and line/row information on the vendors new parts. I need sheet 1 to check its column A part numbers against Sheet 2s Column A part numbers and if it does not find a matching part number in sheet 2 column A , it copies sheet 1 part number and all columns in that line to sheet 3

I hope that sounds right.

please find attached the parts book with all of my part numbers. Sheet one will have 10,000 rows of parts.

Greatly appreciate your assistance your the greatest
Thank you
Naunid

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

Hi Nauhid.

Just try this:

parts.xlsm

This is the code that does the task..

Please Login or Register  to view this content.

**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

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

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