How can i put some rows in colums?

[macro001]

Hi to all,
I have excel like this

21005-2 <colgroup><col span="3"></colgroup><tbody> </tbody> S,M,L 3,3,3 <tbody> </tbody> 21005-1 S,M,L 2,2,2 <tbody> </tbody> 45699 S,M,L,XL 1,5,3,0 <tbody> </tbody>

<colgroup><col span="3"></colgroup><tbody>
</tbody>

now i must trasform in colums like this:

21005-2 <colgroup><col span="3"></colgroup><tbody> </tbody>	S	3
21005-2 <colgroup><col span="3"></colgroup><tbody> </tbody>	M	3
21005-2 <colgroup><col span="3"></colgroup><tbody> </tbody>	L	3
21005-1	S	2
21005-1	M	2
21005-1	L	2
45699	S	1
45699	M	5
45699	L	3
45699	XL	0

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 56px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

3,3,3

<tbody>
</tbody>

</body>How can do this? Thx

 

[hiker95]

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
	A	B	C
1	21005-2	S,M,L	3,3,3
2	21005-1	S,M,L	2,2,2
3	45699	S,M,L,XL	1,5,3,0
4
5
6
7
8
9
10
11
Sheet1

And, after the macro:

Excel 2007
	A	B	C
1	21005-2	S	3
2	21005-2	M	3
3	21005-2	L	3
4	21005-1	S	2
5	21005-1	M	2
6	21005-1	L	2
7	45699	S	1
8	45699	M	5
9	45699	L	3
10	45699	XL	0
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.

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.

 

[macro001]

perfect explanation and the macro works fine
thx very much mate

 

[macro001]

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

	A	B	C
1	21005-2	S,M,L	3,3,3​
2	21005-1	S,M,L	2,2,2​
3	3344	Universale	5​

<tbody>
</tbody>

 

[hiker95]

perfect explanation and the macro works fine
thx very much mate

macro001,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[macro001]

yes thx for help
but i have find a error, look up
thx

 

[hiker95]

I can have also the situation with the SIZE = "Universale" without comma and with just 1 Value, like ID 3

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
	A	B	C
1	21005-2	S,M,L	3,3,3
2	21005-1	S,M,L	2,2,2
3	45699	S,M,L,XL	1,5,3,0
4	3344	Universale	5
5
6
7
8
9
10
11
12
Sheet1

And, after the new macro:

Excel 2007
	A	B	C
1	21005-2	S	3
2	21005-2	M	3
3	21005-2	L	3
4	21005-1	S	2
5	21005-1	M	2
6	21005-1	L	2
7	45699	S	1
8	45699	M	5
9	45699	L	3
10	45699	XL	0
11	3344	Universale	5
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).

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.

 

[macro001]

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

 

[hiker95]

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.

 

[macro001]

i'm not expert
sorry if u have lose your time with me
have nice day
goodbye