Help With Data Transpose - Different Columns Combined into 1

S

Shadefalcon

New Member
Joined
Feb 15, 2013
Messages
21
Hey all,

I need help with a data transpose. If you check out what I pasted below for the first data set, you will see that I have columns for different attributes and values. Some attributes appear in different columns and their values are always to the right. What I want to do is transform this data to how it looks in the second variation - all of the attributes are listed across the top, and their values are associated underneath. Does anyone have a formula or macro that might help me achieve this?

Thanks,
M

[TABLE="width: 704"]
<colgroup><col style="width:48pt" span="11" width="64"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 704, colspan: 11"]Initial Data[/TD]
[/TR]
[TR]
[TD="class: xl65"]Part #[/TD]
[TD="class: xl65"]Attribute 1[/TD]
[TD="class: xl65"]Value 1[/TD]
[TD="class: xl65"]Attribute 2[/TD]
[TD="class: xl65"]Value 2[/TD]
[TD="class: xl65"]Attribute 3[/TD]
[TD="class: xl65"]Value 3[/TD]
[TD="class: xl65"]Attribute 4[/TD]
[TD="class: xl65"]Value 4[/TD]
[TD="class: xl65"]Attribute 5[/TD]
[TD="class: xl65"]Value 5[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]Brand[/TD]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"]Type[/TD]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"]Color[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]Height[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]Amperage[/TD]
[TD="class: xl65"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]Brand[/TD]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"]Type[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]Color[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]Height[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Length[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Brand[/TD]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"]Type[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]Height[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Voltage[/TD]
[TD="class: xl65"]50[/TD]
[TD="class: xl65"]Length[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]Brand[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]Color[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]Height[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Voltage[/TD]
[TD="class: xl65"]60[/TD]
[TD="class: xl65"]Style[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Brand[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]Color[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]Voltage[/TD]
[TD="class: xl65"]40[/TD]
[TD="class: xl65"]Amperage[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]Feed[/TD]
[TD="class: xl65"]J[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]Brand[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]Color[/TD]
[TD="class: xl65"]Green[/TD]
[TD="class: xl65"]Voltage[/TD]
[TD="class: xl65"]60[/TD]
[TD="class: xl65"]Amperage[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]Feed[/TD]
[TD="class: xl65"]J[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 640"]
<colgroup><col style="width:48pt" span="10" width="64"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 640, colspan: 10"]How I Want it to Look Like After Transpose[/TD]
[/TR]
[TR]
[TD="class: xl67"]Part #[/TD]
[TD="class: xl67"]Amperage[/TD]
[TD="class: xl67"]Brand[/TD]
[TD="class: xl67"]Color[/TD]
[TD="class: xl67"]Feed[/TD]
[TD="class: xl67"]Height[/TD]
[TD="class: xl67"]Length[/TD]
[TD="class: xl67"]Style[/TD]
[TD="class: xl67"]Type[/TD]
[TD="class: xl67"]Voltage[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"]Red[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]2[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]2[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"]Blue[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]4[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67, align: right"]50[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]4[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67"]Red[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]60[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67"]Blue[/TD]
[TD="class: xl67"]J[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]6[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67"]Green[/TD]
[TD="class: xl67"]J[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
The initial problem that pops out at me is that you have several attributes in a single column and even one attribute can show up in multiple columns.

How large is your actual data set?
 
Upvote 0
Yeah I know, it doesn't make a ton of sense but this is how the data was given to me. Attributes are duplicated in different columns.

Actual data set is ~70 columns and ~6k rows
 
Upvote 0
Try this:-
The code assumes that all data cells are filled !!!
Results sheet2 starting "A1"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG31May47
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] oHds [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Cells(1).CurrentRegion
ReDim nRay(1 To UBound(Ray, 1), 1 To 10)
oHds = Array("Part #", "Amperage", "Brand", "Color", "Feed", "Height", "Length", "Style", "Type", "Voltage")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(oHds)
        nRay(1, n + 1) = oHds(n)
    [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
        [COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 2
            Col = Application.Match(Ray(n, Ac), oHds, 0)
            nRay(n, 1) = Ray(n, 1)
            nRay(n, Col) = Ray(n, Ac + 1)
        [COLOR="Navy"]Next[/COLOR] Ac
  [COLOR="Navy"]Next[/COLOR] n

[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), 10)
     .Value = nRay
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG31May30
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] oHds [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Cells(1).CurrentRegion
ReDim nRay(1 To UBound(Ray, 1), 1 To 10)
oHds = Array("Part #", "Amperage", "Brand", "Color", "Feed", "Height", "Length", "Style", "Type", "Voltage")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(oHds)
        nRay(1, n + 1) = oHds(n)
    [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
        [COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 2
            [COLOR="Navy"]If[/COLOR] Not IsError(Application.Match(Ray(n, Ac), oHds, 0)) [COLOR="Navy"]Then[/COLOR]
            Col = Application.Match(Ray(n, Ac), oHds, 0)
            nRay(n, 1) = Ray(n, 1)
            nRay(n, Col) = Ray(n, Ac + 1)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac
  [COLOR="Navy"]Next[/COLOR] n

[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), 10)
     .Value = nRay
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
That worked for the sample data set.

Now if I want to modify the code to add more attributes to the data set, I just make the changes where I've highlighted in Red?

Sub MG31May30
Dim Ray As Variant, Dn As Range, n As Long
Dim Col As Integer, oHds As Variant, Ac As Integer, c As Long
Ray = Cells(1).CurrentRegion
ReDim nRay(1 To UBound(Ray, 1), 1 To 10)
oHds = Array("Part #", "Amperage", "Brand", "Color", "Feed", "Height", "Length", "Style", "Type", "Voltage")
For n = 0 To UBound(oHds)
nRay(1, n + 1) = oHds(n)
Next n
For n = 2 To UBound(Ray, 1)
For Ac = 2 To UBound(Ray, 2) Step 2
If Not IsError(Application.Match(Ray(n, Ac), oHds, 0)) Then
Col = Application.Match(Ray(n, Ac), oHds, 0)
nRay(n, 1) = Ray(n, 1)
nRay(n, Col) = Ray(n, Ac + 1)
End If
Next Ac
Next n

With Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), 10)
.Value = nRay
.Columns.AutoFit
.Borders.Weight = 2
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Compiling and then splitting an array (array formula or VBA)
Replies
4
Views
315
Matrovsky
M
S
  • Solved
Ranking with NET and with in the group
Replies
7
Views
161
sksanjeev786
S
R
  • Question Question
VBA Inserting rows based on Column criteria
Replies
4
Views
350
myall_blues
myall_blues
I
  • Question Question
Advice for Could Not Find The Specific Object message
2
Replies
12
Views
242
ipbr21054
I
S
  • Question Question
Data filters - how to use multiple filters to select rows, without adding further columns
Replies
3
Views
147
Cubist
Cubist

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