Large data search between sheets. Need help.

johnaboggs · Apr 23rd, 2002, 09:25 PM

I am trying to find a macro or add in of some sort that can solve the following problem. I need to compare data on one sheet (part numbers) to data on another sheet (part numbers and routing masters). I need to find the corresponding routing masters to about 800 part numbers. The problem is that there are about 5000 part numbers w/ routing masters on the second sheet. I need to find a way to duplicate the search and replace on a mass scale. In other words, I want to search for all 800 on the next sheet and then replace them with a bolded text or place an X in the column next to them or something of the like. I am currently plugging one at a time, so any suggestions would be appreciated. Thank you.

JB

Jay Petrulis · Apr 23rd, 2002, 09:37 PM

Quote:

On 2002-04-23 20:25, johnaboggs wrote:
I am trying to find a macro or add in of some sort that can solve the following problem. I need to compare data on one sheet (part numbers) to data on another sheet (part numbers and routing masters). I need to find the corresponding routing masters to about 800 part numbers. The problem is that there are about 5000 part numbers w/ routing masters on the second sheet. I need to find a way to duplicate the search and replace on a mass scale. In other words, I want to search for all 800 on the next sheet and then replace them with a bolded text or place an X in the column next to them or something of the like. I am currently plugging one at a time, so any suggestions would be appreciated. Thank you.

JB

Hi JB,

Use a lookup table.

Assume that your master sheet is on Sheet 2, with part numbers in A1:A5000, and B1:B5000 holding the routing masters.

Sheet 1, A1:A800 are the part numbers that need routing masters attached.

On sheet1, cell B1, type the following

=VLOOKUP(A1,Sheet2!$A$1:$B$5000,2,0)

and copy down the column.

Any ones on the short list missing from the master list will return a #NA error result.

Bye,
Jay

gareth · Apr 24th, 2002, 12:03 AM

I believe that the vlookup function are more suitable in this case my friend

johnaboggs · Apr 24th, 2002, 02:07 PM

Thank you very much for your help. It cut my work in half! I guess I need to brush up on my Excel.

JB

Apr 24th, 2002, 12:03 AM	#3
gareth Board Regular Join Date: Apr 2002 Location: Cape Town,South Africa Posts: 234	I believe that the vlookup function are more suitable in this case my friend __________________ Regards Gareth Visit The Excel Maniacs :<>: Have a Chat! :<>: Free Stuff!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 23rd, 2002, 09:25 PM	#1
johnaboggs New Member Join Date: Apr 2002 Posts: 2	I am trying to find a macro or add in of some sort that can solve the following problem. I need to compare data on one sheet (part numbers) to data on another sheet (part numbers and routing masters). I need to find the corresponding routing masters to about 800 part numbers. The problem is that there are about 5000 part numbers w/ routing masters on the second sheet. I need to find a way to duplicate the search and replace on a mass scale. In other words, I want to search for all 800 on the next sheet and then replace them with a bolded text or place an X in the column next to them or something of the like. I am currently plugging one at a time, so any suggestions would be appreciated. Thank you. JB

Apr 24th, 2002, 02:07 PM	#4
johnaboggs New Member Join Date: Apr 2002 Posts: 2	Thank you very much for your help. It cut my work in half! I guess I need to brush up on my Excel. JB