Split two or more text in a column into rows without changing it associated row

[elfasus]

I have a worksheet with a data distributed as follows :


-- removed inline image ---
<table border="0" cellpadding="0" cellspacing="0" width="548"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2450; width:50pt" span="4" width="67"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2450; width:50pt" span="2" width="67"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td> <td style="width:50pt" width="67">Column B</td> <td style="width:50pt" width="67">Column C</td> <td style="width:50pt" width="67">Column D</td> <td style="width:50pt" width="67">Column E</td> <td style="width:62pt" width="82">Column F</td> <td style="width:50pt" width="67">Column G</td> <td style="width:50pt" width="67">Column H</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 2</td> <td>B2</td> <td>C2</td> <td>D2</td> <td>E2</td> <td>A B C </td> <td>G2</td> <td>H2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 3</td> <td>B3</td> <td>C3</td> <td>D3</td> <td>E3</td> <td>A B </td> <td>G3</td> <td>H3</td> </tr> </tbody></table>

and i want to split data in columns F into rows and without changing it associated row

and the final result would be like as follows:


-- removed inline image ---
<table border="0" cellpadding="0" cellspacing="0" width="548"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2450; width:50pt" span="4" width="67"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2450; width:50pt" span="2" width="67"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td> <td style="width:50pt" width="67">Column B</td> <td style="width:50pt" width="67">Column C</td> <td style="width:50pt" width="67">Column D</td> <td style="width:50pt" width="67">Column E</td> <td style="width:62pt" width="82">Column F</td> <td style="width:50pt" width="67">Column G</td> <td style="width:50pt" width="67">Column H</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 2</td> <td>B2</td> <td>C2</td> <td>D2</td> <td>E2</td> <td>A</td> <td>G2</td> <td>H2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 3</td> <td>B2</td> <td>C2</td> <td>D2</td> <td>E2</td> <td>B</td> <td>G2</td> <td>H2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 4</td> <td>B2</td> <td>C2</td> <td>D2</td> <td>E2</td> <td>C</td> <td>G2</td> <td>H2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 5
</td> <td>B3</td> <td>C3</td> <td>D3</td> <td>E3</td> <td>A</td> <td>G3</td> <td>H3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 6
</td> <td>B3</td> <td>C3</td> <td>D3</td> <td>E3</td> <td>B</td> <td>G3</td> <td>H3</td> </tr> </tbody></table>

Could you please help me?

Thank you in advance
Regards,
Elf

 

[gaurav23]

Hi Can you please further clarify what is the criteria you want in cloumn F.

 

[elfasus]

Hi..
Column F consist of text data only and it was delimited using spaces.
I want make the text data in columns F to be multiple row so i can pivot and counted as many as text data delimited in column F

Example :
If in column F have 4 text data delimited by space, i want it become 4 row without changing value in other columns which is associated with its column

Thanks in advance and appreciated
Regards,
Andy

 

[mirabeau]

Welcome Andy,

Would you like to try this VBA code and see if it works for you

Sub testcode()
Dim e As Range, x, k&, j&
Set e = Cells.Rows(2)
Do
x = Split(Cells(e.Row, "F"), " ", -1)
k = UBound(x)
If k > 0 Then
e(2).Resize(k).Insert
e.Copy e.Resize(k + 1)
For j = 0 To k
    Cells(e.Row + j, "F") = x(j)
    Cells(e.Row + j, "A") = "Row " & e.Row + j
Next j
End If
If Cells(e.Row + k, 1).End(4).Row = Rows.Count _
    Then Exit Do
Set e = e(k + 2)
Loop
End Sub

 

[elfasus]

Dear mirabeau,

Great, thanks it works!, but i found problem in the result table, please take a look and your advise

Data as follows :

