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

#### Tasty Mango

##### New Member
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:

### Excel Facts

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

Replies
1
Views
105
Replies
3
Views
37
Replies
0
Views
168
Replies
5
Views
267
Replies
0
Views
202