Hi!
I need to help with vba code or formula for transpose data from Rows to one Column.
In sheet1 I have 350 rows (each row have 8 columns).
In sheet2 I want transpose this data to one column (in column will 2800 data = 2800 rows).
Example:example.png
That means I need to change the data dynamically.
I tried to use transpose matrix, index, etc. but it did not work right for me.
Thank you in advance for your help.
--Daniel
=INDEX(Sheet1!$B$2:$B$6,1+INT((ROWS($1:1)-1)/8))
copied down...
and
=INDEX(Sheet1!$C$2:$J$6,1+INT((ROWS($1:1)-1)/8),1+MOD((ROWS($1:1)-1),8))
copied down.
Adjust the ranges to suit, as we could not tell what cells your data were in. Also, being Czech, you will probably need ; instead of , as a separator.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Sub voda4444()
If code is required, you might try something like this.
Works on data as per your example picture.
Please Login or Register to view this content.
Thank you Glenn Kennedy, that works perfect.
I still want to ask for help with this... In your Attached File Book1.xlsx...
From sheet1 I want to use data from columns D, G, I, J.
In sheet2 want output see attached png below.
Example:
example2.png
Book1.xlsx
Formula what I tried is like this
=CONCATENATE("CELL[";INDEX(Sheet1!$B$2:$B$6,1+INT((ROWS($1:1)-1)/8));"].BASE.X := ";INDEX(Sheet1!$C$2:$J$6,1+INT((ROWS($1:1)-1)/8),1+MOD((ROWS($1:1)-1),8));";")
I dont know how to do it.
Help me please.
Thank you in advance.
--Daniel
Last edited by voda4444; 01-12-2018 at 05:17 PM.
Please attach a workbook, not a picture of one.
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
In the mean time here's a VBA solution.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
voda444. I have't a clue what you are talking about. Start with a new file, showing before and after....
Thank you bakerman2, your VBA code works perfect.
My question about this code...
Is it possible to change output data dynamicly (when I change something in Sheet1), change also occurs in a Sheet2 without manual Run macro.
Put this in Sheet Module of Sheet1.
Please Login or Register to view this content.
Thank you
You're welcome and thanks for rep+.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Hi!
I need help with some adjustments of used VBA.
There is a example - Book1.xlsx
Thank you.
Solved...Originally Posted by voda4444
Please Login or Register to view this content.
Last edited by voda4444; 01-16-2018 at 12:17 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks