Sorting data in Excel

[polarbear7]

Hi,

I have a question on how to convert data arranged in a line into columns. E.g. I have a data set that looks like this

Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu
3 5 7 2 5 7 3 9 8 2 5

I would like to have only seven columns (for days of week) and arrange values in their corresponding columns i.e. all Monday values in the Monday column, etc.

Is there a way to do that? Thank you.

 

[dneufarth]

How often does this need to be done?

If only once, why not just manually cut & paste?


Dave

 

[polarbear7]

Unfortunately, the data set has 365 columns in it. That would take a lot of cutting and pasting. I believe there must be a way to automate this operation.

 

[Hasit]

There is a way. Take a look at:

http://support.microsoft.com/support/kb/articles/Q214/0/24.ASP

Note, ultimately, to get exactly whay you want, you may need to write some code. I am not a VBA expert, but perhaps others could help.

 

[Hasit]

Incidentally, the manual option might sound tedious, but to get the result you want quickly whilst someone comes up with code thats better than the code below, you could do this:

1. Select the cells you want to convert
2. EDIT -> COPY
3. EDIT -> PASTE SPECIAL
4. Click on the cell you want to start the new column at
5. TRANSPOSE then OK

Heres the noddy code:

Sub trans()
'
'
Range("A1:E1&quot.Select
Selection.Copy
Range("C8&quot.Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("E16&quot.Select
End Sub