Transpose from Horizontal(with blanks) to Vertical (without blanks)

[Legacy 185509]

He guys I need help transposing cell from sheet2 to sheet1.
Sheet2 has data, it has data in cell C2,E2,G2,I2,K2.... all the way to IM2.
so it is skipping one column every time.
here is the picture of it
it is green cells

image removed, sensitive information

I want to transpose it without space
thank you

 

[Legacy 185509]

anyone?

 

[ghard1]

One way of doing it would be to copy it from sheet 2, then paste special values and number formats only to sheet 1

then copy the newly pasted data and paste special - transpose to the same location (say column A

You'll then have your data transposed in column A but with the blanks on every other line.

Then in a column B, place a 1 in the first row, a 2 in the second, then copy and paste these so that there is a 2 beside every blank cell. Finally, sort by column B.

This is a workaround I've used for deleting duplicates. It's not pretty, but it works.

 

[Legacy 185509]

is there any easier way to do this, because where i am pasting is has to match with whatever is there already

 

[Marcelo Branco]

Hi,

Try this

Put this formula in any empty cell of Sheet1
=INDIRECT("Sheet2!"&ADDRESS(2,2*ROWS($1:2)-1))
copy down

HTH

M.

 

[Legacy 185509]

Thanx man you are awesome, can you explain me how it works please
if you can

 

[Marcelo Branco]

Thanx man you are awesome, can you explain me how it works please
if you can

You are welcome and tks for your kind words

I think this formula is better - dont use INDIRECT that is a volatile function - and easier to understand

=INDEX(Sheet2!$C$2:$IV$2,2*ROWS($1:1)-1)

The bit
2*ROWS($1:1)-1
when copied down, generates 1, 3 , 5, 7...
(try it an empty column to see)

and the INDEX function gets the proper column from the range C2:IV2

Hope i made myself clear

M.

 

[Legacy 185509]

Thank you again, I will try to play around with this formula to learn it more
Thank you again for solution and help.

 

[Marcelo Branco]

Thank you again, I will try to play around with this formula to learn it more
Thank you again for solution and help.

My pleasure

M.

 

[Greg Truby]

...dont use INDIRECT that is a volatile function - and easier to understand

Just in case you don't know what that means. Most functions are non-volatile. Excel uses a dependency tree to track which cells are dependents & precedents so that if you change cell A1 for example, only those cells that referene A1 in a formula (its dependents) recalculate. This optimizes Excel's performance (though this goes out the window if there are more than 64K dependencies at which point all cells become volatile).

However certain functions, like INDIRECT are volatile which means that they always recalc no matter what. A couple hundred, no big deal. But a couple thousand and you start to feel the performance hit.

Also whenever you get a formula here that you don't quite understand what it does, you can use the Evaluate Formula tool [in the Formula Auditing group on the Formulas tab in the ribbon] to step through a formula and see what's going on.

¡Hola, Marcelo! ¿Todo bien?