Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Transpose Formula Challenge (Excel XP)

    Hi all,

    Here is a challenging one. In the attached file there are two sheets.
    - The Budget Data sheet : Is a dump from a DB system and acts as the source data.
    - The Report Sheet is a report that is drawn from the Source Data, based on certain required columns for the report.

    The Dump is arranged by row, where the report needs to be structured by column. I need someones assistance to develop a formula for each column in the report, so that I can select the first row in the report, autofill and it collects the data for that record in the Dump. Is this possible??? and if so, how do I set up a formula like that?

    TIA

    PS: The shading in the file is just for view sake...this is not required for the report!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose Formula Challenge (Excel XP)

    If the Budget Data are from a database, they must be stored in a structured way. So you should try to get them from the database in a reasonable format. There's little point in letting the database convert the data from a structured format into a useless one, and then spending a lot of effort in converting it back.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transpose Formula Challenge (Excel XP)

    Actually, i just assume this...The Budget Data sheet in the attachment is what the client says they recieve as a file to work with. Wether it comes from a DB or not, I do not know. All i do know is that this is the format they recieve and its what they have to work with.

    Sorry for the unclear info!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Transpose Formula Challenge (Excel XP)

    Still, it's worth trying to see if the data are available in a more structured way.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transpose Formula Challenge (Excel XP)

    I will ask...!
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Transpose Formula Challenge (Excel XP)

    How about:
    A2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),0)
    B2: =MOD(ROW()-2,12)+1
    C2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),13)
    D2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),14)
    E2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),15)
    F2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),16)
    G2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),17)
    H2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+18)
    I2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+31)
    J2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+44)
    K2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+70)
    L2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+83)
    M2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+57)
    N2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+96)
    O2: =OFFSET('Budget data'!$A$2,INT((ROW()-2)/12),MOD(ROW()-2,12)+109)

    This matches the first up to row 15, I think you started making mistakes with row 16 and do not have the correct reference (they refer to row 2 and not 3)...

    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transpose Formula Challenge (Excel XP)

    Steve, it looks as if you put quite some effort into this. This is really kind of you. I will put the formulas to the test in the real file and see if they work as well as on this sample file. Thanks for the effort and time...I am truely grateful!

    Hans:
    The client has not yet come back to me regarding if they have another format for this file.

    Tx for the input!
    Regards,
    Rudi

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Transpose Formula Challenge (Excel XP)

    <hr>it looks as if you put quite some effort into this.<hr>

    I would say it was a "little effort" not "quite some" <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. If you notice the 2 key calcs:
    INT((ROW()-2)/12)
    MOD(ROW()-2,12)+1 [Col B]

    Which repeats the numbers 12x and then advances (1,1,1,1,1,1...1,2,2,2,2,....,2,etc)
    or loop thru 1-12:
    1,2,3,....11.12,1,2,3,...11,12,1,etc

    The rest are just a variant with OFFSET to take the row (using the INT formula) with a set column [Cols A, C-G]
    or the row with an advancing column using the MOD and a different starting column) [Cols H-O]

    Steve

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transpose Formula Challenge (Excel XP)

    Cheers again Steve....you are truely a formula wizard!!!!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •