Excel Transpose (T) From Column to Row specific position.

[lukelim]

I would like to ask any possible Transpose shortcut on my recent project, which wish someone here guide how to Transpose as attached image.

Appreciate someone help.

thanks

 

[Special-K99]

Questions

1.

Will each element of data ALWAYS have FOUR rows then ONE blank cell separating them.
FOUR rows = A, 123, 456, 789
then one BLANK row.

Is this ALWAYS the case or could it vary?

2.

Are you looking for a formula solution or VBA ?

 

[lukelim]

Hi Special-K99
Thanks for reply

1. I may said YES, ALWAYS same. Supposed in real data I'm wanna to transpose URL's from row to cell as "gif" Attached

2. Please guide the easy and faster way to done it.

Thanks

 

[Special-K99]

Hi Special-K99
2. Please guide the easy and faster way to done it.
Thanks

That does not answer my question.
Do you want a formula solution or VBA (I will not be able to help with a VBA solution as it is not my area of expertise).

 

[lukelim]

May guide me through formula solutions. Thanks

 

[Special-K99]

Here's a formula solution, you will have to make adjustments on this as you have not supplied any cell reference data.

This assumes the following

Your original data is in A$2:A$1000, adjust this range depending where your data starts
Your output begins in C3, change the 3 to whatever row your output begins, also change the 2 to one less than the column you want to begin outputting in so if output starts in column E (column 5) change the 2 to a 4 (one less than 5).

in top left hand corner of your output cell
=INDEX($A$2:$A$1000,MOD((ROW()-3),5)*5+COLUMN()-2,1)

 

[lukelim]

Appreciate your help, it's really worked. thanks

Here's a formula solution, you will have to make adjustments on this as you have not supplied any cell reference data.

This assumes the following

Your original data is in A$2:A$1000, adjust this range depending where your data starts
Your output begins in C3, change the 3 to whatever row your output begins, also change the 2 to one less than the column you want to begin outputting in so if output starts in column E (column 5) change the 2 to a 4 (one less than 5).

in top left hand corner of your output cell
=INDEX($A$2:$A$1000,MOD((ROW()-3),5)*5+COLUMN()-2,1)