Need Excel formula to convert 1 line to many

djrubin11

Estimable
Oct 13, 2015
1
0
4,510
0
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

Distinguished
Jan 27, 2012
244
0
19,160
69
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

Illustrious
Moderator
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).
 
Thread starter Similar threads Forum Replies Date
S Apps General Discussion 5
H Apps General Discussion 13
U Apps General Discussion 1
P Apps General Discussion 1
P Apps General Discussion 4
W Apps General Discussion 2
C Apps General Discussion 7
S Apps General Discussion 3
B Apps General Discussion 4
M Apps General Discussion 11
indyitguy Apps General Discussion 8
wanamingo Apps General Discussion 1
E Apps General Discussion 1
GOM3RPLY3R Apps General Discussion 6
Christopher Chung Apps General Discussion 4
S Apps General Discussion 4
G Apps General Discussion 1
E Apps General Discussion 3
A Apps General Discussion 1
R Apps General Discussion 2

ASK THE COMMUNITY