Transpose data

S

sachin483

Board Regular
Joined
Mar 31, 2015
Messages
157
Office Version
  1. 2019
Platform
  1. Windows
i have the data in this format

[TABLE="width: 166"]
<tbody>[TR]
[TD]srno[/TD]
[TD]desg[/TD]
[TD]ach[/TD]
[TD]pmpm[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SRR[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]120523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SRR[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]135523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SRR[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]155523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SRR[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]205523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SRR[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]255523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMF[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]118523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMF[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]133523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMF[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]153523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMF[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]205523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMF[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]255523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMS[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]116523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMS[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]131523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMS[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]151523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMS[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]205523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMS[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]255523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMT[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]114523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMT[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]129523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMT[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]149523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMT[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]255523[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SRR[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]123948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SRR[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]138948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SRR[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]158948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SRR[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]208948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SRR[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]258948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMF[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]121948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMF[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]136948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMF[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]156948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMF[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]208948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMF[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]258948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMS[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]119948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMS[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]134948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMS[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]154948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMS[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]208948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMS[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]258948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMT[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]117948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMT[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]132948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMT[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]152948[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMT[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]258948[/TD]
[/TR]
</tbody>[/TABLE]


i want to transpose the same in below format so how can it be done

[TABLE="width: 483"]
<tbody>[TR]
[TD]srno[/TD]
[TD]descg[/TD]
[TD]ach[/TD]
[TD]ach[/TD]
[TD]ach[/TD]
[TD]ach[/TD]
[TD]ach[/TD]
[TD]pmpm[/TD]
[TD]pmpm[/TD]
[TD]pmpm[/TD]
[TD]pmpm[/TD]
[TD]pmpm[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SRR[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]120523[/TD]
[TD="align: right"]135523[/TD]
[TD="align: right"]155523[/TD]
[TD="align: right"]205523[/TD]
[TD="align: right"]255523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMF[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]118523[/TD]
[TD="align: right"]133523[/TD]
[TD="align: right"]153523[/TD]
[TD="align: right"]205523[/TD]
[TD="align: right"]255523[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]LMT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SRR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]LMT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try this in a copy of your workbook.

Code: 
Sub Transpose_Data()
  Dim a As Variant, ach As Variant, pmpm As Variant
  Dim lc As Long, i As Long, k As Long, MaxCols As Long
  
  a = Range("A1", Range("D" & Rows.Count).End(xlUp)).Value
  ReDim ach(1 To UBound(a), 1 To UBound(a) + 3)
  ReDim pmpm(1 To UBound(a), 1 To UBound(a))
  lc = UBound(ach, 2)
  For i = 2 To UBound(a)
    If a(i, 1) & "|" & a(i, 2) <> a(i - 1, 1) & "|" & a(i - 1, 2) Then
      k = k + 1
      ach(k, 1) = a(i, 1): ach(k, 2) = a(i, 2)
    End If
    ach(k, lc) = ach(k, lc) + 1
    If ach(k, lc) > MaxCols Then MaxCols = ach(k, lc)
    ach(k, ach(k, lc) + 2) = a(i, 3)
    pmpm(k, ach(k, lc)) = a(i, 4)
  Next i
  Application.ScreenUpdating = False
  With Range("F1").Resize(, MaxCols + 2)
    .Value = "ach"
    .Resize(, 2).Value = Array("smo", "desg")
    .Offset(1).Resize(k).Value = ach
    .EntireColumn.AutoFit
    With .Offset(, MaxCols + 2).Resize(, MaxCols)
      .Value = "pmpm"
      .Offset(1).Resize(k).Value = pmpm
      .EntireColumn.AutoFit
    End With
  End With
  Application.ScreenUpdating = True
End Sub

Sample data and results


Book1
ABCDEFGHIJKLMNOPQ
1srnodesgachpmpmsmodesgachachachachachpmpmpmpmpmpmpmpmpmpm
2AASRR120120523AASRR120115110105103120523135523155523205523255523
3AASRR115135523AALMF118113108105103118523133523153523205523255523
4AASRR110155523AALMS116111106105103116523131523151523205523255523
5AASRR105205523AALMT114109105103114523129523149523255523
6AASRR103255523ABSRR120115110105103123948138948158948208948258948
7AALMF118118523ABLMF118113108105103121948136948156948208948258948
8AALMF113133523ABLMS116111106105103119948134948154948208948258948
9AALMF108153523ABLMT114109105103117948132948152948258948
10AALMF105205523
11AALMF103255523
12AALMS116116523
13AALMS111131523
14AALMS106151523
15AALMS105205523
16AALMS103255523
17AALMT114114523
18AALMT109129523
19AALMT105149523
20AALMT103255523
21ABSRR120123948
22ABSRR115138948
23ABSRR110158948
24ABSRR105208948
25ABSRR103258948
26ABLMF118121948
27ABLMF113136948
28ABLMF108156948
29ABLMF105208948
30ABLMF103258948
31ABLMS116119948
32ABLMS111134948
33ABLMS106154948
34ABLMS105208948
35ABLMS103258948
36ABLMT114117948
37ABLMT109132948
38ABLMT105152948
39ABLMT103258948
Transpose
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
575
madi1004
M
K
  • Question Question
sumif / countif
Replies
1
Views
260
kelvin_9
K
C
  • Question Question
Transposing Data from two columns with a common data identifier into another column one row at a time
Replies
4
Views
839
Fluff
Fluff
T
  • Question Question
Copying and Moving Portions of a Range
Replies
0
Views
259
travislr11
T
W
  • Solved
Set Worksheet as Variable in For Loop Error
2 3
Replies
26
Views
2K
WildBird
W

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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