# Selective transpose formula or similar

1. ## Selective transpose formula or similar

Hi

Excel newbie here! I'd appreciate any assistance with the following:

I'm trying to selectively transpose a row of data to a column. Please see my attachment.

I need know how to typically do the following:

Example:
A1 B1
(needs to transpose to)
A3 B3

C1 D1
(needs to transpose to)
A4 B4

E1 F1
(needs to transpose to)
A5 B5

(etc)

I'm using Excel 2019

2. ## Re: Selective transpose formula or similar

In A3

=INDEX(\$A\$1:\$H\$1,,(ROWS(\$1:1)-1)*2+1)

in B3

=INDEX(\$A\$1:\$H\$1,,ROWS(\$1:1)*2)

Copy both down

3. ## Re: Selective transpose formula or similar

Thank you!

That's definitely what I need

It works in the example I provided but I'm having a problem implementing it.

In the attached example the values display backwards.

I'd appreciate any suggestions.

In the Report sheet
First column
=INDEX('Progression Schedule'!\$H\$11:\$AG\$11,,(ROWS('Progression Schedule'!1:\$11)-1)*2+1)

Second Column
=INDEX('Progression Schedule'!\$H\$11:\$AG\$11,,(ROWS('Progression Schedule'!1:\$11)-1)*2+1)

4. ## Re: Selective transpose formula or similar

Another one way formula, takes care of both numbers from the first example.

Formula:
`Please Login or Register  to view this content.`

5. ## Re: Selective transpose formula or similar

Originally Posted by w china
In the Report sheet
First column
=INDEX('Progression Schedule'!\$H\$11:\$AG\$11,,(ROWS('Progression Schedule'!1:\$11)-1)*2+1)

Second Column
=INDEX('Progression Schedule'!\$H\$11:\$AG\$11,,(ROWS('Progression Schedule'!1:\$11)-1)*2+1)
Second Column
=INDEX('Progression Schedule'!\$H\$11:\$AG\$11,,ROWS('Progression Schedule'!1:\$11)*2)

6. ## Re: Selective transpose formula or similar

Apologies- mistake in the formula example. Incorrect sheet name.

I meant...

In the Report sheet
First column
=INDEX('Marks'!\$H\$11:\$AG\$11,,(ROWS('Marks'!1:\$11)-1)*2+1)

Second Column
=INDEX('Marks'!\$H\$11:\$AG\$11,,(ROWS('Marks'!1:\$11)-1)*2+1)

7. ## Re: Selective transpose formula or similar

Hi thanks for that

Unfortunately it only works for the first two values.

=OFFSET(\$A\$1,0,(ROWS(\$1:1)-1)*2,1,2)

How would I implement for the whole row?

8. ## Re: Selective transpose formula or similar

Correction keeps the same for second column

Second Column
=INDEX('Marks'!\$H\$11:\$AG\$11,,ROWS('Marks'!\$1:1)*2)

9. ## Re: Selective transpose formula or similar

First column
=INDEX('Marks'!\$H\$11:\$AG\$11,,(ROWS(\$1:1)-1)*2+1)

Second Column
=INDEX('Marks'!\$H\$11:\$AG\$11,,ROWS(\$1:1)*2

'Progression Schedule' not in your sample workbook !

The highlighted ROWS calculation is a simple count to determine the column in the INDEX formula so has no need to reference a sheet.

10. ## Re: Selective transpose formula or similar

Hi John

Apologies for the error in my example sheet name.

First column works with:
=INDEX(Marks!\$H\$11:\$AG\$11,,(ROWS(\$1:1)-1)*2+1)

Second column works with:
=INDEX(Marks!\$H\$11:\$AG\$11,,ROWS(1:1)*2)

However copy down doesn't work (see attached).

11. ## Re: Selective transpose formula or similar

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

12. ## Re: Selective transpose formula or similar

One formula solution applied on your exemple.

Formula:
`Please Login or Register  to view this content.`

13. ## Re: Selective transpose formula or similar

What doesn't work ??? the results are as per !Marks"

They would if you manage to copy the formula correctly !!!

=INDEX(Marks!\$H\$11:\$AG\$11,,ROWS(\$1:1)*2)

14. ## Re: Selective transpose formula or similar

Hi John, thanks for your help again.

Excel keeps telling me there is a typo in your column 2 formula, however I've tried both: your exact formula and excel's 'corrected' version.

Both produce the same result. The first entry is correct, but copying down does not produce the other results.

15. ## Re: Selective transpose formula or similar

I am guessing that you removed one of these commas:

=INDEX(Marks!\$H\$11:\$AG\$11,,ROWS(\$1:1)*2)

If you did, then no dice!

16. ## Re: Selective transpose formula or similar

Hi Junqueira

It works for the first entry, however if I copy down it does not how the other results correctly.

17. ## Re: Selective transpose formula or similar

Hi AliGW

Thanks for the suggestion.

I've double checked using that formula. The first entry is correct, however the rest of the data in the row does not transfer correctly into the column in the manner:

Example:
A1 B1
(needs to transpose to)
A3 B3

C1 D1
(needs to transpose to)
A4 B4

E1 F1
(needs to transpose to)
A5 B5

(etc)

18. ## Re: Selective transpose formula or similar

How many times do I have to repeat myself????: see attached.

19. ## Re: Selective transpose formula or similar

Hi John

Thank you again for your assistance.

I've copied the code over from your spreadsheet and it's working now.

I'm still not sure what the issue was, as I checked (double, triple) the copying over of your formulas. Perhaps a version issue?

But regardless, thank you for your patience and making my job as a teacher easier!

20. ## Re: Selective transpose formula or similar

It is not a version issue: simple copy/paste of the formulae should have sufficed.

Anyway, glad it is resolved so can you mark this as SOLVED (see "Thread Tools" at top of the thread).

Thank you.

21. ## Re: Selective transpose formula or similar

Copy paste - I did exactly that, plus AB comparison in notepad. Puzzling.
Thank you again for assisting me.

There are currently 1 users browsing this thread. (0 members and 1 guests)