<table border="0" cellpadding="0" cellspacing="0" width="512"><col style="width:48pt" span="8" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td> <td style="width:48pt" width="64">Column 1</td> <td style="width:48pt" width="64">Column 2</td> <td style="width:48pt" width="64">Column 3</td> <td style="width:48pt" width="64">Column 4</td> <td style="width:48pt" width="64">Column 5</td> <td style="width:48pt" width="64">Column 6</td> <td style="width:48pt" width="64">Column 7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 2</td> <td>Data 21</td> <td>Data 22</td> <td>Data 23</td> <td>Data 24</td> <td>A B C D</td> <td>Data 26</td> <td>Data 27</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 5</td> <td>Data 31</td> <td>Data 32</td> <td>Data 33</td> <td>Data 34</td> <td>A B C</td> <td>Data 36</td> <td>Data 37</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 4</td> <td>Data 41</td> <td>Data 42</td> <td>Data 43</td> <td>Data 44</td> <td>A B</td> <td>Data 46</td> <td>Data 47</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 10</td> <td>Data 51</td> <td>Data 52</td> <td>Data 53</td> <td>Data 54</td> <td>A</td> <td>Data 56</td> <td>Data 57</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 12</td> <td>Data 61</td> <td>Data 62</td> <td>Data 63</td> <td>Data 64</td> <td>A B C</td> <td>Data 66</td> <td>Data 67</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 15</td> <td>Data 71</td> <td>Data 72</td> <td>Data 73</td> <td>Data 74</td> <td>A B</td> <td>Data 76</td> <td>Data 77</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 8</td> <td>Data 81</td> <td>Data 82</td> <td>Data 83</td> <td>Data 84</td> <td>A B C D</td> <td>Data 86</td> <td>Data 87</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 20</td> <td>Data 91</td> <td>Data 92</td> <td>Data 93</td> <td>Data 94</td> <td>A B C D E</td> <td>Data 96</td> <td>Data 97</td> </tr> </tbody></table>
The result as follows :

<table border="0" cellpadding="0" cellspacing="0" width="512"><col style="width:48pt" span="8" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td> <td style="width:48pt" width="64">Column 1</td> <td style="width:48pt" width="64">Column 2</td> <td style="width:48pt" width="64">Column 3</td> <td style="width:48pt" width="64">Column 4</td> <td style="width:48pt" width="64">Column 5</td> <td style="width:48pt" width="64">Column 6</td> <td style="width:48pt" width="64">Column 7</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 2</td> <td>Data 21</td> <td>Data 22</td> <td>Data 23</td> <td>Data 24</td> <td>A</td> <td>Data 26</td> <td>Data 27</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 3</td> <td>Data 21</td> <td>Data 22</td> <td>Data 23</td> <td>Data 24</td> <td>B</td> <td>Data 26</td> <td>Data 27</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 4</td> <td>Data 21</td> <td>Data 22</td> <td>Data 23</td> <td>Data 24</td> <td>C</td> <td>Data 26</td> <td>Data 27</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 5</td> <td>Data 21</td> <td>Data 22</td> <td>Data 23</td> <td>Data 24</td> <td>D</td> <td>Data 26</td> <td>Data 27</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 6</td> <td>Data 31</td> <td>Data 32</td> <td>Data 33</td> <td>Data 34</td> <td>A</td> <td>Data 36</td> <td>Data 37</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 7</td> <td>Data 31</td> <td>Data 32</td> <td>Data 33</td> <td>Data 34</td> <td>B</td> <td>Data 36</td> <td>Data 37</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 8</td> <td>Data 31</td> <td>Data 32</td> <td>Data 33</td> <td>Data 34</td> <td>C</td> <td>Data 36</td> <td>Data 37</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 9</td> <td>Data 41</td> <td>Data 42</td> <td>Data 43</td> <td>Data 44</td> <td>A</td> <td>Data 46</td> <td>Data 47</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 10</td> <td>Data 41</td> <td>Data 42</td> <td>Data 43</td> <td>Data 44</td> <td>B</td> <td>Data 46</td> <td>Data 47</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 10</td> <td>Data 51</td> <td>Data 52</td> <td>Data 53</td> <td>Data 54</td> <td>A</td> <td>Data 56</td> <td>Data 57</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 12</td> <td>Data 61</td> <td>Data 62</td> <td>Data 63</td> <td>Data 64</td> <td>A</td> <td>Data 66</td> <td>Data 67</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 13</td> <td>Data 61</td> <td>Data 62</td> <td>Data 63</td> <td>Data 64</td> <td>B</td> <td>Data 66</td> <td>Data 67</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 14</td> <td>Data 61</td> <td>Data 62</td> <td>Data 63</td> <td>Data 64</td> <td>C</td> <td>Data 66</td> <td>Data 67</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 15</td> <td>Data 71</td> <td>Data 72</td> <td>Data 73</td> <td>Data 74</td> <td>A</td> <td>Data 76</td> <td>Data 77</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 16</td> <td>Data 71</td> <td>Data 72</td> <td>Data 73</td> <td>Data 74</td> <td>B</td> <td>Data 76</td> <td>Data 77</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 17</td> <td>Data 81</td> <td>Data 82</td> <td>Data 83</td> <td>Data 84</td> <td>A</td> <td>Data 86</td> <td>Data 87</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 18</td> <td>Data 81</td> <td>Data 82</td> <td>Data 83</td> <td>Data 84</td> <td>B</td> <td>Data 86</td> <td>Data 87</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 19</td> <td>Data 81</td> <td>Data 82</td> <td>Data 83</td> <td>Data 84</td> <td>C</td> <td>Data 86</td> <td>Data 87</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 20</td> <td>Data 81</td> <td>Data 82</td> <td>Data 83</td> <td>Data 84</td> <td>D</td> <td>Data 86</td> <td>Data 87</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 21</td> <td>Data 91</td> <td>Data 92</td> <td>Data 93</td> <td>Data 94</td> <td>A</td> <td>Data 96</td> <td>Data 97</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 22</td> <td>Data 91</td> <td>Data 92</td> <td>Data 93</td> <td>Data 94</td> <td>B</td> <td>Data 96</td> <td>Data 97</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 23</td> <td>Data 91</td> <td>Data 92</td> <td>Data 93</td> <td>Data 94</td> <td>C</td> <td>Data 96</td> <td>Data 97</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 24</td> <td>Data 91</td> <td>Data 92</td> <td>Data 93</td> <td>Data 94</td> <td>D</td> <td>Data 96</td> <td>Data 97</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 25</td> <td>Data 91</td> <td>Data 92</td> <td>Data 93</td> <td>Data 94</td> <td>E</td> <td>Data 96</td> <td>Data 97</td> </tr> </tbody></table>

