Excel - rows to columns?

[Redder Lurtz]

I'm using a microstrategy tool that pulls info from a huge database for me to then filter down and mess about with to my heart's content. Only problem with the tool is that when I ask it to retrieve information based on more than one subheading it produces me the information in row form making it impossible to filter, sort, pivot etc. Does anyone know a trick I can use to switch rows to columns?

Many thanks.

[wiiksie]

Edit/Paste Special/Transpose?

[Thants]

Originally Posted by wiiksie

Edit/Paste Special/Transpose?

If one could add "copy the area you want to switch" then yup. I use it every fucking day. That was my one chance to be the TNF oracle and I blew it. Damn you wiiks.

[wiiksie]

Originally Posted by Thants

If one could add "copy the area you want to switch" then yup. I use it every fucking day. That was my one chance to be the TNF oracle and I blew it. Damn you wiiks.

There's still a chance for you to shine Nick, Ive been doing a load of video editing in Premiere CS3 this week, and seeing as you're the vid guru, I'm sure I'll have a question or two for ya.

[Redder Lurtz]

Right.....(might have to post a screen-shot here later if my descriptive powers aren't up to scratch)....how about if the first column is, say, three rows deep; the second column is two rows deep and the remaining columns are a standard one row deep. How would I fit those into columns?

Cheers

[wiiksie]

hmmm...not sure. Screenshot (or even better, pasted values) would help.

[Redder Lurtz]

Ok, hope this helps. The first column here is four rows deep, the second column is two rows deep and the thirs one row. The report generates in Microstrategy which I then export to Excel. The report, once exproted, gives the first two columns as merged cells: (This is dummy data which means cock all btw)


URN 1 Red 2565
2148
Green 4112
225
URN 2 Red 458
4512
Green 1585
1787
URN 3 Red 135
165
Green 1511
1658
URN 4 Red 1332
3367
Green 1124
1145

[Redder Lurtz]

Shite - that's come out wrong. I'll post a screenshot later. (Firewall won't let me access photobucket).

Cheers chaps.

[wiiksie]

Thats just evil. No chance of getting Transpose to recognise that (even without the horrible feature of merging cells in the export).

I think you need to write a function to iterate the cells, strip out individual items (column headers, data, anything merged etc) and then write it out in columns.

Edit: lets see screeny to confirm its evil.

[madstock]

If none of the above works, try turning your screen 90-degrees anti-clockwise.

Glad to be of help.

[Redder Lurtz]

Originally Posted by wiiksie

Thats just evil. No chance of getting Transpose to recognise that (even without the horrible feature of merging cells in the export).

I think you need to write a function to iterate the cells, strip out individual items (column headers, data, anything merged etc) and then write it out in columns.

Edit: lets see screeny to confirm its evil.

That's what I'm thinking Wiiksie. I'm all over Excel but I'm thinking I might have to do some VB stuff to make this work. I'm not up on Visual Basic at all although I recently bought a book about it. This exercise is leading me slowly but surely to the somewhat unfortunate conclusion that I am going to have to read it. Damn.

[RedinSweden]

Excel is like being attacked by a bat.

[Thants]

I could help you but the merged cells fuck it up.

[bobhorse]

right here is where i show i know fuck all about excel everything i do on it is how can i put it Long hand and every thing i do on it takes me an age so here is the question and i hope you will bear with me

I have a spreadsheet and information i input into that spreadsheet i want to then have populate onto another worksheet in the spread sheet here is an excample that will i hope explain what it is i want to do

I have a result in race (A ) historicly the winner will go to race (b) or race (c ) now what i want to do is have that name from race (a ) pop up in a cell (b) and (c)so that is the first problem

The 2nd one comes from when race (D) is run as horse will also go to either race (B) or race (C) and so on it will go so how can i have those alert horses names pop up

well i hope that makes some sense this is what i will be doing for every Major Group race in UK & Europe so it is massive and would cut down my inputting time if it could be done

The information will be really worth the effort it takes and i would be willing to to send the helpful soul a copy

[DJLJ]

You need a small vb script. Send me a pm and lets see what we can do.

I assume this is on Win? Applescript is much better suited on Mac.

[Redder Lurtz]

Mine's solved. A super-techie-nerd at work wrote me a piece of VB. I managed to avoid having to read the book I've bough on VB. Happy days.

[bobhorse]

Does anyone no how you remove hyperlinks all at once?

when i try to cut and paste from the web to a spreadsheet there can be 30 or 40 hyperlinks in it and i am having to remove each 1, one at a time it is doing my tree in