Rearrange Data

L

LuxCoder

New Member
Joined
Apr 8, 2018
Messages
6
Hi,

I have data in this format:

[TABLE="width: 667"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]CID[/TD]
[TD]"C-00001"[/TD]
[TD]"C-00002"[/TD]
[TD]"C-00003"[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Alexandra Van de Vyvere[/TD]
[TD]Nathalie Pouradier Duteil[/TD]
[TD]Thomas Raguenaud[/TD]
[/TR]
[TR]
[TD]Waist[/TD]
[TD]75[/TD]
[TD]79[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]Half Waist Front[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Half Waist Back[/TD]
[TD]37[/TD]
[TD]39[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hips[/TD]
[TD]92[/TD]
[TD]98[/TD]
[TD]104[/TD]
[/TR]
[TR]
[TD]Half Hips Front[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Half Hips Back[/TD]
[TD]47[/TD]
[TD]53[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Thigh[/TD]
[TD]51[/TD]
[TD]57[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]Knee[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]Calf[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bottom[/TD]
[TD]29[/TD]
[TD]29[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Full length including waistband[/TD]
[TD]91[/TD]
[TD]89[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]Inside length[/TD]
[TD]66[/TD]
[TD]71[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Full rise[/TD]
[TD]63[/TD]
[TD]58[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]Front crotch including waistband[/TD]
[TD]26[/TD]
[TD]22[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]Back crotch including waistband[/TD]
[TD]37[/TD]
[TD]36[/TD]
[TD]46
[/TD]
[/TR]
</tbody>[/TABLE]

Which need in the following format:

[TABLE="width: 418"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]"C-00001"[/TD]
[TD]Waist[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Front[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Back[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hips[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Front[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Back[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Thigh[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Knee[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Calf[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bottom[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full length including waistband[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inside length[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full rise[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Front crotch including waistband[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Back crotch including waistband[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]"C-00002"[/TD]
[TD]Waist[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Front[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Back[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hips[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Front[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Back[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Thigh[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Knee[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Calf[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bottom[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full length including waistband[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inside length[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full rise[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Front crotch including waistband[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Back crotch including waistband[/TD]
[TD]36[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be great. Thanks.

Lj
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Do you need to do this just once, or many times over and over again ?
Is your data far larger than the sample shown here ?

If you only need to do this once, and if your data isn't too large, then a simple cut and paste will do it.
 
Upvote 0
Hi,

Previous post had wrong data, please use this reference.

I have data in this format:

[TABLE="class: cms_table, width: 667"]
<tbody>[TR]
[TD]CID[/TD]
[TD]"C-00001"[/TD]
[TD]"C-00002"[/TD]
[TD]"C-00003"[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Alexandra Van de Vyvere[/TD]
[TD]Nathalie Pouradier Duteil[/TD]
[TD]Thomas Raguenaud[/TD]
[/TR]
[TR]
[TD]Waist[/TD]
[TD]75[/TD]
[TD]79[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]Half Waist Front[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Half Waist Back[/TD]
[TD]37[/TD]
[TD]39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hips[/TD]
[TD]92[/TD]
[TD]98[/TD]
[TD]104[/TD]
[/TR]
[TR]
[TD]Half Hips Front[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Half Hips Back[/TD]
[TD]47[/TD]
[TD]53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thigh[/TD]
[TD]51[/TD]
[TD]57[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]Knee[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]Calf[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bottom[/TD]
[TD]29[/TD]
[TD]29[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Full length including waistband[/TD]
[TD]91[/TD]
[TD]89[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]Inside length[/TD]
[TD]66[/TD]
[TD]71[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full rise[/TD]
[TD]63[/TD]
[TD]58[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]Front crotch including waistband[/TD]
[TD]26[/TD]
[TD]22[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]Back crotch including waistband[/TD]
[TD]37[/TD]
[TD]36[/TD]
[TD]46[/TD]
[/TR]
</tbody>[/TABLE]


Which need in the following format:

[TABLE="width: 418"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]"C-00001"[/TD]
[TD]Waist[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Front[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Back[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hips[/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Front[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Back[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Thigh[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Knee[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Calf[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bottom[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full length including waistband[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inside length[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full rise[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Front crotch including waistband[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Back crotch including waistband[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]"C-00002"[/TD]
[TD]Waist[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Front[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Waist Back[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hips[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Front[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Half Hips Back[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Thigh[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Knee[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Calf[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bottom[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full length including waistband[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Inside length[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Full rise[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Front crotch including waistband[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Back crotch including waistband[/TD]
[TD]36[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be great. Thanks.

Lj​





​
 
Upvote 0
Thank you Sir for replying.

There are around 600 customers and their data. Cannot copy paste all of them.

Regards

Lj
 
Upvote 0
How about
Code: 
Sub LuxCoder()
   Dim Ary As Variant, Hdr As Variant
   Dim i As Long, UsdCols As Long
   
   With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
      UsdCols = .Cells(1, Columns.Count).End(xlToLeft).Column
      Hdr = .Range("A1", .Cells(1, UsdCols)).Value
      Ary = .Range("A3", .Range("A" & Rows.Count).End(xlUp).Offset(, UsdCols - 1)).Value
   End With
   For i = 2 To UBound(Ary, 2)
      With Sheets("[COLOR=#ff0000]sheet2[/COLOR]").Range("B" & Rows.Count).End(xlUp).Offset(1)
         .Offset(, -1).Value = Hdr(1, i)
         .Resize(UBound(Ary), 2).Value = Application.Index(Ary, Evaluate("row(1:15)"), Array(1, i))
      End With
   Next i
End Sub
Change sheet names in red to suit
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
204
frewert
F
D
  • Solved
finding changes and looking back
2
Replies
10
Views
411
felixstraube
F
M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
98
Fluff
Fluff
S
  • Question Question
Excel VBA - Copy from one sheet to another with conditions
Replies
0
Views
491
servox
S
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
589
madi1004
M

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

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