How to split the name and display the status of task/project for each name?

[haha97]

Hi. I want to create a summary report. The table below (at the left side) show that project A is in progress and managed by M1 and M2 (need to key in by staff).
To create a summary report (table at the right side), I use INDEX and MATCH (with the help from helper column). But the problem is, it cannot separate the manager name to each row. Is there a way on how I can split M1/M2, each name to summary report and display the status for each. Thank you.

project_tracker.xlsx
	A		C			F	G	H	I	J	K	L
5											Date	4/4/2022
6	Date1		Project			status	manager	Helper			Project	A
7	4/4/2022		A			in progress	M1/M2	44655AM1/M2			Manager	Status Project
8											M1	-
9											M2	-
Sheet1

Cell Formulas
Range	Formula
H7	H7	=A7&C7&G7
L8:L9	L8	=IFNA(INDEX(F:F,MATCH(L$5&L$6&K8,H:H,0)),"-")

Cells with Data Validation
Cell	Allow	Criteria
L6	Any value

Desired output:

project_tracker.xlsx
	A		C			F	G	H	I	J	K	L
5											Date	4/4/2022
6	Date1		Project			status	manager	Helper			Project	A
7	4/4/2022		A			in progress	M1/M2	44655AM1/M2			Manager	Status Project
8											M1	in progress
9											M2	in progress
Sheet1

Cell Formulas
Range	Formula
H7	H7	=A7&C7&G7

Cells with Data Validation
Cell	Allow	Criteria
L6	Any value

 

[haha97]

According to your profile you have 365 so why not use that?

Actually, this is for my office work and it is only supported Microsoft 2019. Thus, I cannot use other version.

 

[mumps]

Try this macro:

Sub SplitName()
    Application.ScreenUpdating = False
    Dim LastRow As Long, project As Range, v As Variant, i As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set project = Range("C:C").Find(Range("L2"), LookIn:=xlValues, lookat:=xlWhole)
    If Not project Is Nothing Then
        v = Split(Range("G" & project.Row), "/")
        For i = LBound(v) To UBound(v)
            Cells(Rows.Count, "K").End(xlUp).Offset(1) = v(i)
            Cells(Rows.Count, "L").End(xlUp).Offset(1) = project.Offset(, 3)
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

 

[haha97]

Try this macro:

Sub SplitName()
    Application.ScreenUpdating = False
    Dim LastRow As Long, project As Range, v As Variant, i As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set project = Range("C:C").Find(Range("L2"), LookIn:=xlValues, lookat:=xlWhole)
    If Not project Is Nothing Then
        v = Split(Range("G" & project.Row), "/")
        For i = LBound(v) To UBound(v)
            Cells(Rows.Count, "K").End(xlUp).Offset(1) = v(i)
            Cells(Rows.Count, "L").End(xlUp).Offset(1) = project.Offset(, 3)
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

Hi. I'm not familiar in VBA. Do I need to remove all formula in excel file and insert this VBA Code?

 

[mumps]

You don't need any formulas.
If you want to try the macro, do the following:
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro
-close the Visual Basic Editor to return to your sheet
There are easier ways to run macros such as clicking a button on your sheet or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.
Make sure that Sheet1 is the active sheet before you run the macro.

 

[haha97]

You don't need any formulas.
If you want to try the macro, do the following:
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro
-close the Visual Basic Editor to return to your sheet
There are easier ways to run macros such as clicking a button on your sheet or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.
Make sure that Sheet1 is the active sheet before you run the macro.

Thank you. It's working. But, is there a way to view the summary report in other sheet?
Means that if I want to view the table below in Sheet2 instead of Sheet1. Sorry for asking

project_tracker2.xlsx
	K	L
1	Date	4/4/2022
2	Project	A
3	Manager	Status Project
4	M1	in progress
5	M2	in progress
Sheet2

 

[mumps]

This should work regardless of which sheet is the active sheet:

Sub SplitName()
    Application.ScreenUpdating = False
    Dim project As Range, v As Variant, i As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    Set project = srcWS.Range("C:C").Find(srcWS.Range("L2"), LookIn:=xlValues, lookat:=xlWhole)
    If Not project Is Nothing Then
        v = Split(srcWS.Range("G" & project.Row), "/")
        For i = LBound(v) To UBound(v)
            With desWS
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = v(i)
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = project.Offset(, 3)
            End With
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

 

[jtakw]

Hi,

Another formula option for all Excel versions:

Book3.xlsx
	A	B	C	D	E	F	G	H
