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

[hinsonlam1118]

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 Time	End Time	Duration (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 Time	End Time	Duration (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!

 

[alansidman]

with power query also called Get & Transform Data

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
	A	B	C	D
1	Start Time	End Time	Duration (hour)	Value
2	8:00 AM	12:00 PM	4.00	Staff1
3	8:00 AM	12:00 PM	4.00	Staff2
4	8:00 AM	12:00 PM	4.00	Staff 3
5	9:00 AM	11:00 AM	4.00	Staff4
6	9:00 AM	11:00 AM	4.00	Staff5
Table1

 

[hinsonlam1118]

with power query also called Get & Transform Data

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
	A	B	C	D
1	Start Time	End Time	Duration (hour)	Value
2	8:00 AM	12:00 PM	4.00	Staff1
3	8:00 AM	12:00 PM	4.00	Staff2
4	8:00 AM	12:00 PM	4.00	Staff 3
5	9:00 AM	11:00 AM	4.00	Staff4
6	9:00 AM	11:00 AM	4.00	Staff5
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]

Welcome to MrExcel Message Board.
Set Sh1 to your sheet. Set sheet2 to your Blank sheet at Workbook
Try this:

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]

What about

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

 

[alansidman]

You could, but then you would not be able to make changes to the original sheet and have them reflected in your outcome.