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

 

[LSQuinn]

Just did some more testing and it appears to go wrong when I change (Sheet1!A1:E1 to (Sheet1!A1:C1 which I thought would be the correct thing to so as there are now 3 headings - Company, Email, Name, rather than the 5 we had before - this is when I get the #N/A headings?

Thanks,
Lou

 

[Fluff]

That makes sense. You need to change the 150 to 90 as you now have 3 headers * 30 which is 90 rather than 5*30 which is 150

 

[LSQuinn]

Yes, this has worked! many thanks again. Lou

 

[Fluff]

My pleasure.

 

[LSQuinn]

Hi,
When I use the above formula and a formatted date is used in the sheet, the formatting changes from 02/11/2023 to 45232 - is there anyway to protect the formatted date when using the above formula please?
Thanks,
Lou

 

[Fluff]

You will just need to format the output cells.

 

[LSQuinn]

Ok thanks very much

 

[Fluff]

You're welcome.