Transposing data accross a fixed number of columns

[LSQuinn]

Hello,

I'm trying to achieve the following -

Each day I import an updated spreadsheet with the following colums

The data will grow each time it's imported but the headings will always stay the same.

I need to transpose the data accross the colums like

The first name will be in column B (column A will be used for ID's which I create)

The headings created should run until 30 so start at Name and end at Name 30

After the 30th name and email has been transposed the 31st will drop below the first Name column again as per image, transpose to the 30th again, and keep repeating until all the data is transposed

I've used the following function before to do something similar and wondered if it could be modified work for this scenario? (the emails visable are my own test emails)

=LET( Arr, TRANSPOSE(A2:C5), rws, ROWS(Arr), seq, SEQUENCE(,rws * COLUMNS(Arr),0), sqR, 1+MOD(seq, rws), sqC, 1+QUOTIENT(seq, rws), CHOOSE({1;2}, INDEX({"Name ","Email ","Phone "}, sqR) & sqC, INDEX(Arr, sqR, sqC)) )

Many thanks,

Lou

 

[Fluff]

Hi & welcome to MrExcel.
How about

=VSTACK(TOROW(IF(SEQUENCE(30),A1:E1)),WRAPROWS(TOCOL(A2:E111),150,""))

 

[LSQuinn]

Thanks very much - where would you input the formula please?

 

[Fluff]

Any where you like.
If you want it on a different sheet then just add the sheet name to the ranges

 

[LSQuinn]

Hi, Thanks again! i'm being stupid sorry.

Ideally, the data i'd import would be in sheet 1 then in sheet 2, i'd use your formula, Presuming the formula will then stay in place when downloaded

As I said the first Name would appear in column B and run for 30 columns (as your formula is doing now thanks) and then start again under column Name, and so on.

The only other part I didn't clarify is i'd like the headings to show as

Name Email Afford Pets Beds Name 1 Email 1 Afford 1 Pets 1 Beds 1 Name 2 Email 2 Afford 2 Pets 2 Beds 2 untl 30

Thanks again for your help (and patience!)

Lou

 

[Fluff]

Ok, how about

=VSTACK(TOROW(Sheet1!A1:E1&" "&SEQUENCE(30)),WRAPROWS(TOCOL(Sheet1!A2:E35),150,""))

 

[LSQuinn]

Hi! thanks very much, this is just what I need. As a welshman might even have to wish you good luck next month!!

thanks again,
Lou

 

[Fluff]

Glad to help & thanks for the feedback.

I think we'll both need a lot of luck.

 

[LSQuinn]

Hi,

I've just tried using this formulav- =VSTACK(TOROW(Sheet1!A1:E1&" "&SEQUENCE(30)),WRAPROWS(TOCOL(Sheet1!A2:E35),150,""))

with differentt headings and for some reason it's stopped starting again after 30 and continues for another 10 turns of #N/A until then starting again under Company 1 as expected. I've changed the &SEQUENCE(30)) to &SEQUENCE(40)) to test and it's still adding the 10 turns of #N/A after. Is there some obvious i'm doing to make this happen? many thanks, Lou

 

[Fluff]

Not sure what is going on there, as col CM should have the value from A1 & 19, so no idea why it showing 30