difficult transpose?

[redb]

Hi! In my worksheet:
Column A: Sunday
Column B: the date (2005-10-03)
Column C: hours in sunday (12pm, 1pm, 2pm, etc)
Columns D thru K: numerical values corresponding to that day

Instead, I want columns D thru K to be rows and column C (the hours) to be
rows.
So, right now it looks like:
D E F G H I J K
Sunday 8/14 0:00:00 2 4 6 3 2 5 6 2
Sunday 8/14 1:00:00 1 2 3 5 7 9 2 5
Sunday 8/21 0:00:00 3 5 3 2 7 8 3 7
SUnday 8/21 1:00:00 5 7 9 3 5 2 4 6

Instead, I want it to be:
0:00:00 1:00:00
Sunday 8/14 D 2 1
Sunday 8/14 E 4 2
Sunday 8/14 F 6 3
Sunday 8/14 G 3 5
Sunday 8/14 H 2 7
Sunday 8/14 I 5 9
Sunday 8/14 J
Sunday 8/14 K
Sunday 8/21 D
Sunday 8/21 E
Sunday 8/21 F
Sunday 8/21 G
Sunday 8/21 H
Sunday 8/21 I
Sunday 8/21 J
Sunday 8/21 K


Does anyone have any suggestions? Maybe some macro assistance? Right now,
I'm doing it by hand, but honestly, it's driving me crazy. Any help would be
wonderful right now.
Thanks!

 

[Gary Keramidas]

select the range you want to transpose, click copy
go to another sheet, right click where you want the upper left cell to be,
select paste special
check the transpose box at the bottom and click ok

 

[redb]

Unfortunately, that solution doesn't work well (although it is close to what
I am doing now). The number of days is very long, with close to 2000 some
odd entries in several worksheets and doing a traditional transpose won't cut
it. Also, I need all of the 0:00:00 (1:00:00, etc) entries to be in one
column and that solutions wouldn't do that.

Thanks anyway for the assistance!

 

[Dave Peterson]

It looks like you could use a pivottable and get very close.

I added headers in row 1 (day, date, time, and D, E, ..., K)

Then I selected the range and did:
data|pivottable
follow the wizard until you get a step that has a Layout button on it.
Click that Layout button.

Drag the Day "button" row field
drag the date "button" to the row field
drag the time button to the column field
drag the D button to the data field.
If you see "count of", double click on that button and change it to "sum"
do the same for E:K

Then finish up the wizard.

Now you'll have a little bit of editing to do.

Select A1 and hit ctrl-A (twice in xl2003).
edit|copy
edit|paste special|values

delete those totals at the bottom and the totals to the far right.

Select column C and
edit|Replace
what: "sum of " (without the quotes)
with: (leave blank)

Select your data in A5:Bxxx (as far down as you need)
Then Edit|goto|special|click blanks
type the equal sign =
and hit the up arrow key.
hit ctrl-enter


You've now filled those empty cells in columns A:B with formulas that point to
the cell above.

Select columns A:B
edit|copy
edit|paste special|values

and it looks like you'r done.

 

[Dave Peterson]

ps. Debra Dalgleish has some instructions that describe how to fill those blank
cells at:

http://contextures.com/xlDataEntry02.html

 

[GB]

Well, it's possible to do, ie. with VBA. One thing though well, actually I'm
starting to understand. You will have 24 columns (one for each hour), and
then enough rows to cover the join of every original column and every
calendar date.

If you can identify how many data columns there are in the original then you
can adjust some of the following loops.

Dim CurrentDay as string
Dim CurrentDate as Date
Dim CurrentCol as integer
Dim CurrentRow as Integer
Dim CurrentTime as string
Dim TotalCol as integer
Dim ColOffset as integer
dim NewDay as string
Dim NewDate date
Dim NewCol as Integer
Dim NewColOffset as Integer
Dim NewTime as string
Dim NewRow as integer
Dim I as integer
Dim J as Integer
Dim K as integer

TotalCol=8
ColOffset = 4
J=2
NewRow = 2

Do
newday = Sheet1.cells(J,1).text
newdate = Sheet1.cells(j, 2).value
newTime = Sheet1.Cells(j, 3).text

For I = ColOffset to ColOffset+TotalCol
Sheet2.cells(NEwRow, 1) = Sheet1.Cells(j, 1).text
Sheet2.cells(NewRow, 2) = Sheet1.Cells(j, 2).text
Sheet2.cells(NewRow, 3) = Sheet1.Cells(1, I).text
Sheet2.cells(NewRow, ColOffset+hour(newdate)) = sheet1.cells(J, I).text
Next I
J=J+1
While sheet1.cells(J, 1) <> ""


Ohh bugger. Not that I'm british or anything, but I'll tell you that, yeah
this isn't just a simple matter. You could perhaps do one thing at a time,
but I'm trying to figure out how to ensure that if 8/14 appears way down the
list, then to make it work.

I.e. though if you have the data sorted, then perhaps it can be made to
work. I apologize, I am not in a very convient work environment.

 

[redb]

OH! It works! Thank you so much. You have saved me from weeks of
transposing hell.