Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Transposing data from a horizontal schedule to a vertical schedule using formulas

    I have a schedule (RAW DATA) which the departments are horizontal and employees are vertical. I want to pull the information onto another schedule ( FINAL SUMMARY) utilizing a formula which I could just copy from one employee to another.

    I am attaching a workbook with two worksheets. One tab has the Raw data and the second has the Final Summary. I color coded each of the dates to show the flow of the data from the formula. Each Department for each employee has a "START DATE" and an "END DATE".

    Hopefully this is doable.


    Thank you.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    MMN,

    Here is a VBA solution. This code will transpose the raw data to the Final Summary sheet in the format you requested. The Final Summary sheet is updated each time it is accessed. I placed a reset button on the second sheet if you wanted to clear the data manually then re-access the sheet to see the results. The number of rows of news castors on the Raw sheet is limited only to the possible number of grouped columns on the Final Summary sheet. As long as you continue the same format, you can add as many departments as you like on the Raw Data sheet.

    HTH,
    Maud

    MNN1.png

    In a standard module:
    Code:
    Public Sub TransposeData()
    '----------------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastCol As Long, LastRow As Long
    Dim I As Long, J As Long, Row As Long, Col As Long
    Set ws1 = Worksheets("RAW DATA")
    Set ws2 = Worksheets("FINAL SUMMARY")
    LastCol = ws1.Cells(2, Application.Columns.Count).End(xlToLeft).Column
    LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    ws2.Cells(2, 1) = "DEPARTMENT"
    Row = 5
    '----------------------------
    'CLEAR THEN BUILD FINAL SUMMARY SHEET
    Reset
    '----------------------------
    'LIST DEPARTMENTS
    For I = 3 To LastCol Step 4
        ws2.Cells(Row, 1) = ws1.Cells(2, I)
        Row = Row + 1
    Next I
    '----------------------------
    'TRANSPOSE DATA
    Col = 3
    Row = 3
    For I = 6 To LastRow 'ws1
            ws2.Cells(Row, Col) = ws1.Cells(I, 1)
            Row = Row + 1
            ws2.Cells(Row, Col) = "START DATE"
            ws2.Cells(Row, Col + 1) = "END DATE"
            Row = Row + 1
        For J = 3 To LastCol Step 4 'ws1
            ws2.Cells(Row, Col) = ws1.Cells(I, J)
            ws2.Cells(Row, Col + 1) = ws1.Cells(I, J + 1)
            Row = Row + 1
        Next J
        Col = Col + 3
        Row = 3
    Next I
    '----------------------------
    'CLEANUP
    Set ws1 = Nothing
    Set ws2 = Nothing
    End Sub
    
    Public Sub Reset()
    ActiveSheet.Cells.ClearContents
    End Sub
    In the Final Summary Worksheet module:
    Code:
    Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
        TransposeData
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you for the response, however, I am not well versed in VBA and have limited use of Excel Macros. Is it possible to accomplish the goal using formulas only? Something with an "INDEX, MATCH" combination.

    Again thank you for the response.

    MNN

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    MNN,

    The answer is probably yes but complex. The beauty with the code however, is that there is no user intervention needed for maintenance. You just enter the data and the code will do the rest. Kick the tires a bit and in the meantime I will, along with other members, give it some thought on the formulas needed to accomplish it.

    Maud

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    MNN,

    Here is a dirty version with no macros. It uses an Index function that encompasses 2 match formulas (row and column) all inside an IF statement. Due to the lack of congruence of formatting between the sheets, you are limited to the amount of formulas you can copy down.

    On the Final Summary Sheet cell C5, enter the following formula and copy down to D8
    Code:
    =IF('RAW DATA'!C$4="START DATE",INDEX('RAW DATA'!$A$2:$P$1000,MATCH('FINAL SUMMARY'!$C$3,'RAW DATA'!$A$2:$A$1000,0),MATCH($A5,'RAW DATA'!$A$2:$P$2,0)),INDEX('RAW DATA'!$A$2:$P$1000,MATCH('FINAL SUMMARY'!$C$3,'RAW DATA'!$A$2:$A$1000,0),MATCH($A5,'RAW DATA'!$A$2:$P$2,0)+1))
    Each successive block will require a slight modification. You will see the pattern between blocks under each name.

    Too messy for me
    Maud
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'd use:
    C5: =INDEX('RAW DATA'!$C$6:$P$7,MATCH(C$3,'RAW DATA'!$A$6:$A$7,0),MATCH($A5,'RAW DATA'!$C$2:$P$2,0))
    D5: =INDEX('RAW DATA'!$C$6:$P$7,MATCH(C$3,'RAW DATA'!$A$6:$A$7,0),MATCH($A5,'RAW DATA'!$C$2:$P$2,0)+1)
    and fill down as far as necessary.

    You can then simply copy those two columns for each additional block.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you Maud and Rory.

    I will give the formulas a try. I hate to admit it however I have a VBA PHOBIA and really need a course or a great primer for VBA like "VBA FOR REAL DUMMIES"

    But again, thanks for the formulas and I'll get back to you on how they worked out.

    MNN

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    You can also do it using the following formula in C5 of your summary sheet:
    =OFFSET('RAW DATA'!$A$1,MATCH(OFFSET(F$3,,-MOD(COLUMN(),3)),'RAW DATA'!$A:$A,0)-1,MATCH($A5,'RAW DATA'!$2:$2,0)+MOD(COLUMN(),3)-1)
    then copied to all cells for which you want a result

    Alternatively, in C5:
    =OFFSET('RAW DATA'!$A$1,MATCH(C$3,'RAW DATA'!$A:$A,0)-1,MATCH($A5,'RAW DATA'!$2:$2,0-1)
    and in D5:
    =OFFSET('RAW DATA'!$A$1,MATCH(C$3,'RAW DATA'!$A:$A,0)-1,MATCH($A5,'RAW DATA'!$2:$2,0))
    then copied as a pair to all cells for which you want a result:
    Last edited by macropod; 2015-09-13 at 06:57.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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