View Single Post
 
Old 11-26-2020, 01:34 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

As it seems, you don't like to use defined Tables, so I used regular worksheet formula now instead Table formula. I also modified formula, so it is same for all columns now.

But be aware, you have to redesign formula whenever you redesign your tables (add new entries into column A, add rows at top of your tables, add new columns into your tables or between them, etc. Unlike Table formulas, regular ones don't adjust automatically.

Edit: To make the formula easier to understand, for cell K2 you may have it as
Code:
=IFERROR(INDEX($A$2:$A$33,(ROW()-1) +INT(((ROW()-1)-1)/4)*4*(4-1) + (COLUMN()-11)*4),"")
which can be explained as
=IFERROR(INDEX(SourceColumnRange,(SheetRowNumber-NumberOfHeaderRows) +INT(((SheetRowNumber-NumberOfHeaderRows)-1)/NumberOfTargetColumns)*NumberOfTargetColumns*(NumberOfTargetColumns-1) + (SheetColumnNumber-NumberOf1stTargetColumn)*NumberOfTargetColumns),"")
Attached Files
File Type: xlsx transpose vertical (2).xlsx (11.0 KB, 8 views)
Reply With Quote