Group And Transpose Data Based On Sections of Data

[theladysaphir]

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

[Excel Fox]

theladysaphir (http://www.excelfox.com/forum/posthistory.php?p=5276), 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?

[theladysaphir]

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.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=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=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=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=313843#p313843)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792 (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=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=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=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=313744#p313744)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741 (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=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=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=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=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&p=313555#p313555)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533 (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=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=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=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=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=313380#p313380)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378 (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=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=44&t=40455&p=313035#p313035)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

That makes sense. Yes.

[Excel Fox]

Here's the code you need


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(lngRowSetsToT ranspose * (lngRows - 1) + 2, lngStandardInitialCol + 2).Resize(lngRowSetsToTranspose, lngColumnsToTranspose).Value)
Next lngRows
End With

End Sub

[Excel Fox]

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?