Separating list of text into multiple cells.rows

[Traceelewis71]

I have a list of positions for employees that are in one cell that I needs to be in rows for each employee. Text to column doesn't work and neither does any sort of text formula like =left, =mid, etc. I have over 2500 employees and th

Active Status	Emp ID	Worker	Hire Date	Position ID	Position for Worker
Yes <tbody> </tbody>	123456	John Smith <tbody> </tbody>	4/4/2018 <tbody> </tbody>	Job-6-2768 <tbody> </tbody>	Bartender - John Smith (+) Bartender Training - John Smith (+) FOH E Learning - John Smith (+) SA Training - John Smith (+) Server - John Smith Server Assistant-Tipped - John Smith (+) Server Training - John Smith (+) <tbody> </tbody>
Yes <tbody> </tbody>	234567	Jane Doe	2/27/2016 <tbody> </tbody>	Job-6-1033 <tbody> </tbody>	Bartender - Jane doe (+) Bartender Training - Jane doe (+) FOH E Learning - Jane doe (+) Server - Jane doe Server Training - Jane doe (+) Team Lead - Jane doe (+) <tbody> </tbody>
Yes <tbody> </tbody>	345678 <tbody> </tbody>	Adam Eve <tbody> </tbody>	3/30/2016 <tbody> </tbody>	Job-6-1060 <tbody> </tbody>	Bartender - Adam Eve (+) Bartender Training - Adam Eve (+) FOH E Learning - Adam Eve (+) Server - Adam Eve Server Training - Adam Eve (+) <tbody> </tbody>

<tbody>
</tbody>

 

[sandy666]

post a link to shared excel file with your example data. use dropbox, onedrive, googledrive or any similar

 

[DanteAmor]

I suppose your data on sheet1 is like this:


<b>Sheet1</b><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:98.85px;" /><col style="width:70.34px;" /><col style="width:102.65px;" /><col style="width:90.3px;" /><col style="width:90.3px;" /><col style="width:273.74px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Active Status</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Emp ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Worker</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Hire Date</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position for Worker</td></tr><tr style="height:130px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Bartender - John Smith (+)<br />Bartender Training - John Smith (+)<br />FOH E Learning - John Smith (+)<br />SA Training - John Smith (+)<br />Server - John Smith<br />Server Assistant-Tipped - John Smith (+)<br />Server Training - John Smith (+)</td></tr><tr style="height:111px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Bartender - Jane doe (+)<br />Bartender Training - Jane doe (+)<br />FOH E Learning - Jane doe (+)<br />Server - Jane doe<br />Server Training - Jane doe (+)<br />Team Lead - Jane doe (+)</td></tr><tr style="height:93px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Bartender - Adam Eve (+)<br />Bartender Training - Adam Eve (+)<br />FOH E Learning - Adam Eve (+)<br />Server - Adam Eve<br />Server Training - Adam Eve (+)</td></tr></table>

-----------

The data on sheet 2 will look like this:

<b>Sheet2</b><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:112.16px;" /><col style="width:47.52px;" /><col style="width:101.7px;" /><col style="width:71.29px;" /><col style="width:116.91px;" /><col style="width:362.14px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Active Status</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Emp ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Worker</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Hire Date</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position ID</td><td style="background-color:#c2d69a; font-weight:bold; text-align:center; ">Position for Worker</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Bartender - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Bartender Training - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >FOH E Learning - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >SA Training - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Server - John Smith</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Server Assistant-Tipped - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Yes</td><td style="text-align:right; ">123456</td><td >John Smith</td><td style="text-align:right; ">04/04/2018</td><td >Job-6-2768</td><td >Server Training - John Smith (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Bartender - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Bartender Training - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >FOH E Learning - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Server - Jane doe</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Server Training - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Yes</td><td style="text-align:right; ">234567</td><td >Jane Doe</td><td style="text-align:right; ">27/02/2016</td><td >Job-6-1033</td><td >Team Lead - Jane doe (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Bartender - Adam Eve (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Bartender Training - Adam Eve (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >FOH E Learning - Adam Eve (+)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Server - Adam Eve</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >Yes</td><td style="text-align:right; ">345678</td><td >Adam Eve</td><td style="text-align:right; ">30/03/2016</td><td >Job-6-1060</td><td >Server Training - Adam Eve (+)</td></tr></table>

------
Use this macro:

Sub Separating_list()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, p As Variant, n As Long
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Cells.ClearContents
    sh1.Rows(1).Copy sh2.Rows(1)
    n = 2
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        For Each p In Split(c.Offset(, 5), Chr(10))
            sh2.Cells(n, "A").Resize(1, 5).Value = c.Resize(1, 5).Value
            sh2.Cells(n, "F").Value = p
            n = n + 1
        Next
    Next
    MsgBox "Done"
End Sub

------------

INSERT A MODULE
Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.
Close the editor (press Alt-Q). From Excel, press Alt-F8 to open the macro selector, and select Separating_listand press Run.

 

[Traceelewis71]

Thank you both!! I was trying to rewrite the report with a calculation to separate the field in the report in our system.

Dante!! This worked Thank you!!

 

[DanteAmor]

Thank you both!! I was trying to rewrite the report with a calculation to separate the field in the report in our system.

Dante!! This worked Thank you!!

I'm glad to help you. Thanks for the feedback.