Need Excel formula to convert 1 line to many

[djrubin11]

I have a line in my inventory report that shows multiple columns pertaining to my assets.

Basically it has the columns Model, Model Type, Quantity, Serial #, purchase date

All of this information is on a single line however I want to take a line that has a quantity greater than 1 and change it to 2 lines with individual serial numbers.


To further explain:


Model Model Type Quantity Serial # Purchase Date

HP G1 Computer 25 24234324, 3457365356, 5845674673, 45245134, etc. 10/5/2015



Basically the serial # column is delimited by a comma, but instead of having a single line like above I want this:




Model Model Type Quantity Serial # Purchase Date

HP G1 Computer 1 23245324 10/5/15

HP G1 Computer 1 3457365356 10/5/15

HP G1 Computer 1 5845674673 10/5/15


etc, etc.



What formula or excel function can I use to make this happen?

 

[greens]

This would be a complex task and would be more effective to do manually unfortunately.

If you were dealing with thousands of entries we could probably whip up a script to take care of it, but as it stands it would be too much for excel. It isn't capable of understanding diliniation by comma.

 

[USAFRet]

1. You're using the wrong tool for this. You're using a calculator (Excel) for what should be in a database (Access).
2. Possibly a one time formula could be made, but I'd have to see the actual data (or a subset).