Generating a list based off of the first 3 char of a name

Tasty Mango

New Member
Joined
May 12, 2016
Messages
12
Hello all,
I'm attempting to make a formula that grabs from a list of parts and assemblies and generates a list of parts from the chosen assembly.
The mechanic is that all parts and assemblies have an adjacent column with their identifying serial (XXX-YYY) where XXX is the Assembly designation and YYY is the part designation.
For example a cup & ball toy would have the assembly code of 010-000, the ball would be 010-001, the string would be 010-002, the stick would be 010-003, etc).
I have a formula that lets me know the part count for a chosen assembly:
=IF($B5="","",(SUMIF(Page1!$D$7:$D$157,LEFT(VLOOKUP($B5,Page1!$C$7:$D$157,2,FALSE),3)&"*",Page1!$M$7:$M$140)-1)*IF(Page2'!$D$4="",0,Page2!$D$4))
This grabs the first 3 char from the serial (the assembly code) and counts only parts under that assembly family, and has worked rather well for me.

The parts list function:
{=IFERROR(INDEX(Page1!$C$7:$C$157 ,SMALL(IF(Page1!$D$7:D$157=LEFT(VLOOKUP(Page2!L4,Page1!$C$7:$D$157,2,FALSE)*3)&"*",-ROW(Page1!$C$7:$C$157)-ROW(Page1!$C$7)+1),ROWS(Page1!$C$7:$C7))),"")}
however has not worked at all.

Here's a breakdown of what I'm trying to do:

=IFERROR(INDEX(Page1!$C$7:$C$157 , [Checks the list of names for containing parts and assemblies]
SMALL(IF(Page1!$D$7:D$157=LEFT(VLOOKUP(Page2!L4,Page1!$C$7:$D$157,2,FALSE)*3)&"*",
[Grabs the first 3 characters of the part (the assembly designation) and filters out any parts not part of the selected assembly]
ROW(Page1!$C$7:$C$157)-ROW(Page1!$C$7)+1),ROWS(Page1!$C$7:$C7))),"")
[Generates list of qualifying parts]

Would any of you have a solution for this?

Thanks,
-TM
 
Last edited:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,829
Members
409,839
Latest member
akashsadhu
Top