How can i put some rows in colums?

M

macro001

New Member
Joined
Sep 11, 2016
Messages
46
Hi to all,
I have excel like this[TABLE="width: 225"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 225"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]21005-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S,M,L[/TD]
[TD]
[TABLE="width: 225"]
<tbody>[TR]
[TD]3,3,3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]21005-1[/TD]
[TD]S,M,L[/TD]
[TD]
[TABLE="width: 225"]
<tbody>[TR]
[TD]2,2,2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]45699[/TD]
[TD]S,M,L,XL[/TD]
[TD]1,5,3,0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


now i must trasform in colums like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 225"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]21005-2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 225"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]21005-2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]M[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 225"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]21005-2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]L[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]21005-1[/TD]
[TD]S[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]21005-1[/TD]
[TD]M[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]21005-1[/TD]
[TD]L[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]45699[/TD]
[TD]S[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]45699[/TD]
[TD]M[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]45699[/TD]
[TD]L[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]45699[/TD]
[TD]XL[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 56px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 225"]
<tbody>[TR]
[TD]3,3,3[/TD]
[/TR]
</tbody>[/TABLE]
</body>How can do this? Thx
 
Sort by date Sort by votes
macro001,

Here is a macro solution for you to consider, based on your flat text displays.

Sample raw data in the active worksheet:


Excel 2007
ABC
121005-2S,M,L3,3,3
221005-1S,M,L2,2,2
345699S,M,L,XL1,5,3,0
4
5
6
7
8
9
10
11
Sheet1


And, after the macro:


Excel 2007
ABC
121005-2S3
221005-2M3
321005-2L3
421005-1S2
521005-1M2
621005-1L2
745699S1
845699M5
945699L3
1045699XL0
11
Sheet1


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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub RearrangeData()
' hiker95, 10/16/2016, ME970641
Dim r As Long, lr As Long, smx As Long, sb, sc
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  If InStr(Cells(r, 2), ",") Then
    sb = Split(Cells(r, 2), ",")
  End If
  If InStr(Cells(r, 3), ",") Then
    sc = Split(Cells(r, 3), ",")
  End If
  smx = Application.Max(UBound(sb), UBound(sc))
  Rows(r + 1).Resize(smx).Insert
  Cells(r, 1).Resize(smx + 1) = Cells(r, 1)
  Cells(r, 2).Resize(UBound(sb) + 1) = Application.Transpose(sb)
  Cells(r, 3).Resize(UBound(sc) + 1) = Application.Transpose(sc)
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RearrangeData macro.
 
Last edited:
Upvote 0
ehm no i have find a error,
the line 934 set the wrong "SIZE" , in this case must be "Universale" and not "XS"
i not understand why your macro jump until the line 951
I not know visual basic language is very hard for me edit your macro
can u help me again?
thx


I can have also the situation with the SIZE = "Universale" without comma and with just 1 Value, like ID 3
21005-2S,M,L
3,3,3​
21005-1S,M,L
2,2,2​
Universale
5​

<tbody>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]

[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3344[/TD]

</tbody>
Chrysanthemum.jpg
 
Last edited:
Upvote 0
I can have also the situation with the SIZE = "Universale" without comma and with just 1 Value, like ID 3
Click to expand...

macro001,

Here is a new macro solution for you to consider, based on your flat text display in your reply #4.

Sample raw data:


Excel 2007
ABC
121005-2S,M,L3,3,3
221005-1S,M,L2,2,2
345699S,M,L,XL1,5,3,0
43344Universale5
5
6
7
8
9
10
11
12
Sheet1


And, after the new macro:


Excel 2007
ABC
121005-2S3
221005-2M3
321005-2L3
421005-1S2
521005-1M2
621005-1L2
745699S1
845699M5
945699L3
1045699XL0
113344Universale5
12
Sheet1


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).

Code: 
Sub RearrangeData_V2()
' hiker95, 10/18/2016, ME970641
Dim r As Long, lr As Long, smx As Long, sb, sc
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  If InStr(Cells(r, 2), ",") And InStr(Cells(r, 3), ",") Then
    If InStr(Cells(r, 2), ",") Then
      sb = Split(Cells(r, 2), ",")
    End If
    If InStr(Cells(r, 3), ",") Then
      sc = Split(Cells(r, 3), ",")
    End If
    smx = Application.Max(UBound(sb), UBound(sc))
    Rows(r + 1).Resize(smx).Insert
    Cells(r, 1).Resize(smx + 1) = Cells(r, 1)
    Cells(r, 2).Resize(UBound(sb) + 1) = Application.Transpose(sb)
    Cells(r, 3).Resize(UBound(sc) + 1) = Application.Transpose(sc)
  Else
    ' do nothing
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RearrangeData_V2 macro.
 
Upvote 0
already error, sorry mate
in this line(1266) not have splitted correct
in this case i like to have
7502-1 S/M 1
7502-1 M/L 1
7503-1 S/M 1
7503-1 M/L 0
the line C-1267 have just one value=1, in this case must be 1,0 but excel automatic remove the 0

Chrysanthemum.jpg
 
Last edited:
Upvote 0
macro001,

You keep changing what your raw data looks like?????

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
1
  • Question Question
How can I create a pivot table pie chart to summarise each row that has more than one column that uses the same multiple choice list
Replies
0
Views
723
108monkeys
1
M
  • Question Question
How add a range of data o OLAppointment.Body
Replies
0
Views
426
mecerrato
M
S
  • Solved
how can i extract data from cells?
Replies
2
Views
303
smilyben
S
H
  • Question Question
How do I create a table that then considers whether or not clients appear in other tables?
Replies
0
Views
460
HelpPlease123
H

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