Transpose data from colum to cells with number of empty cells between

New Contributor

Hello,

 

I’m trying to achieve the following -

 

Take the data from a column and transpose it across cells with predefined headings, missing a consistent number of cells each time.

 

This is how the data starts 

LousQuinn_0-1660626666185.png

 

This is what i'm trying to achieve 

LousQuinn_2-1660627388263.png

 

I've researched transposing and indexing but can't find a solution to what i'm trying to achieve.

Many thanks for any help

Lou

 

2 Replies

@LousQuinn 

If you want all the data, enter the following formula in G2, then fill to the right:

=INDEX($A:$C,QUOTIENT(COLUMN()-7,3)+2,MOD(COLUMN()-7,3)+1)

If you want only the names, with empty cells in between:

=IF(MOD(COLUMN(),3)=1,INDEX($A:$A,QUOTIENT(COLUMN()-7,3)+2),"")