Transpose data from row to column

N

needassistance

New Member
Joined
Mar 23, 2011
Messages
10
I have more then 10000 data in signle range A:A
I want macro script to get transpose data from a1 to black cell, to any colume e.g c and so on as per data
when it transpose then collect data from next cell mean after last blank cell
and transpose again.
is it possible ?
if yes then please provide me script. Asap
Thanks for your attention.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Could you post a small example, say 15-20 cells in ColumnA showing just what you want done with these.

Your request is too vague as it stands for me (at least) to be able to give any more feedback.
 
Upvote 0
<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt; width: 192pt;" width="256" height="17">infect i need to collect data from beginning till blank cell like after sold there is blank cell or some time last value is not like sold. but i need that
i collect data from start till blank and ttranspose it.
then re collect data from all worked black cell till next blank cell


Sure.
I have data like this in a:a

HUGE 100% LEGO DUPLO LOT of 200 ANIMALS BOB BUILDER </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Location: USA </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Item: 280644259567 </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Seller User ID: sislandgirl1983 </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Feedback: 802 | 100% </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">View similar active items | Sell one like this </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Buy It Now </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">Sold $20.50 End Date:Mar-14 16:12 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17"> DUPLOS,LEGOS,US?ED,SET OF 6 MISC BEDROOM PIECES </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Location: USA </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Item: 310303791069 </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Store: Ashlees Fine Things </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Seller User ID: nick9372 </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Feedback: 1,404 | 100% </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">View similar active items | Sell one like this </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Enlarge </td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Buy It Now </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">Sold $9.99 End Date:Mar-14 15:53</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17"> Set of Lego Duplo Zoo Animals- Elephants Seal Bear </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Location: USA </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Item: 220751068780 </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Seller User ID: officer6945 </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Feedback: 1,036 | 99.4% </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">View similar active items | Sell one like this </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">20 Bids </td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">Sold $22.50 End Date:Mar-14 15:50</td> </tr> </tbody></table>
mirabeau said:
Could you post a small example, say 15-20 cells in ColumnA showing just what you want done with these.

Your request is too vague as it stands for me (at least) to be able to give any more feedback.
Click to expand...
 
Last edited:
Upvote 0
How does this work for you? Everything is from row 2 down in case of headers, but very easily modified to start from row 1 as you ask.
Code: 
Sub copyranges()
Dim colnum As Integer, e As Range
colnum = 3
Set e = Range("A2")
Do
    If e(2) = "" Then
        e.Copy Cells(2, colnum)
        Set e = e.End(4)
    Else
        Range(e, e.End(4)).Copy Cells(2, colnum)
        Set e = e.End(4).End(4)
    End If
colnum = colnum + 1
Loop Until e.Row = Rows.Count
End Sub
 
Upvote 0
You posted this question 5 times in different forums on this board. Please do NOT do this, this is against forum rules (see rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).

Per forum rules, these others posts have all been removed. Please only post each question once, and only in its appropriate forum. Any clarifications, "bumps", or direct follow-ups to the original question should be posted back to the original thread.
 
Upvote 0
Dear thanks for response.but its copy same txt from a1:a8 to c1:c8, infect i want it to past c1:j8

mirabeau said:
How does this work for you? Everything is from row 2 down in case of headers, but very easily modified to start from row 1 as you ask.
Code: 
Sub copyranges()
Dim colnum As Integer, e As Range
colnum = 3
Set e = Range("A2")
Do
    If e(2) = "" Then
        e.Copy Cells(2, colnum)
        Set e = e.End(4)
    Else
        Range(e, e.End(4)).Copy Cells(2, colnum)
        Set e = e.End(4).End(4)
    End If
colnum = colnum + 1
Loop Until e.Row = Rows.Count
End Sub
Click to expand...
 
Upvote 0
Over all your progress is too much great.

needassistance said:
<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt; width: 192pt;" width="256" height="17">infect i need to collect data from beginning till blank cell like after sold there is blank cell or some time last value is not like sold. but i need that
i collect data from start till blank and ttranspose it.
then re collect data from all worked black cell till next blank cell


Sure.
I have data like this in a:a

HUGE 100% LEGO DUPLO LOT of 200 ANIMALS BOB BUILDER </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Location: USA </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Item: 280644259567 </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Seller User ID: sislandgirl1983 </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Feedback: 802 | 100% </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">View similar active items | Sell one like this </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Buy It Now </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">Sold $20.50 End Date:Mar-14 16:12 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17"> DUPLOS,LEGOS,US?ED,SET OF 6 MISC BEDROOM PIECES </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Location: USA </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Item: 310303791069 </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Store: Ashlees Fine Things </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Seller User ID: nick9372 </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Feedback: 1,404 | 100% </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">View similar active items | Sell one like this </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Enlarge </td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Buy It Now </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">Sold $9.99 End Date:Mar-14 15:53</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17"> Set of Lego Duplo Zoo Animals- Elephants Seal Bear </td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Location: USA </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">Item: 220751068780 </td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Seller User ID: officer6945 </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="3" style="height: 12.75pt;" height="17">Feedback: 1,036 | 99.4% </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">View similar active items | Sell one like this </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">20 Bids </td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="4" style="height: 12.75pt;" height="17">Sold $22.50 End Date:Mar-14 15:50</td> </tr> </tbody></table>
Click to expand...
 
Upvote 0
I can only repeat:
mirabeau said:
Your request is too vague as it stands for me (at least) to be able to give any more feedback.
Click to expand...
The clearer you are about what you want, the more likely you are to get it.

Give a reasonably concise, simplified example showing clearly where you want the elements of ColumnA to go.

Don't give example with long strings like your above stuff since these don't add anything but just confuse matters.

If you really think you needassistance then be clear about just what is the assistance you need.
 
Upvote 0
I need transpose same txt through above script from a1:a8 to c1:c8, infect i want it to past c1:j1
your current last script give me data from row to row a1:a8 to c2-c9 but i need it c2:j2 range


Thanks for your too much cooperation and positive response i will next time follow your advice ans instructions
 
Last edited:
Upvote 0
I guess for about the last time I must ask that you give a meaningful illustration if you really need assistance.

Repetition of vague verbiage is no help at all.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
375
sijpie
sijpie
T
  • Solved
Transposing(?) data from row into multiple shorter columns
Replies
5
Views
325
arun_eagertolearn
A
excelbytes
  • Solved
VBA Code To Copy Data Into Next Blank Column
Replies
5
Views
613
mumps
mumps
L
  • Question Question
Transposing data accross a fixed number of columns
2
Replies
17
Views
975
Fluff
Fluff
T
  • Question Question
Excel online Script to move data from one sheet to another (NOT VBA)
Replies
1
Views
392
tonywatsonhelp
T

Forum statistics

Threads
1,224,521
Messages
6,179,286
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top