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
