Matching Parent Item with Component Item

[beronson]

I am identifying component items which will be Made-to-Stock (MTS) for parent items which are Assemble-to-Order (ATO) for manufacturing purposes.

What I would like to do is:

• In column A, list all of my items which are ATO
• In column B, compare the first 6 characters of column A to a list of part numbers on a different sheet, but only return a value if the first 6 characters match and the part number ends in WY
• The returned value should be the full part number of the matching item

What formula(s) and column structuring will support this?

Thanks,
Bronson

 

[shg]

Couldn't you do all of this on the same sheet using Autofilter?

 

[beronson]

Maybe?

I have a Plan for Every Part (PFEP) with all items, Mfg Env (MTS, ATO, MTO), etc. listed. My current process for making sure the components are stocked appropriately to enable ATO for the parent items is as follows:

• Filter items by Mft Env = ATO
• Manually consolidate all item numbers which contain the same first 6 characters
• Manually find the component items ending in "WY"
• Manually change the Mfg Env for these "WY" items to MTS

Is there a better way to do this? I'm trying to eliminate as much manual work to help mistake-proof this process.

 

[shg]

I dunno. I'd need to see an example of what you have and what you're trying to do.

 

[beronson]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Mfg Env[/TD]
[TD]WY Item[/TD]
[/TR]
[TR]
[TD]9PK661W-A1-25MM[/TD]
[TD]ATO[/TD]
[TD]9PK661W-A1-WY[/TD]
[/TR]
[TR]
[TD]9PM655W-A1-1A[/TD]
[TD]ATO[/TD]
[TD]9PM655W-A1-WY[/TD]
[/TR]
</tbody>[/TABLE]





The above table represents how I would envision an end product. The WY Item column would be populated if the Mfg Env = ATO. If it does, then I would need to match the first 6 characters of an item from the Item column with the first six characters of an item number than ends in WY (table below). The full item number of the WY item would be returned.

The below table would list all the item numbers I plan.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[/TR]
[TR]
[TD]9PK661W-A1-WY[/TD]
[/TR]
[TR]
[TD]9PM655W-A1-WY[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Can't you just filter the Mfg Env column for ATO?

 

[beronson]

I could, but I don't have anything in the WY Item column. This is what needs to get populated by checking for a match, then returning the WY item.

 

[beronson]

I figured it out! Here's what I did:

• Placed all items ending in WY in a separate sheet in the workbook
• Created a helper column titled "ITEM FIRST 6" to extract the first 6 characters from the Item column using formula =LEFT([@ITEM],6)
• Created a WY COMPONENT column which would return a partial VLOOKUP match by looking up what was in the ITEM FIRST 6 column in the WY Items sheet and returning the closest matching value

It really does help to take a break from things for a while and come back later with a fresh mind. This is how it looks now:


[TABLE="width: 375"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]MFG ENV[/TD]
[TD]WY COMPONENT[/TD]
[/TR]
[TR]
[TD]9CS797W-C1-TJ[/TD]
[TD]ATO[/TD]
[TD]9CS797W-C1-WY[/TD]
[/TR]
[TR]
[TD]9FC002-A1-MH[/TD]
[TD]ATO[/TD]
[TD]9FC002-A1-WY[/TD]
[/TR]
[TR]
[TD]9FL525W-A1-VH[/TD]
[TD]ATO[/TD]
[TD]9FL525W-A1-WY[/TD]
[/TR]
[TR]
[TD]9NL602W-A1-1L[/TD]
[TD]ATO[/TD]
[TD]9NL602W-A1-WY[/TD]
[/TR]
[TR]
[TD]9NX330W-A1-TQB[/TD]
[TD]ATO[/TD]
[TD]9NX330W-A1-WY[/TD]
[/TR]
[TR]
[TD]9NX330W-A1-WN[/TD]
[TD]ATO[/TD]
[TD]9NX330W-A1-WY[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td="bgcolor:#F3F3F3"]

Mfg Env​

[/td][td="bgcolor:#F3F3F3"]

WY Item​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]9PK661W-A1-25MM[/td][td]ATO[/td][td="bgcolor:#E5E5E5"]9PK661W-A1-WY[/td][td]C2: =VLOOKUP(LEFT(A2, 6) & "*WY", $C$7:$C$8, 1, 0)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]9PM655W-A1-1A[/td][td]ATO[/td][td="bgcolor:#E5E5E5"]9PM655W-A1-WY[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td]9PK661W-A1-WY[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td]9PM655W-A1-WY[/td][td][/td][/tr]
[/table]

 

[beronson]

I ended up going with a very similar solution, where I used a helper column to take the first 7 characters of the Item column (A) and used the partial VLOOKUP match to check those characters against all my item numbers ending in WY listed on a separate sheet.

I made the post earlier, but it went up for moderator approval. After some experimenting, I found that 7 characters worked better for what I was trying to accomplish, and I still have some items where the incorrect WY product is returned. However, this made quick work of applying the [close to] proper WY component to over 1500 products.

Thank you for your help.