How do I split out delimited data in one cell and repeat the adjacent cell against each item of delimited data separated out?

hinsonlam1118

New Member
Joined
Jan 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi

I am working on the name attendance list in the excel table.
The data will be filled into the excel table as below automatically by using Microsoft Form.


Start TimeEnd TimeDuration (hour)Staff Name
8:00 AM​
12:00 PM​
4.00​
Staff1, Staff2, Staff 3
9:00 AM​
11:00 AM​
4.00​
Staff4, Staff5,

is there any method/programming to separate the 'Staff Name' by using ',' and go into next row? (please find the expected result as below)
For the 'Start Time', 'End Time' and 'Duration (hour)' can be copied into the related row.
It would be great if it can be automatically once the new data is filled in. if not, I could also do the exporting per month and apply the programme to separate the staff name together with their related information.

Start TimeEnd TimeDuration (hour)Staff Name
8:00 AM​
12:00 PM​
4.00​
Staff1
8:00 AM​
12:00 PM​
4.00​
Staff2
8:00 AM​
12:00 PM​
4.00​
Staff3
9:00 AM​
11:00 AM​
4.00​
Staff4
9:00 AM​
11:00 AM​
4.00​
Staff5

Your reply is highly appreciated and thanks so much for your help!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
with power query also called Get & Transform Data

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type text}, {"End Time", type text}, {"Duration (hour)", type text}, {"Staff Name", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Staff Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Staff Name.1", "Staff Name.2", "Staff Name.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Staff Name.1", type text}, {"Staff Name.2", type text}, {"Staff Name.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Start Time", "End Time", "Duration (hour)"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"})
in
    #"Removed Columns"

Book5
ABCD
1Start TimeEnd TimeDuration (hour)Value
28:00 AM12:00 PM4.00Staff1
38:00 AM12:00 PM4.00Staff2
48:00 AM12:00 PM4.00Staff 3
59:00 AM11:00 AM4.00Staff4
69:00 AM11:00 AM4.00Staff5
Table1
 

hinsonlam1118

New Member
Joined
Jan 17, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
with power query also called Get & Transform Data

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type text}, {"End Time", type text}, {"Duration (hour)", type text}, {"Staff Name", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Staff Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Staff Name.1", "Staff Name.2", "Staff Name.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Staff Name.1", type text}, {"Staff Name.2", type text}, {"Staff Name.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Start Time", "End Time", "Duration (hour)"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"})
in
    #"Removed Columns"

Book5
ABCD
1Start TimeEnd TimeDuration (hour)Value
28:00 AM12:00 PM4.00Staff1
38:00 AM12:00 PM4.00Staff2
48:00 AM12:00 PM4.00Staff 3
59:00 AM11:00 AM4.00Staff4
69:00 AM11:00 AM4.00Staff5
Table1
Hi Alansidman,

thanks so much for your quick reply.
it works! may i also ask if the outcome is possible to replace the original sheet?
now is creating the new sheet..

the reason is that i could do more formula in the same sheet.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,639
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
Set Sh1 to your sheet. Set sheet2 to your Blank sheet at Workbook
Try this:
VBA Code:
Sub TransformData()
Dim i As Long, j As Long, Lr1 As Long, Arr As Variant, F As Long, Lr2 As Long
Dim Sh1 As Worksheet, Sh2 As Worksheet, Nval As Variant
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Sh2.Range("A1:D1").Value = Sh1.Range("A1:D1").Value
Lr1 = Sh1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To Lr1
Lr2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row
Arr = Split(Sh1.Range("D" & i).Value, ",")
For F = 0 To UBound(Arr)
Nval = Arr(F)
If Nval = "" Then GoTo Resum
Sh2.Range("A" & Lr2 + F + 1 & ":C" & Lr2 + F + 1).Value = Sh1.Range("A" & i & ":C" & i).Value
Sh2.Range("D" & Lr2 + F + 1).Value = Nval
Resum:
Next F
Next i
Lr2 = Sh2.Cells(Rows.Count, 1).End(xlUp).Row
Sh1.Range("A1:D" & Lr2).Value = Sh2.Range("A1:D" & Lr2).Value
Sh2.Range("A1:D" & Lr2).ClearContents
End Sub
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
What about
VBA Code:
Sub test()
    Dim a, x As Variant
    Dim lr, i, l As Long
    With Sheets("sheet1")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    a = .Range("A2:D" & lr)
    End With
    With Sheets("sheet2")
    For i = 1 To UBound(a)
        x = Split(a(i, 4), ",")
            .Range("A2").Offset(l).Resize(UBound(x) + 1, 3) = Application.Index(a, i, 0)
            .Range("A2").Offset(l, 3).Resize(UBound(x) + 1) = Application.Transpose(x)
            l = UBound(x) + 1 + l
             Next
        End With
End Sub
 
Last edited:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
You could, but then you would not be able to make changes to the original sheet and have them reflected in your outcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,767
Members
416,136
Latest member
senthil_sk

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
Top