Microsoft Excel - Splitting multiple columns into rows? 1

[gbaughma]

I have some data that I'm attempting to massage before I bring it into a database.

It looks similar to this:

Item    Related Items ----->

1       2
2       1     4     6    7    9
3       12    15    13

Where the first column is an item ID, and starting at the second column could be several "related" items.

In order to effectively bring this into a database, I need to crunch it down to two columns; the item and a related item, repeating the item as necessary.
For the example above, I need to end up with:

1   2
2   1
2   4
2   6
2   7
2   9
3   12
3   15
3   13

... then I can bring that in as a one-to-many table in my database.

Can anyone think of a simple way to do this?

Additionally.... one other quick question.

My "Part numbers" are messed up (I did *NOT* write all of this stuff to start with)... and in part of the database, a part number may be "S-0001" and in other parts it may be "S-1".

I need to additionally format my first column so that it's always S-#### where #### is 4 digits, zero-filled... so S-1 becomes S-0001.

Any thoughts on THAT one?

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SkipVought]

Hi,

faq68-5287.
[tt]
="S-"&text(A1,"0000")
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gbaughma]

Well, it took a little fiddling... ok, it took a LOT of fiddling... but that worked. Thank you.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com