Part Number and Components

[Cranman2127]

Hello,
I'm currently have two lists of data.
The first list is a list of Parent Item numbers.
The second list is the components that make up the parent number.
I'm trying to figure how to get the components to move from a vertical list to a row.

Example of current data set.:
Parent Item Component Item
11000 CM-11000-BK
11000 CM-11000-40
11000 S0104U
11000 S0104U
11000 S0178X
11000 S0178X
11000 11000-BK
11000 11000-WA
11000 21400
11000 21400
110001 11000
110001 45571
110002 11000
110002 45572
110003 11000
110003 45016

Thanks

[alansidman]

Here is a vba solution for you

Please Login or Register  to view this content.

[Cranman2127]

The vba code isn't grabbing all the components of a kit number.
Example it only grabbed 2 components that make up 11000

[alansidman]

Unable to replicate your issue. Attached is my worksheet showing values that you have provided and the results of the Macro on Sheet2

[EchoPassenger]

Here's a non VBA approach...

I have sequenced your list, created a unique reference for each combination, then used a vlookup function across the rows (up to the maximum number of components for any one parent).

All you need to do now is replace the formulas with the values, remove the original component column, then use the Remove duplicates function on the Parent column.