There is problem with Row 10 (it's double right?)
Could you help with this problem due to i am very new in vba script in excel

Thanks in advance
Andy (Elf)

 

[mirabeau]

Andy,

Try this modified version

Sub testcode2()
Dim e As Range, x, k&, j&
Set e = Cells.Rows(2)
Do
x = Split(Cells(e.Row, "F"), " ", -1)
k = UBound(x)
If k > 0 Then
e(2).Resize(k).Insert
e.Copy e.Resize(k + 1)
For j = 0 To k
    Cells(e.Row + j, "F") = x(j)
Next j
End If
If Cells(e.Row + k, 1).End(4).Row = Rows.Count Then
    Set c = Cells(2, 1)
    c.AutoFill Range(c, c.End(4)), 2
    Exit Do
End If
Set e = e(k + 2)
Loop
End Sub

 

[elfasus]

Dear mirabeau,

Working perfectly!! Nice and thank you very much!
Would you mind if you explain to me line by line the VBA Script?
in case my data is not on the same row eg. starting at row 7 or the data text is not in column "F" instead in column "AG" for instance.
How to change the VBA script?

Thanks and regards,
Andy

 

[mirabeau]

Andy,

Here's the code (slightly modified but no functional change) with some attempt at explaining the various steps and lines.

Maybe it will be of some help.

Sub testcode2()
Dim e As Range, x, k&, j&, c As Range
'the 2 on the next line is the row where the data start
Set e = Cells.Rows(2)
Set c = e.Resize(, 1)
Do
'the "F" on next line denotes cell in Column F is split
x = Split(Cells(e.Row, "F"), " ", -1)
k = UBound(x)
If k > 0 Then
'k > 0 is the case where there is at least one space to split
'insert rows according to number of spaces in (say) A B C
'e(2) is the next row down from the row where the most recent split occurs
e(2).Resize(k).Insert
e.Copy e.Resize(k + 1)
For j = 0 To k
    Cells(e.Row + j, "F") = x(j)
Next j
End If
'the following lines determine when the code should terminate
'and list the correct row numbers in Column A
If Cells(e.Row + k, 1).End(4).Row = Rows.Count Then
    c.AutoFill Range(c, c.End(4)), 2
    Exit Do
End If
'reset the next row to split the cell in Col F
Set e = e(k + 2)
Loop
End Sub

 

[elfasus]

Dear mirabeau,

Sorry for late reply
Thanks a lot for your help! it was perfect.
thumbs up for you

Regards,
Andy (Elf)

 

[mirabeau]

Dear mirabeau,

Sorry for late reply
Thanks a lot for your help! it was perfect.
thumbs up for you

Regards,
Andy (Elf)

Thanks for the feedback Andy (Elf).

It's always encouraging to get.