Transpose data form columns in rows matched with a key

ArviLaanemets · #1 11-26-2020, 01:34 AM

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),"")

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Merging all data between two sheets into a third one without duplicates or rows or columns	Ricko_uk	Excel	4	08-27-2020 01:16 AM
Transpose multiple data form rows in columns matched with a key	Alex1s85	Excel	6	06-24-2020 07:08 AM
Mail Merge with Fields extracted from ROWS instead of COLUMNS or transpose of typical mail merge	djrexy	Mail Merge	1	05-20-2020 04:04 PM
How to Loop through rows and Columns to Offset Data	Josh1012	Excel Programming	4	09-06-2018 03:20 AM
Insert rows when column A data changes then shade and autosum blanks in columns E to I	kgoosen	Excel Programming	0	08-30-2017 04:45 AM