Excel: moving data from multiple columns to a 2-column format

August 17, 2006 5:00 AM

Excel / VBA help needed: moving data from multiple columns to a 2-column format

I've got an Excel worksheet which contains data in the following format:


Foo 1 3 7
Bar 3 5
Yay 1 5 11 17


And I need it in this format:


Foo 1
Foo 3
Foo 7
Bar 3
Bar 5
Yay 1
Yay 5
Yay 11
Yay 17


...where the contents of columns B:ZZ are moved to B1:B999 and the relevant title from column A is listed alongside each item. It's complicated by the fact that each title has a variable number of entries alongside it: some have as many as 30, some have none.

I know the 'transpose' command will convert the rows to columns but it's still not what I'm after. It may be the first step, though.

Is there an easy way to do this automagically or is it a VB task? If so, any VB geniuses out there?

Thanks

posted by blag to Computers & Internet (9 answers total)

Best answer: I haven't tried this out, as I don't have excel handy, apart from on a mac, and mac/excel makes my head hurt, but I think this should work:

(oh.. well, so much for indenting....)


----

' ***********************************************************************************
' * presuming this all starts at A1, the existing data is in a sheet called "input" *
' * and the results go into an existing sheet called "results" *
' ***********************************************************************************

range("a1").activate

while activecell.value <> ""
n=1
label = activecell.value


while selection.offset(0,n) <> ""
item = selection.offset(0,n)
sheets("results").activate
activecell.value = label
selection.offset(0,1).value = item
selection.offset(1,0).select
sheets("input").activate
n=n+1
wend

selection.offset(1,0).select

wend
posted by pompomtom at 5:49 AM on August 17, 2006

Best answer: Oh, and you'd probably want to put "sub kwijibo()" at the start, and "end sub" at the end....
posted by pompomtom at 5:50 AM on August 17, 2006

Best answer: @pompomtom - just tried it out for you; works OK, but pastes the data starting at E42 :)
Need to stick the following at the start, immediately prior to [range("a1").activate] :

Sheets("Results").Activate
Range("a1").Activate
Sheets("Input").Activate

HTH!
posted by Chunder at 6:07 AM on August 17, 2006

gah....

tidy, together, indented version, without nested while loops, because I don't know how they'll behave here.
posted by pompomtom at 6:08 AM on August 17, 2006

...and what Chunder said. Ta.
posted by pompomtom at 6:09 AM on August 17, 2006

Response by poster: pompomtom - huge thanks. I've tried the macro as listed here but I think I must be doing something wrong.

I've got the macro inserted as a VBA module. My sheets are named "input" and "results". When I run the script, I only get the results from the first row: going from my example above, my output would be:

Foo 1
Foo 3
Foo 7

And nothing from row 2.

Any thoughts? Your help really is appreciated.
posted by blag at 7:35 AM on August 17, 2006

Response by poster: I've got it working using the first script that pompomtom provided; I guess removing the nested while loops in the second script broke it somehow.

pompomtom, you're a bloody genius. Many thanks, mate.
posted by blag at 8:29 AM on August 17, 2006

Response by poster: Hmm. Best answers isn't working at the moment; will try again later.
posted by blag at 8:32 AM on August 17, 2006

Happy to help.

Full working version for posterity (because that link will die eventually):

Sub Kwijibo()

application.screenupdating = false
sheets("results").activate
range("a1").activate
sheets("input").activate
range("a1").activate

while activecell.value <> ""

n=1
label = activecell.value


while selection.offset(0,n) <> ""
item = selection.offset(0,n)
sheets("results").activate
activecell.value = label
selection.offset(0,1).value = item
selection.offset(1,0).select
sheets("input").activate
n=n+1
wend

selection.offset(1,0).select

wend

application.screenupdating = true

end sub
posted by pompomtom at 3:24 PM on August 17, 2006