Transposing data

[wlln001]

I have columns of data which i need to transpose to multiple rows.. The special paste function does not do what I require..

example
3 columns with data

<TABLE style="WIDTH: 267pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=355 border=0 x:str><COLGROUP><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7204" width=197><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 148pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=197 height=17>interface FastEthernet0/1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 98pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=130>description AP53</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 21pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=28>exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>interface FastEthernet0/2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">description AP54</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>interface FastEthernet0/3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">description AP55</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">exit</TD></TR></TBODY></TABLE>



need to transpose this to.....



<TABLE style="WIDTH: 148pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=197 border=0 x:str><COLGROUP><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7204" width=197><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 148pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=197 height=17>interface FastEthernet0/1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>description AP53</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>interface FastEthernet0/2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>description AP54</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>exit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>interface FastEthernet0/3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>description AP55</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>exit</TD></TR></TBODY></TABLE>

Hope someone can help

 

[MickG]

Based on your data being in columns "A to C", try this:-

[COLOR="Navy"]Sub[/COLOR] MG23Mar15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("C" & rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count * 3)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        c = c + 1
        Ray(c) = Dn
    [COLOR="Navy"]Next[/COLOR] Dn
Rng.ClearContents
Range("A1").Resize(Rng.Count) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hiker95]

wlln001,

Welcome to the MrExcel forum.


Sample raw data in worksheet Sheet1 beginning in cell A1:

Excel Workbook
	A	B	C
1	interface FastEthernet0/1	description AP53	exit
2	interface FastEthernet0/2	description AP54	exit
3	interface FastEthernet0/3	description AP55	exit
4
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A
1	interface FastEthernet0/1
2	description AP53
3	exit
4	interface FastEthernet0/2
5	description AP54
6	exit
7	interface FastEthernet0/3
8	description AP55
9	exit
10
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub TransData()
' hiker95, 03/23/2011
' http://www.mrexcel.com/forum/showthread.php?t=538122
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, LC As Long, a As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
NR = 1
For a = 1 To LR Step 1
  LC = w1.Cells(1, Columns.Count).End(xlToLeft).Column
  wR.Cells(NR, 1).Resize(LC).Value = Application.Transpose(w1.Cells(a, 1).Resize(, LC).Value)
  NR = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Next a
wR.Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the

TransData​

macro.


The above macro code will adjust for a varying number of raw data columns.

 

[wlln001]

Thanks Guys

Fantastic stuff

Many Thanks