Identify part number in list and tag it with characters from another list

[DanDungan]

Hi,

Using Excel 2000 on windows xp, I have a workbook with two
spreadsheets--each sheet contains a list. I need to summarize a list
of part numbers by type in a pivot table. I know how to prepare the
pivot table

I use vlookup for this to match part numbers, but I haven't been able
to figure out how to add the part type to the part number list.

I appreciate any suggestions.

Thanks

Dan

ListA is a list of part number types.

00-4
01-900483
01-900543
02-157
02-217
02-271
02-31
02-42
02-900716
02-91578
02-9645
04-2000
04-809
45-217
45-422
45-91577
45-964500

ListB is a list of part numbers on a separate sheet.

00-4140AD1801
00-4140AD2001
00-4140AD2400
00-4140AD2401
01-900483AA1600
01-900483AA1000
01-900483AA1400
01-900543AA1200
01-900543AA1400
01-900543AA1600
01-900543AA1800
02-1573201-2012A
02-1573501-1004A
02-1573501-1005A
02-2174116-1204A
02-2174120-0805A
02-2174122-1605A
02-2714114-0706A
02-2714116-1204A
02-2716808-0604A
02-3140-0801A
02-3140-1201A
02-3140-1801A
02-42241-1006A
02-42254-0804A
02-42264-1812A
02-900716AD1004
02-900716AD1404
02-900716AD1810
02-91578-1204A
02-91578-1406A
02-9157818-11C04A
02-9157841-0804B
45-42200-1006B
45-42200-1108B
45-42200-1309B
02-96451801-22C07A
04-20000-0400A
04-809-0900B
45-21700-1205B

I need to add the series one cell to the left to identify each part
number.
Like this


A B
1 01-900543 01-900543AA1800

 

[Dave Peterson]

it may be as simple as using appending a wildcard (the asterisk) to your
=vlookup() value:

=vlookup(a1&"*",sheet2!a:a,1,false)

One of the few times that I've seen a reason for having a single column lookup
table!

 

[DanDungan]

Hi Dave,

I had tried to get that to work, but I had neglected the "&". Thanks!

However, when I applied the wild card, the formula returns a complete
part number in column A.

this formula:
=VLOOKUP(Selection!A5&"*",D52974,1,FALSE)

Returned:

A B
Selection Part
02-2714108-0404A 00-28668AD1600

and I want to see the Part Type in column A


I tried
=LOOKUP(SCRUB!D2,Selection!$A$2:$A$18)

but that returns data such as:
00-4 00-4941AD2401
00-4 00-4941BA2001
00-4 00-900484AD1201
00-4 00-900885BD1000

I only want the flag on part numbers that start with that set of
characters.

I tried:
=LOOKUP(SCRUB!D2,Selection!$A$2:$A$18&"*")

and that returns #Value

I couldn't understand how I might use Index and Match or search,
either.

Do you have any other recommendations?

Thanks,

Dan

____________________________________________________
Further Description:

My boss wants to see how many times we built a part number last year
so he can streamline our process.

He gave me the list of 3000 part numbers in sheet "Scrub" in the
workbook, "Index Unloaded_2010.xls" and a list of the types he wants
to analyze. I put his list of types on worksheet "Selection" and
inserted a named Range "PartTypes". The Part Number reside in
worksheet, "Scrub" with a named range, "PartNumbers"

Sheet Scrub layout

A B
Selection PART
00-34317PD1200
00-4 00-4015BD0600

Sheet Selection layout

A B
Item Length
00-4 4

This procedure I'm asking for help on is so that I can flag the part
numbers he wants to see. I think he's going to want to do this more
often so I'm trying to automate.

Thanks for your feedback.

Dan

 

[Dave Peterson]

Oops. I misunderstood your question. (I had it backwards.)

So you want to start with the long part number and return the shorter part number.

I don't see a good way of doing this.

Maybe someone else will chime in.

 

[DanDungan]

Thanks for your response, Dave.

Oops.  I misunderstood your question.  (I had it backwards.)

So you want to start with the long part number and return the shorter part number.

I don't see a good way of doing this.

Maybe someone else will chime in.