Hello, I need to copy data from an excel file with data horizontally to another excel file vertically, but unfortunately I have merged cells in this file where I want to paste vertically. I am asking for help because I have a lot of data to transfer in this way. Thank you very much in advance.

You select the data and copy it. You position yourself on the cell and click the right mouse button and you choose PASTE SPECIALLY. Then select TRANSPOSITION in the opened window. [/ Quote]

I do this too and it comes out like this:

Try with an additional selection of one of the Formulas / Values and Number Formats options. Note: the number of cells must be the same (i.e. merged cells are counted as one)

I do as you say, scheming right and left - to no avail

This cannot be done with the usual Copy / Paste, because there is no such option to paste with offset results. This can be done, for example, with formulas, provided that the glued areas are of the same size. Since you did not include your files, in my example you have to adjust the example. The idea behind the formula is this: Code: TextLog in, to see the code where lks stands for the number of merged cells. You copy this formula down until your data is exhausted.

[Excel] Transferring Data Horizontally to Vertically with Merged Cells: Efficient Methods

nahirek 9495 5

Treść została przetłumaczona

Zobacz oryginalną wersję tematu

Reply | New topic

#1 16617875 31 Jul 2017 13:26

nahirek nahirek

Level 8

» |

Post #1
16617875 31 Jul 2017 13:26

Hello,
I need to copy data from an excel file with data horizontally to another excel file vertically, but unfortunately I have merged cells in this file where I want to paste vertically.
I am asking for help because I have a lot of data to transfer in this way.
Thank you very much in advance.
ADVERTISEMENT
#2 16618065 31 Jul 2017 14:57

rav88pl rav88pl

Level 10

» | Helpful post? (0)

Post #2
16618065 31 Jul 2017 14:57

You select the data and copy it.
You position yourself on the cell and click the right mouse button and
you choose PASTE SPECIALLY.
Then select TRANSPOSITION in the opened window. [/ Quote]
ADVERTISEMENT
#3 16618295 31 Jul 2017 17:30

nahirek nahirek

Level 8

» | Topic author Helpful post? (0)

Post #3
16618295 31 Jul 2017 17:30

I do this too and it comes out like this:
ADVERTISEMENT
#4 16618400 31 Jul 2017 18:50

adamas_nt adamas_nt

Moderator of Programming

» | Helpful post? (0)

Post #4
16618400 31 Jul 2017 18:50

Try with an additional selection of one of the Formulas / Values and Number Formats options.
Note: the number of cells must be the same (i.e. merged cells are counted as one)
ADVERTISEMENT
#5 16618581 31 Jul 2017 20:34

nahirek nahirek

Level 8

» | Topic author Helpful post? (0)

Post #5
16618581 31 Jul 2017 20:34

I do as you say, scheming right and left - to no avail
#6 16621049 02 Aug 2017 10:05

Maciej Gonet Maciej Gonet

VBA, Excel specialist
» | Helpful post? (0)

Post #6
16621049 02 Aug 2017 10:05

This cannot be done with the usual Copy / Paste, because there is no such option to paste with offset results.
This can be done, for example, with formulas, provided that the glued areas are of the same size.
Since you did not include your files, in my example you have to adjust the example. The idea behind the formula is this:

Code: Text
Log in, to see the code
where lks stands for the number of merged cells.
You copy this formula down until your data is exhausted.

Attachments:

Kopiowanie_do_scalonych.xlsx Download (9.02 KB)
Create an account, log in and become active in a forum and ads will not appear. You will receive points by participating in discussions.
Join this discussion.

Install the application

Didn't find an answer? Ask Artificial Intelligence

*I agree to send the question to OpenAI, Anthropic PBC, Perplexity AI, Inc., Kagi Inc., Google LLC - owners of language models in order to prepare the best response. The companies may monitor and log information entered into the form.

*I agree to publicly display my question and answer. The question and answer will be publicly available to everyone. The process may take a few minutes. Upon completion, you will be redirected to the page with the answer.

Wait...(2min)

Reply | New topic

Report a violation of the law

[Excel] Transferring Data Horizontally to Vertically with Merged Cells: Efficient Methods

Didn't find an answer? Ask Artificial Intelligence