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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
233
frewert
F
D
  • Solved
finding changes and looking back
2
Replies
10
Views
420
felixstraube
F
M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
116
Fluff
Fluff
S
  • Question Question
Excel VBA - Copy from one sheet to another with conditions
Replies
0
Views
503
servox
S
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
633
madi1004
M

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

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