Copy cells from Vertical to Horizontal (with empty cells in between)

[epistimon]

Hello friends. I have a serious case from my manager I need to resolve.
We have a vertical report which he needs to have transformed to horizontal:

ORIGINAL REPORT

A B C D
1 1/4/2012
2 DATA1 VALUE 1 VALUE 2 VALUE 3
3 DATA 2 VALUE 4 VALUE 5 VALUE 6
4 DATA 3 VALUE 7 VALUE 8 VALUE 9
5 2/4/2012
6 DATA1 VALUE 10 VALUE 11 VALUE 12
7 DATA 2 VALUE 13 VALUE 14 VALUE 15
8 DATA 3 VALUE 16 VALUE 17 VALUE 18
9 3/4/2012
10 DATA 1 .....
11 DATA 2 .....


NEW REPORT

A B C D
1 DATA 1 DATA 2 DATA 3
2 1/4/2012 VALUE 3 VALUE 6 VALUE 9
3 2/4/2012 VALUE 12 VALUE 15 VALUE 18
4 3/4/2012 VALUE.... ........... .............

I need to copy cells but I need to "jump" over some cells and get repeated results.

CAN SOMEONE HELP? It is urgent!

Thank you in advance!

[dilipandey]

Hi epistimon,

Welcome to the forum.
As an option, try checking copy - paste special - transpose ... thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[oeldere]

Maybe you can start posting a excel file, without confidentional information.

Also add the desired result.

[epistimon]

I am sending you an example. Please see attachement.
I tried the PASTE SPECIAL, but which option shoud I use?

[oeldere]

If you re-arange your data you can use pivot table.

I made an example to show you.

Let me know if you can work with this and with this way?

[epistimon]

First of all I would like to thank you for your assistance.
I partially understood your way of working, although the table on the right confused me (I understood the drop down selection)

Unfortunately I have been asked to do it on the way I showed you into my file. It's not my decision........

[oeldere]

The table on the right side is an pivot table.

You can choose the item to select. => It's a realy powerfull tool.

Maybe you can ask to be able to use pivot table.

If you know how it works, a new world of Excel will open for you.

So my advice to you is: => Make sure you can (and are allowed) to work with pivot table.

[epistimon]

Although I will take one more look on the pivot table solution, I am still looking for the originally requested solution, as I was requested

[dilipandey]

Hi epistimon,

I have used a defined name " data " with below formula:-

Please Login or Register  to view this content.

and then, I have used this name in below formula to obtain results:-

Please Login or Register  to view this content.

Enter this formula using ctrl shift enter key combination

see attached:

regards,
DILIPandey

<click on below 'star' if this helps>