BOM from 1:N => 1:1 relation

[aboot]

Can someone help me to get an easy solution for the problem above. This is a Bill Of Materials (BOM), the left one we use in warehouse the second one in production Normally this BOM has >100 rows

Available

Article Location
A J1;J2;D5
B K4;K5
C K6;K7

Desired

Article Location
A J1
A J2
A D5
B K4
B K5
C K6
C K7

Looking forward to your reply and solution

[Pete_UK]

Are those locations all in one cell, separated by a semicolon?

Pete

[Special-K]

This works, output in Sheet2

Please Login or Register  to view this content.

[Pete_UK]

The attached file shows how you can do this using formulae. There are two helper columns, D and E (coloured blue), and the formulae in these should be copied down until you start to get hyphens. The formulae in G and H should be copied down until you start to get blanks. Note that cell E1 should contain zero (coloured yellow, for emphasis). The formula in column H assumes your location codes can be up to 5 characters in length.

Hope this helps.

Pete

[aboot]

Yes, in one cell and seperated by semicolon

[Pete_UK]

I went ahead assuming that - see Post #4.

Pete