I'm hoping someone can help me - I've been pondering over a solution for a few days now!
I have a large list of part numbers, with their material parts that make that part. I have extracted the 2 columns of data from a Microsoft Query. Below is a small example - part num 1D001A is made up of 8 material parts. Although not seen in the list below, some of the MtlPartNum are also listed within the PartNum column, as they also require MtlPartNums to make them (basically a sub part) - I know that Material Part 3D284H has a sub part.
PartNum MtlPartNum
1D001A 3D284H
1D001A 3D285A
1D001A 3D288A
1D001A 3F8CT120S4
1D001A 3F8FN000S4
1D001A 3F8WP000S2
1D001A 3H582H
1D001A 3R473A
1D002A 3D284H
1D002A 3D285A
1D002A 3D288A
1D002A 3F019A
1D002A 3F8FN000S4
1D002A 3F8WP000S2
1D002A 3H582H
1D002A 3R473A
1D003A 3D312H
1D003A 3F8CT120S4
1D003A 3F8FN000S4
1D003A 3F8WB000S2
1D003A 3H602H
1D003A 3R473A
1D004A 3D337A
1D004A 3D339H
1D004A 3D340A
1D004A 3F10CT120S4
1D004A 3F10FN000S4
1D004A 3F10WP000S2
1D004A 3H583H
1D004A 3R473A
1D005A 3D337A
I haven't been able to find the data I want from the tables linked in the Microsoft query database, so am having to do a manual work around. What I am trying to achieve is a full bill of materials for a given part. I would like the data to end up looking like this:
Where a MtlPartNum has a sub part, such as 1W020A below, I would still like to have a separate row for the mtl part, as well as separate rows for all the sub parts.
PartNum MtlPart MtlPart2 MtlPart3
DW-LJH5D/A1 1W005A
DW-LJH5D/A1 1W020A
DW-LJH5D/A1 1W020A 2W124A
DW-LJH5D/A1 1W020A 3W409A
DW-LJH5D/A1 2W144A
DW-LJH5D/A1 2W144A 3W087E
DW-LJH5D/A1 2W144A 3W088E
DW-LJH5D/A1 2W144A 3W034A
DW-LJH5D/A1 2W144A 3W125A
DW-LJH5D/A1 2W144A 3W136A
DW-LJH5D/A1 2W144A 3W023A
DW-LJH5D/A1 2W144A 3W024A
DW-LJH5D/A1 2W144A 3W163A
DW-LJH5D/A1 2W144A 3W175A
DW-LJH5D/A1 2W144A 3W138A
DW-LJH5D/A1 2W243A
DW-LJH5D/A1 2W243A 3W247A
DW-LJH5D/A1 2W243A 3W385A
DW-LJH5D/A1 3R481A
DW-LJH5D/A1 3R500A
DW-LJH5D/A1 3R518A
DW-LJH5D/A1 3R545A
DW-LJH5D/A1 3R694A
DW-LJH5D/A1 3R724A
DW-LJH5D/A1 3R847A
DW-LJH5D/A1 3W115A
DW-LJH5D/A1 3W143A
DW-LJH5D/A1 3W229A
DW-LJH5D/A1 3W234A
DW-LJH5D/A1 3W311A
DW-LJH5D/A1 3W312A
DW-LJH5D/A1 3R881A
I would like this layout, as I have a lot of other data to tag into the other columns.
So far I have only been able to create the following layout: The sub part data was created using the following array formula: {=IFERROR(INDEX($B:$B, SMALL(IF([@MtlPartNum]=$A:$A, ROW($A:$A)-ROW($A$1)+1), COLUMN(A2173))),"")}
PartNum MtlPartNum Sub Part1 Sub Part2 Sub Part3 Sub Part4 Sub Part5 Sub Part6 Sub Part7 Sub Part8 Sub Part9 Sub Part10 Sub Part11 Sub Part12
DW-LJH5D/A1 1W005A 0
DW-LJH5D/A1 1W020A Has Sub Part 2W124A 3W409A
DW-LJH5D/A1 2W144A Has Sub Part 3W023A 3W024A 3W034A 3W087E 3W088E 3W125A 3W136A 3W138A 3W163A 3W175A
DW-LJH5D/A1 2W243A Has Sub Part 3W247A 3W385A
DW-LJH5D/A1 3R481A 0
DW-LJH5D/A1 3R500A 0
DW-LJH5D/A1 3R518A 0
DW-LJH5D/A1 3R545A 0
DW-LJH5D/A1 3R694A 0
DW-LJH5D/A1 3R724A 0
DW-LJH5D/A1 3R847A 0
DW-LJH5D/A1 3R881A 0
DW-LJH5D/A1 3W115A 0
DW-LJH5D/A1 3W143A 0
DW-LJH5D/A1 3W229A 0
DW-LJH5D/A1 3W234A 0
DW-LJH5D/A1 3W311A 0
DW-LJH5D/A1 3W312A 0
I have over 15000 rows of data and the above formula takes a lot of processing memory to update, and the layout is not practical for what I want to achieve.
So what I'm asking, is there a way, even using VBA, to create the layout I want?
Any help would be greatly received.
Thanks,
I have a large list of part numbers, with their material parts that make that part. I have extracted the 2 columns of data from a Microsoft Query. Below is a small example - part num 1D001A is made up of 8 material parts. Although not seen in the list below, some of the MtlPartNum are also listed within the PartNum column, as they also require MtlPartNums to make them (basically a sub part) - I know that Material Part 3D284H has a sub part.
PartNum MtlPartNum
1D001A 3D284H
1D001A 3D285A
1D001A 3D288A
1D001A 3F8CT120S4
1D001A 3F8FN000S4
1D001A 3F8WP000S2
1D001A 3H582H
1D001A 3R473A
1D002A 3D284H
1D002A 3D285A
1D002A 3D288A
1D002A 3F019A
1D002A 3F8FN000S4
1D002A 3F8WP000S2
1D002A 3H582H
1D002A 3R473A
1D003A 3D312H
1D003A 3F8CT120S4
1D003A 3F8FN000S4
1D003A 3F8WB000S2
1D003A 3H602H
1D003A 3R473A
1D004A 3D337A
1D004A 3D339H
1D004A 3D340A
1D004A 3F10CT120S4
1D004A 3F10FN000S4
1D004A 3F10WP000S2
1D004A 3H583H
1D004A 3R473A
1D005A 3D337A
I haven't been able to find the data I want from the tables linked in the Microsoft query database, so am having to do a manual work around. What I am trying to achieve is a full bill of materials for a given part. I would like the data to end up looking like this:
Where a MtlPartNum has a sub part, such as 1W020A below, I would still like to have a separate row for the mtl part, as well as separate rows for all the sub parts.
PartNum MtlPart MtlPart2 MtlPart3
DW-LJH5D/A1 1W005A
DW-LJH5D/A1 1W020A
DW-LJH5D/A1 1W020A 2W124A
DW-LJH5D/A1 1W020A 3W409A
DW-LJH5D/A1 2W144A
DW-LJH5D/A1 2W144A 3W087E
DW-LJH5D/A1 2W144A 3W088E
DW-LJH5D/A1 2W144A 3W034A
DW-LJH5D/A1 2W144A 3W125A
DW-LJH5D/A1 2W144A 3W136A
DW-LJH5D/A1 2W144A 3W023A
DW-LJH5D/A1 2W144A 3W024A
DW-LJH5D/A1 2W144A 3W163A
DW-LJH5D/A1 2W144A 3W175A
DW-LJH5D/A1 2W144A 3W138A
DW-LJH5D/A1 2W243A
DW-LJH5D/A1 2W243A 3W247A
DW-LJH5D/A1 2W243A 3W385A
DW-LJH5D/A1 3R481A
DW-LJH5D/A1 3R500A
DW-LJH5D/A1 3R518A
DW-LJH5D/A1 3R545A
DW-LJH5D/A1 3R694A
DW-LJH5D/A1 3R724A
DW-LJH5D/A1 3R847A
DW-LJH5D/A1 3W115A
DW-LJH5D/A1 3W143A
DW-LJH5D/A1 3W229A
DW-LJH5D/A1 3W234A
DW-LJH5D/A1 3W311A
DW-LJH5D/A1 3W312A
DW-LJH5D/A1 3R881A
I would like this layout, as I have a lot of other data to tag into the other columns.
So far I have only been able to create the following layout: The sub part data was created using the following array formula: {=IFERROR(INDEX($B:$B, SMALL(IF([@MtlPartNum]=$A:$A, ROW($A:$A)-ROW($A$1)+1), COLUMN(A2173))),"")}
PartNum MtlPartNum Sub Part1 Sub Part2 Sub Part3 Sub Part4 Sub Part5 Sub Part6 Sub Part7 Sub Part8 Sub Part9 Sub Part10 Sub Part11 Sub Part12
DW-LJH5D/A1 1W005A 0
DW-LJH5D/A1 1W020A Has Sub Part 2W124A 3W409A
DW-LJH5D/A1 2W144A Has Sub Part 3W023A 3W024A 3W034A 3W087E 3W088E 3W125A 3W136A 3W138A 3W163A 3W175A
DW-LJH5D/A1 2W243A Has Sub Part 3W247A 3W385A
DW-LJH5D/A1 3R481A 0
DW-LJH5D/A1 3R500A 0
DW-LJH5D/A1 3R518A 0
DW-LJH5D/A1 3R545A 0
DW-LJH5D/A1 3R694A 0
DW-LJH5D/A1 3R724A 0
DW-LJH5D/A1 3R847A 0
DW-LJH5D/A1 3R881A 0
DW-LJH5D/A1 3W115A 0
DW-LJH5D/A1 3W143A 0
DW-LJH5D/A1 3W229A 0
DW-LJH5D/A1 3W234A 0
DW-LJH5D/A1 3W311A 0
DW-LJH5D/A1 3W312A 0
I have over 15000 rows of data and the above formula takes a lot of processing memory to update, and the layout is not practical for what I want to achieve.
So what I'm asking, is there a way, even using VBA, to create the layout I want?
Any help would be greatly received.
Thanks,