Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 17th 05, 06:52 PM
SteveC
 
Posts: n/a
Default Transpose Column With Gaps to Row With No Gaps?

How could I convert, by click and dragging a formula, the following?

It's additionally tricky because the Horizontal format includes an end of
year lable (2004, 2005, etc), but the vertical format does not

Label Cell Reference
4Q04 R17
1Q05 R20
2Q05 R23
3Q05 R26
4Q05 R29
1Q06 R32

Label Cell Reference
4Q04 AG8
2004 AH8
1Q05 AI8
2Q05 AJ8
3Q05 AK8
4Q05 AL8
2005 AM8
1Q06 AN8

Thanks very much in advance.

This will become a forecasting tool for debt, reported interest expense,
accrued interest expense, capitalized interest expense, and cash interest
expense.

Regards,
SteveC
http://www.mavericksocietyinvestmentresearch.com



  #2   Report Post  
Old June 17th 05, 08:46 PM
JE McGimpsey
 
Posts: n/a
Default

This seems to work:

=IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN() - 35 -
COUNTIF($AH$8:AH$8, "20*")) * 3 + 20))



In article ,
SteveC wrote:

How could I convert, by click and dragging a formula, the following?

It's additionally tricky because the Horizontal format includes an end of
year lable (2004, 2005, etc), but the vertical format does not

Label Cell Reference
4Q04 R17
1Q05 R20
2Q05 R23
3Q05 R26
4Q05 R29
1Q06 R32

Label Cell Reference
4Q04 AG8
2004 AH8
1Q05 AI8
2Q05 AJ8
3Q05 AK8
4Q05 AL8
2005 AM8
1Q06 AN8

Thanks very much in advance.

This will become a forecasting tool for debt, reported interest expense,
accrued interest expense, capitalized interest expense, and cash interest
expense.

Regards,
SteveC
http://www.mavericksocietyinvestmentresearch.com

  #3   Report Post  
Old June 17th 05, 11:17 PM
SteveC
 
Posts: n/a
Default

Thanks JE
When I paste in cell AG8, I get a value of zero, but it should show "4Q04."
When I drag to the right one space it it should show (I dont' know), but I
get a circular reasoning error. When I drag it to the right two spaces, it
should show"1Q05" but I get a circular reasoning error. Any suggestions?

Perhaps to clarify, I'm trying to figure out how to transpose:
R17 to AG8
R20 to AI8 (skipping AH8)
R23 to AI8
R26 to AJ8
R29 to AK8,
R32 to AM8 (skipping AL8)
and so on... continuing to skip the last of five cells dragged to the right
so that the horizontal row, dragged out, looks like this:
4Q04 skipped cell 1Q05 2Q05 3Q05 4Q05 skipped cell 1Q06...

Once I've completed dragging the formula over... I suppose I should manually
enter the skipped cells with "2004," "2005" and so on.

Thanks... I really appreciate it your help
Regards,
SteveC

I've obviously copied and pasted it wrong... I've tried deleting the
spacing between operation signs and keeping them as is. Either way I get the
same problem.

Thanks so much.

"JE McGimpsey" wrote:

This seems to work:

=IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN() - 35 -
COUNTIF($AH$8:AH$8, "20*")) * 3 + 20))



In article ,
SteveC wrote:

How could I convert, by click and dragging a formula, the following?

It's additionally tricky because the Horizontal format includes an end of
year lable (2004, 2005, etc), but the vertical format does not

Label Cell Reference
4Q04 R17
1Q05 R20
2Q05 R23
3Q05 R26
4Q05 R29
1Q06 R32

Label Cell Reference
4Q04 AG8
2004 AH8
1Q05 AI8
2Q05 AJ8
3Q05 AK8
4Q05 AL8
2005 AM8
1Q06 AN8

Thanks very much in advance.

This will become a forecasting tool for debt, reported interest expense,
accrued interest expense, capitalized interest expense, and cash interest
expense.

Regards,
SteveC
http://www.mavericksocietyinvestmentresearch.com


  #4   Report Post  
Old June 17th 05, 11:29 PM
SteveC
 
Posts: n/a
Default

Actually, I think I have an easier way to explain

how do I drag across so that it automatically fills in cells, as I drag from
left to right:

Cell1 Cell2 Cell3
=G29, =G32, =G35


  #5   Report Post  
Old June 18th 05, 12:22 AM
JE McGimpsey
 
Posts: n/a
Default

Sorry - I skipped a step:

AH8: =R17
AI8: =IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN()
- 35 - COUNTIF($AH$8:AH$8, "20*")) * 3 + 20))

Drag AI8 across...


In article ,
SteveC wrote:

When I paste in cell AG8, I get a value of zero, but it should show "4Q04."
When I drag to the right one space it it should show (I dont' know), but I
get a circular reasoning error. When I drag it to the right two spaces, it
should show"1Q05" but I get a circular reasoning error. Any suggestions?

Perhaps to clarify, I'm trying to figure out how to transpose:
R17 to AG8
R20 to AI8 (skipping AH8)
R23 to AI8
R26 to AJ8
R29 to AK8,
R32 to AM8 (skipping AL8)
and so on... continuing to skip the last of five cells dragged to the right
so that the horizontal row, dragged out, looks like this:
4Q04 skipped cell 1Q05 2Q05 3Q05 4Q05 skipped cell 1Q06...

Once I've completed dragging the formula over... I suppose I should manually
enter the skipped cells with "2004," "2005" and so on.

Thanks... I really appreciate it your help
Regards,
SteveC

I've obviously copied and pasted it wrong... I've tried deleting the
spacing between operation signs and keeping them as is. Either way I get the
same problem.

Thanks so much.

"JE McGimpsey" wrote:

This seems to work:

=IF(LEFT(AH8,1)="4","20" & RIGHT(AH8,2), INDEX($R:$R, (COLUMN() - 35 -
COUNTIF($AH$8:AH$8, "20*")) * 3 + 20))



  #6   Report Post  
Old June 18th 05, 01:01 AM
SteveC
 
Posts: n/a
Default

Works great! Thanks...



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Position of Filtered TEXT cells in a column Sam via OfficeKB.com Excel Worksheet Functions 0 May 15th 05 08:14 PM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM
Lookup values in a column and display them in order with no gaps Snaggle22 Excel Worksheet Functions 1 April 12th 05 11:36 PM
Auto Skipping and protected cells Dave Peterson Excel Discussion (Misc queries) 6 January 27th 05 11:35 PM
transpose a column into many rows GMed Excel Discussion (Misc queries) 1 January 21st 05 07:15 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017