Excel – How to transpose repeating groups in Excel

microsoft excel

How would I transpose repeating rows and columns in Excel. For example how would I transpose the repeating pattern of rows (Tan color) to repeating columns (Purple). Would I need to do this in VBA and are there some built in functions. Actual worksheet has 8,000 rows which need to be transposed.

enter image description here

Best Answer

This one's for you, @LucasPottersky

If you have the tan data and you want to transpose it to the purple data, here is a formula by which you may do that. This formula only works because the specific example in OP's question has repeating data. If your data does not repeat, this will not work for you.

=INDEX($B$2:$D$16,MATCH(G$1,$G$1:$K$1,0),MATCH($F2,$B$1:$D$1,0))

$B$2:$D$16 is the range containing all the tan data but not the headers.

MATCH(G$1,$G$1:$K$1,0) returns the row we want

MATCH($F2,$B$1:$D$1,0) returns the column we want

You'll have to manually copy / paste the headers and the formula for as many rows as is needed.

Related Question