Results 1 to 6 of 6

Thread: Group And Transpose Data Based On Sections of Data

  1. #1
    Junior Member
    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0

    Question Group And Transpose Data Based On Sections of Data

    I'm sorry if this has been answered elsewhere on the forum; I've done several searches of this and neighbour forums through similar questions to try and prevent cross-posting, but with no luck.

    I have a workbook which needs to be reshaped for use with another programme. The output of columns A-E is fine but columns F-AI need reshaping so the rows are columns and vice versa. This is fine - I've found the tranpose function - but because of the layout of the original output, every 12 rows contains a new section that needs tranposing. I figured a macro was the way to go, as I have over 2k rows where each section needs transposing into a new one. However, after lots of study and experimenting I can't get my loop macro to work (well, it will - just on the same range over and over again), and I think it's the offset and the selection of a new range each time that's not working.

    The requirement of what I need to do is:
    - Transpose the values of F1:F13 on sheet (1) to a new sheet (2) in F1:R1
    - Select a fixed n CxR range in fixed columns on (1) and transpose into a fixed n CxR range in columns G:R in (2)
    - Move to the next same size range on (1) in fixed columns and transpose into a fixed n CxR range in columns G:R in (2) under the last set
    - And so on until it hits a blank in column F.

    I also have to figure out how to copy the contents of columns A-E for each section of 12 rows in (1) into columns A-E in (2), but for more rows instead of 12. The original data from G:AI also needs to be transposed to column format and then recur for every new set of rows in (2). I suspect a fill from above/offset loop macro would work for both of these but as it's not the hardest part of what I'm trying to do I haven't got around to it yet...

    Any help would be so very gratefully received; asking the macro to select a new array and destination range each time seems to be the major stumbling block, but maybe it's too complex and needs a UDF. I have attached dummy data which should hopefully illustrate what I mean. I've included some example rows on the 'data' tab and for some of these have reshaped them manually into the 'required result' tab to show what I need to achieve.


    edit - remembered, in case the number of rows or columns is different when we get the new set later in the year, the number of rows & columns/size of range of array needs to be something I can edit, or tell others how to edit if I'm not here, if necessary
    Last edited by theladysaphir; 01-12-2023 at 03:05 PM. Reason: attachment removed

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    theladysaphir, welcome to Excel Fox

    This can be done. However, when you say the number of rows or columns differ, does the mean the rows can differ for each section? So for example, 12 in one section, 15 in another, 10 in yet another....etc?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0
    Hi Excel Fox,

    No, it would always be the same number for each section, i.e. 12 in one, 12 in the next, but if any more questions are added to the next set of source data I would need to change the setup slightly, e.g. 13 in one, 13 in the next, 13 in the next, or change the tranpose array slightly e.g. from 12Cx29R to 13Cx29R or 12Cx30R (but always the same all the way down the document). Does that make sense?

    Thank you for looking at this for me.

    Best,
    ladysaphir

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
    https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
    https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
    https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378
    https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-01-2024 at 02:21 PM.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    That makes sense. Yes.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Here's the code you need

    Code:
    Sub ConsolidateByTranspose()
    
        Const lngColumnsToTranspose As Long = 29
        Const lngRowSetsToTranspose As Long = 12
        Const lngStandardInitialCol As Long = 5
        Const strOutputSheetName As String = "Output Result"
        Const strSourceSheetName As String = "data"
        Dim lngRows As Long
        Dim wks As Worksheet
        Dim wksInput As Worksheet: Set wksInput = Worksheets(strSourceSheetName)
        On Error Resume Next
        Set wks = Worksheets(strOutputSheetName)
        Err.Clear: On Error GoTo 0: On Error GoTo -1
        If Not wks Is Nothing Then
            wks.UsedRange.Clear
        Else
            Set wks = Sheets.Add(After:=Sheets(Sheets.Count))
            wks.Name = strOutputSheetName
        End If
        With wks
            .Cells(1).Resize(, lngStandardInitialCol + 1).Value = wksInput.Cells(1).Resize(, lngStandardInitialCol + 1).Value
            .Cells(1, lngStandardInitialCol + 2).Resize(, lngRowSetsToTranspose).Value = Application.Transpose(wksInput.Cells(2, lngStandardInitialCol + 1).Resize(lngRowSetsToTranspose).Value)
            For lngRows = 1 To (wksInput.Cells(wksInput.Rows.Count, 1).End(xlUp).Row - 1) / lngRowSetsToTranspose
                .Cells(lngColumnsToTranspose * (lngRows - 1) + 2, 1).Resize(lngColumnsToTranspose, lngStandardInitialCol).Value = wksInput.Cells(lngRowSetsToTranspose * (lngRows - 1) + 2, 1).Resize(, lngStandardInitialCol).Value
                .Cells(lngColumnsToTranspose * (lngRows - 1) + 2, lngStandardInitialCol + 1).Resize(lngColumnsToTranspose).Value = Application.Transpose(wksInput.Cells(1, lngStandardInitialCol + 2).Resize(, lngColumnsToTranspose).Value)
                .Cells(lngColumnsToTranspose * (lngRows - 1) + 2, lngStandardInitialCol + 2).Resize(lngColumnsToTranspose, lngRowSetsToTranspose).Value = Application.Transpose(wksInput.Cells(lngRowSetsToTranspose * (lngRows - 1) + 2, lngStandardInitialCol + 2).Resize(lngRowSetsToTranspose, lngColumnsToTranspose).Value)
            Next lngRows
        End With
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    thanks theladysaphir for the kind words. I thought it shouldn't have even had that sheet missing error, cause I coded to check if the sheet exits, and if it doesn't then add a new sheet. I tend to make users do as less manual work as possible. So adding a sheet was one of the manual steps to eliminate. Are you sure the runtime error was because of the sheet / sheet name not being found?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Select Group Of Cells Using Data Validation
    By rich_cirillo in forum Excel Help
    Replies: 7
    Last Post: 06-09-2013, 05:55 PM
  2. Transpose A Column Of Data In To A Table
    By gunjan.nasit in forum Excel Help
    Replies: 4
    Last Post: 05-20-2013, 12:33 AM
  3. Replies: 2
    Last Post: 04-16-2013, 01:36 PM
  4. Group Pivot Data Based On Row Values In One Column
    By mrmmickle1 in forum Excel Help
    Replies: 10
    Last Post: 10-09-2012, 11:46 PM
  5. Transpose data into Rows
    By vikash200418 in forum Excel Help
    Replies: 2
    Last Post: 04-10-2012, 11:02 PM

Tags for this Thread

Posting Permissions

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