1							Date	4/6/2022
2	Date1	Project	status	manager			Project	C
3	4/4/2022	A	in progress	M1/M2			Manager	Status Project
4	4/5/2022	B	in progress	M3			M4	other
5	4/6/2022	C	other	M4/M5/M6			M5	other
6							M6	other
Sheet1065

Cell Formulas
Range	Formula
G4:G6	G4	=TRIM(MID(SUBSTITUTE(LOOKUP(2,1/SEARCH(H$2,B$3:B$5),D$3:D$5),"/",REPT(" ",100)),ROWS(G$4:G4)*100-99,100))
H4:H6	H4	=IF(G4="","",LOOKUP(2,1/SEARCH(H$2,B$3:B$5),C$3:C$5))

And a shorter version:

Book3.xlsx
	A	B	C	D	E	F	G	H
1							Date	4/6/2022
2	Date1	Project	status	manager			Project	C
3	4/4/2022	A	in progress	M1/M2			Manager	Status Project
4	4/5/2022	B	in progress	M3			M4	other
5	4/6/2022	C	other	M4/M5/M6			M5	other
6							M6	other
Sheet1065

Cell Formulas
Range	Formula
G4:G6	G4	=TRIM(MID(SUBSTITUTE(VLOOKUP(H$2,B$3:D$5,3,0),"/",REPT(" ",100)),ROWS(G$4:G4)*100-99,100))
H4:H6	H4	=IF(G4="","",VLOOKUP(H$2,B$3:C$5,2,0))

 

[haha97]

This should work regardless of which sheet is the active sheet:

Sub SplitName()
    Application.ScreenUpdating = False
    Dim project As Range, v As Variant, i As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    Set project = srcWS.Range("C:C").Find(srcWS.Range("L2"), LookIn:=xlValues, lookat:=xlWhole)
    If Not project Is Nothing Then
        v = Split(srcWS.Range("G" & project.Row), "/")
        For i = LBound(v) To UBound(v)
            With desWS
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = v(i)
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = project.Offset(, 3)
            End With
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

I just made some edit on Line no 6

    Set project = srcWS.Range("C:C").Find(desWS.Range("L2"), LookIn:=xlValues, lookat:=xlWhole)

.Also, I just realized that if you run it multiple time, the output will display according to how many you click to run the macros. Instead of displaying multiple result, can it just replace it with new result? Thank you

 

[haha97]

Hi,

Another formula option for all Excel versions:

Book3.xlsx
	A	B	C	D	E	F	G	H
1							Date	4/6/2022
2	Date1	Project	status	manager			Project	C
3	4/4/2022	A	in progress	M1/M2			Manager	Status Project
4	4/5/2022	B	in progress	M3			M4	other
5	4/6/2022	C	other	M4/M5/M6			M5	other
6							M6	other
Sheet1065

Cell Formulas
Range	Formula
G4:G6	G4	=TRIM(MID(SUBSTITUTE(LOOKUP(2,1/SEARCH(H$2,B$3:B$5),D$3:D$5),"/",REPT(" ",100)),ROWS(G$4:G4)*100-99,100))
H4:H6	H4	=IF(G4="","",LOOKUP(2,1/SEARCH(H$2,B$3:B$5),C$3:C$5))

And a shorter version:

Book3.xlsx
	A	B	C	D	E	F	G	H
1							Date	4/6/2022
2	Date1	Project	status	manager			Project	C
3	4/4/2022	A	in progress	M1/M2			Manager	Status Project
4	4/5/2022	B	in progress	M3			M4	other
5	4/6/2022	C	other	M4/M5/M6			M5	other
6							M6	other
Sheet1065

Cell Formulas
Range	Formula
G4:G6	G4	=TRIM(MID(SUBSTITUTE(VLOOKUP(H$2,B$3:D$5,3,0),"/",REPT(" ",100)),ROWS(G$4:G4)*100-99,100))
H4:H6	H4	=IF(G4="","",VLOOKUP(H$2,B$3:C$5,2,0))

Thank you. It's working. Can you explain the formula for G4 and H4. Thank you

 

[jtakw]

Thank you. It's working. Can you explain the formula for G4 and H4. Thank you

You're welcome.

I think the "Shorter version" of my formulas, at 2nd part of my post # 17, is probably more appropriate for your case.

For Manager(s):
The LOOKUP/VLOOKUP finds the Project Name (H2), in Column Range (B3:B5), returns the "Manager" from Column Range (D3:D5)
The MID and SUBSTITUTE extracts the individual "Manager" and places the results in G4:G# accordingly.

For Status:
Same as above, without the MID/SUBSTITUTE extraction.