Excel formula to transpose horizontal values in ve

2019-08-27 19:17发布

问题:

I have text in D1:E2. By using macro after clicking button it is copied to the next cells F1:G2. After that user can edit text in F1:G2. Then by pressing the button (with previously described macro) D1:E2 can be copied again so H1:I2 will be prefilled. So by one clicking macro button code is copying D1:E2 to the next available cells. One click F1:G2, second click H1:I2 etc.

Now I need to display all values from D1:E2, F1:G2, H1:I2, J1:K2 in vertical order so:

Text 1  Text 2  Text 3  Text 4  Text 5  etc

Will be:

Text 1  
Text 2  
Text 3  
Text 4  
Text 5
etc

What formula would work the best for that situation? It should also skip empty cells and avoid error messages. So if there is no text in H1:I2, J1:K2 it would just skip them.

Note! Cells D1:E2, F1:G2, H1:I2, J1:K2 are merged and wrapped. If it helps I can edit my model to be D1:E1, F1:G1, H1:I1, J1:K1

回答1:

Ok, thanks goes to Scott Craner and original post can be found here https://superuser.com/questions/1213491/.

Solution is to make row height * 2 (so 13*2=26), unmerge cells vertically and use formula:

=INDEX($1:$1;(ROW(1:1)-1)*2+4)


回答2:

Excel formula vertical to horizontal TRANSPOSE function

Step 1: Select blank cells. First select some blank cells. ...

Step 2: Type =TRANSPOSE( With those blank cells still selected, type: =TRANSPOSE( ...

Step 3: Type the range of the original cells. Now type the range of the cells you want to transpose. ...

Step 4: Finally, press CTRL+SHIFT+ENTER. Now press CTRL+SHIFT+ENTER.

And Refer this link may be it will help you

https://www.thewindowsclub.com/transpose-feature-excel-2013



标签: excel formula