Data in columns to rows (not transpose...)

[titus.hanke]

Hi everyone, I am trying to clean up some data, which is organized horizantally AND in rows, as per the attached screenshot.

The fact that there is data horizontally across seven week days and in rows for the weeks of the year makes it impossible for me to use the simple TRANSPOSE feature, of which I am aware...

Does anybody have any idea as to how I can get this solved?

I have quite a couple of worksheets I need to apply this operation on, so manual work is the worst solution...

Thanks to all of you out there!

Titus

[EdMac]

Can you attach a sample of your worksheet, rather than the missing screenshot, and some notes of what you want to achieve

[titus.hanke]

Hi Ed,

Sorry for the missing attachment. I don't know what went wrong when posting, something screwed up as my browser wasn't responding.

Attached please find the file with an example of what I have and where I want to get.

Thanks a million for your time.

Titus

[EdMac]

A quick question.....

What is the reason? i.e. what do you want to do with the data - it may be possible to extract the information you want in its present format.

[titus.hanke]

Hi Ed,

Thanks for your ongoing help with this. What I need to do is create some diagrams of this list, which is why it would be best to have it in the format I outlined.

Also, there will be additional files that I would all like to display next to each other in one, vertical format, day by day.

Let's say, it's simpler to quickly run a diagram or calculation of any kind this way, if the data is arranged in an array format...

Nonetheless, if you have any suggestions....

Thanks again,

Titus

[EdMac]

I can't think of a way to do this using formulae - it's probably possible using VBA.

However, if you highlight the whole table and copy > paste special > transpose

this will give you a series of columns that you can just cut and paste into one big column and can be done quite quickly.

You can then put your start date in an adjacent column and fill down.

maybe some one else will come up with an automated process

[Rahul Nagar]

Hello Titus

Please check the attached file.

it my opinion

regards
rahul

[titus.hanke]

Dear Rahul,

Thanks for your time and looking into this.
What I don't understand from your file is how you got the horizontal data into a vertical format? I only see you seperated the dates which I don't really need, as I can leave that format the way it is.

What I need is to get the rows of horizontal numbers Monday-Sunday into a vertical day by day format. Please see the file I attached to my previous post.

Thanks for you help though, appreciate it!

Titus

[Ron Coderre]

OK....Here's a possibility...
From John Walkenbach's website illustrating Joel Horowitz's technique:
http://j-walk.com/ss/excel/usertips/tip068.htm

On Sheet1

From the Excel Main Menu
<Data><Pivot Table>
Use: Multiple Consolidation Ranges_____Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data: A3:H15)_____Click [Add]_____Click [Next]
Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button
Select a location for the Pivot Table_____Click [Finish]

That will create a minimal Pivot Table containing only one cell with a value.

<Data><Pivot Table>
Use: Multiple Consolidation Ranges_____Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data)_____Click [Add]_____Click [Next]
Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button
Select a location for the Pivot Table_____Click [Finish]

That will create a minimal Pivot Table containing only one cell with a value.

Double-Click on that one value cell
Excel will add a sheet to the workbook with the details of
that cell in a database table format, like this:

Please Login or Register  to view this content.

• Delete the empty rows (the first Mon thru Sun)
• Change the the first "Jan 7 - Jan 13" cell's value to 01/07/2008
• Select from that cell down through the last "date" cell
• <edit><fill><series>...Type: Date...Click [OK]

Now the list looks like this:

Please Login or Register  to view this content.

Is that something you can work with?

[titus.hanke]

Tha looks great and I am definitely going to look into it, as I want to expand my knowledge on advanced pivot techniques.

For this specific case though, this means too much manual work though. I have around 80 sheets with this kind of data (as per my attachement) and this would take up enourmous amounts of time.

I was looking for some kind of macro solution, that would do the following:

MON TUE WED THU FRI SAT SUN
5 8 10 12 5 3 5 2
6 3 7 9 4 8 1 15

1.) Take the first line and copy -> paste (transpose) to a given area on the sheet.
2.) Take the second line and copy -> paste (trasnpose) it below the spot you posted the first line.

...and so on...

Any ideas?
Thanks a million,
Titus

[titus.hanke]

Is there really nobody that has a solution , even with a macro? It's basically a repetition of the transpose command, with the paste to location being one row below the last row where transposed data has been pasted...

Thanks, Titus.

[ChemistB]

Here's a solution.
1, you'll need a dummy column which has the initial date of each week. In B5, I used the formula

Please Login or Register  to view this content.

The "+366" is because Excel would interpret that date as 2008 and you want 2009. Drag that formula down to B15.

In B19, I used the formula

Please Login or Register  to view this content.

Drag that formula down as far as necessary.

My numbers do not match your initial values because Jan 7th 2009 is on a Wednesday, not a monday. See attached. Any questions?

[titus.hanke]

Thank you!!!


That was EXACTLY what I was looking for!!!

Titus

[ChemistB]

Glad it worked for you.