Excel tranpose function or similar

Dubstar07

Hi,

I got a table with 30,000+ rows by 2 columns .

I'd need to transpose this so that it becomes

365 rows x 96 columns

So have:

1 A B
2 A B
3 A B........

Want to have:

1 A to CS
2 A to CS.....
365 A to CS

The transpose function does it row by row but it'll takes hours to do it that way. I tried the offset function too but it doesn't seem to work either.

Anybody know of a method to do this?

Thanks,

Dub

Snowbat

If you have a Linux box handy...

1. Save as CSV
2. Move file to Linux box
3. Open a terminal and run this (change 32000.csv to your filename):

   x=0; for line in $(cat 32000.csv); do if [ "$x" -le 46 ]; then echo -n $line,; x=$(expr $x + 1); else echo $line; x=0; fi; done > output.csv
   

4. Open output.csv in Excel and verify

Thoie

Can probably macro that. I'll have a think about it later, but just to be clear, the contents of row 366, cols A, B will end up in row 1, cols C, D ? And row 730A/B will end up as row 1, E/F ? And so on down the list?

5uspect

Do you have Matlab? If so use the reshape command.
You could do something like this:

A = rand(35040,1);
B = reshape(A,[365,96]);

You could install it's free variant octave and do the same thing if you don't have Matlab.

Dubstar07

Snowbat wrote: »

If you have a Linux box handy...

Eh, no Linux, getting beyond my understanding.
Thanks for the reply Snowbat.

Typical, you go looking for a response, as soon as you give up waiting, along come 3 of them!

Dub

Dubstar07

Thoie,

Thanks for the reply. I've got the data, 2 columns (A & by 30,000+ rows
Column A contains time and date info in 15 min intervals
Column B contains a numeric value for each 15 min interval.

I'd like to be able to re-arrange this so as each row contains the data for one 24hr period (1 row x 96 columns) at each 15 min interval for 365 days

Dubstar07

Thanks 5uspect.

Octave you say!! Will go go_oooogle that!

Thoie

Dubstar07 wrote: »

Thoie,

Thanks for the reply. I've got the data, 2 columns (A & by 30,000+ rows
Column A contains time and date info in 15 min intervals
Column B contains a numeric value for each 15 min interval.

I'd like to be able to re-arrange this so as each row contains the data for one 24hr period (1 row x 96 columns) at each 15 min interval for 365 days

So - you have this:
Date/Time.................The data
01/01/2010 00:00.......15

And you want

Date .......................... 00:00...............00:15...........00:30
01/01/2010...................15

If the Octave thing doesn't work out, the simplest way is:

1. Create a copy of the file
2. In the new copy, insert a column between A and B
3. Under data use "Text to columns", delimited, sep by space on column A to separate the date from the time stamp
4. Highlight the entire block of data (including column headings)
5. Insert - pivot table
6. Put "Date" as row label
7. Put "Time" as column label
8. Put "Sum of Data" (or Average of Data) in the "Values"

Shout if you get stuck, but it will be this evening before I'm back online

Snowbat

If the data is not confidential, attach the file here and one of us will reformat it in the way you specified.

Dubstar07

Thoie,

Your step by step proved a winner!
I had tried the pivot table previously but didn't get what I wanted.
Easy when you know how!!

I put the data into pivot table then cut & pasted it into a new sheet
without the pivot table.

Thanks a mil :D:D

Snowbat, no need to bother you any longer!!

Happy Xmas all;)

Thoie

Dubstar07 wrote: »

Thoie,

Your step by step proved a winner!


Happy Xmas all;)

What's my prize? Is it 36,000 rows of data?

Happy Christmas to you too!