matching part numbers dispersed in two columns

Hello - I work with price lists in Excel. I want to match lists whos
main columns are part numbers. Problem is that there are part number
that do not match in between the two lists. 

For example, the part numbers column in one list (I'll call it "A")ma
have the part numbers 72579, 72580, 72581, 72582, 72583. The par
numbers column in the other list (I'll call it "B") may have 72579
72580, 72582, 72583, 72584.

I want to match the numbers from list "B" to list "A". If Excel coul
just line up the matching numbers on a new column (and leaving blan
cells for unmatched part numbers) that would be great.

Each list has different information, for example list "A" has price
sorted by part number; while list "B" has UPC codes sorted by par
numbers.

My final aim is to add those UPC codes from list "B" into list "A" t
their respective part numbers.

There are thousands of these part numbers and it's very time consumin
to match them one-by-one!

Any help on this topic is appreciated, best regards

--
Message posted from http://www.ExcelForum.com

0
6/8/2004 3:05:17 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
235 Views

Similar Articles

[PageSpeed] 13

hi,

Let's say you have your "A" list in sheet1, and your "B"
list on sheet2 in cells B21:C25 (B has the part numbers,
and C has your UPC codes).  if you paste this

=IF(ISERROR(VLOOKUP(A15,Sheet3!
$B$21:$C$25,1,FALSE)),"",VLOOKUP(A15,Sheet3!
$B$21:$C$25,1,FALSE))

in  sheet1!c15 it should return the matching part number
in sheet2 if it exists - otherwise will be blank.

in sheet1!d15 paste this

=IF(ISERROR(VLOOKUP(A15,Sheet3!
$B$21:$C$25,2,FALSE)),"",VLOOKUP(A15,Sheet3!
$B$21:$C$25,2,FALSE))

and it'll return the corresponding UPC code (if exists);

Modify the cell refs to match your data.

jeff
>-----Original Message-----
>Hello - I work with price lists in Excel. I want to 
match lists whose
>main columns are part numbers. Problem is that there are 
part numbers
>that do not match in between the two lists. 
>
>For example, the part numbers column in one list (I'll 
call it "A")may
>have the part numbers 72579, 72580, 72581, 72582, 72583. 
The part
>numbers column in the other list (I'll call it "B") may 
have 72579,
>72580, 72582, 72583, 72584.
>
>I want to match the numbers from list "B" to list "A". 
If Excel could
>just line up the matching numbers on a new column (and 
leaving blank
>cells for unmatched part numbers) that would be great.
>
>Each list has different information, for example 
list "A" has prices
>sorted by part number; while list "B" has UPC codes 
sorted by part
>numbers.
>
>My final aim is to add those UPC codes from list "B" 
into list "A" to
>their respective part numbers.
>
>There are thousands of these part numbers and it's very 
time consuming
>to match them one-by-one!
>
>Any help on this topic is appreciated, best regards.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
6/8/2004 8:50:53 PM
Hi Jeff - thank you for your response, but the formula makes referenc
to "A15 Sheet3", and I don't have a Sheet 3 with data (I onl
positioned some data as indicated, on Sheets 1 and 2). Should "Sheet 3
in the formula be changed to "Sheet 2"? Advice is appreciated, than
you

Attached is my test spreadsheet

Carlo

             Attachment filename: formula test.xls             
Download attachment: http://www.excelforum.com/attachment.php?postid=57212
--
Message posted from http://www.ExcelForum.com

0
6/9/2004 10:50:32 PM
Hi Jeff - I tried the formulas again. IT WORKED. The Sheet3! had to b
changed to Sheet2!  It organizes them flawlessly. This will help m
enormously. Thank you very much

--
Message posted from http://www.ExcelForum.com

0
7/20/2004 8:56:18 PM
Reply:

Similar Artilces:

Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet? I'm attempting to alter the column sizes half-way through the spreadsheet w/out affecting the upper column sizes... Coolumn width belongs to the entire column and cannot be altered in separate sections of that column. Gord Dibben Excel MVP On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire <spencer4hire@discussions.microsoft.com> wrote: >Can you add seperate column headings (A, B, C, ...) into one spreadsheet? >I'm attempting to alter the column sizes half-way through the spreadsheet >w/ou...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

Numbers and text
I have a link table from an Excel sheet. The first column is for account numbers but some are numbers only and I get the "#num!" error. The only way I can get rid of the error is to retype the number in Excel. I have tried formatting the cells in Excel as "text" but it doesn't work. Is there a work around? Cheers ...

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Auto assign Lot Numbers in PO receiving
Hi! Has anyone made a customization to auto assign Lot numbers upon item receipt? My client's lot number is numeric, they want the system to auto assign this. Any ideas? Thanks! -- Marisol Mortera Marisol, Yes indeed, I created one for a client. You probably will not be able to use it as-is because of some client specific functionality but I'm sure you could use this as a stepping stone. The key features were that it would allow for rapid lot number entry by a. Auto-incrementing the lot number - we had Alphanumeric lot numbers so we added a suffix and b. copy the previous lo...

Keep black parts black??
In 2002 I used this feature for a quick duotone. Colorize the photo, leaving black parts black and it worked great. Now in 2003, this feature is grayed out in spot color mode. What's up?? Thanks! Greg ...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

count if a match occurs
Hi! I have two rows - say Row 1 and Row 2. In the first row I have a answer key. In the second row I have answers from a student. I would like to write a formula where it counts how many answers student got write. Here is a example: Row 0: Q1 Q2 Q3 Q4 Q5 Q6 Row 1: 1 4 2 3 4 1 Row 2: 3 4 1 3 4 1 So student marked 4 questions correctly: Q2, Q4, Q5 and Q6. Hence, the formula should return 4. I know one way to do it but I am sure there is more efficient way to do it. The way I know: Use if statement to compare two corresponding entries and output 1 if...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...