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

H

haha97

New Member
Joined
Mar 31, 2022
Messages
17
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
ACFGHIJKL
5Date4/4/2022
6Date1ProjectstatusmanagerHelperProjectA
74/4/2022Ain progressM1/M244655AM1/M2ManagerStatus Project
8M1-
9M2-
Sheet1
Cell Formulas
RangeFormula
H7H7=A7&C7&G7
L8:L9L8=IFNA(INDEX(F:F,MATCH(L$5&L$6&K8,H:H,0)),"-")
Cells with Data Validation
CellAllowCriteria
L6Any value


Desired output:
project_tracker.xlsx
ACFGHIJKL
5Date4/4/2022
6Date1ProjectstatusmanagerHelperProjectA
74/4/2022Ain progressM1/M244655AM1/M2ManagerStatus Project
8M1in progress
9M2in progress
Sheet1
Cell Formulas
RangeFormula
H7H7=A7&C7&G7
Cells with Data Validation
CellAllowCriteria
L6Any value
 
Sort by date Sort by votes
Fluff said:
According to your profile you have 365 so why not use that?
Click to expand...
Actually, this is for my office work and it is only supported Microsoft 2019. Thus, I cannot use other version.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try this macro:
VBA Code: 
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
 
Upvote 0
mumps said:
Try this macro:
VBA Code: 
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
Click to expand...
Hi. I'm not familiar in VBA. Do I need to remove all formula in excel file and insert this VBA Code?
 
Upvote 0
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.
 
Upvote 0
mumps said:
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.
Click to expand...
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
KL
1Date4/4/2022
2ProjectA
3ManagerStatus Project
4M1in progress
5M2in progress
Sheet2
 
Upvote 0
This should work regardless of which sheet is the active sheet:
VBA Code: 
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
 
Upvote 0
Hi,

Another formula option for all Excel versions:

Book3.xlsx
ABCDEFGH
1Date4/6/2022
2Date1ProjectstatusmanagerProjectC
34/4/2022Ain progressM1/M2ManagerStatus Project
44/5/2022Bin progressM3M4other
54/6/2022CotherM4/M5/M6M5other
6M6other
Sheet1065
Cell Formulas
RangeFormula
G4:G6G4=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:H6H4=IF(G4="","",LOOKUP(2,1/SEARCH(H$2,B$3:B$5),C$3:C$5))


And a shorter version:

Book3.xlsx
ABCDEFGH
1Date4/6/2022
2Date1ProjectstatusmanagerProjectC
34/4/2022Ain progressM1/M2ManagerStatus Project
44/5/2022Bin progressM3M4other
54/6/2022CotherM4/M5/M6M5other
6M6other
Sheet1065
Cell Formulas
RangeFormula
G4:G6G4=TRIM(MID(SUBSTITUTE(VLOOKUP(H$2,B$3:D$5,3,0),"/",REPT(" ",100)),ROWS(G$4:G4)*100-99,100))
H4:H6H4=IF(G4="","",VLOOKUP(H$2,B$3:C$5,2,0))
 
Upvote 0
mumps said:
This should work regardless of which sheet is the active sheet:
VBA Code: 
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
Click to expand...
I just made some edit on Line no 6
VBA Code: 
    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
 
Upvote 0
jtakw said:
Hi,

Another formula option for all Excel versions:

Book3.xlsx
ABCDEFGH
1Date4/6/2022
2Date1ProjectstatusmanagerProjectC
34/4/2022Ain progressM1/M2ManagerStatus Project
44/5/2022Bin progressM3M4other
54/6/2022CotherM4/M5/M6M5other
6M6other
Sheet1065
Cell Formulas
RangeFormula
G4:G6G4=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:H6H4=IF(G4="","",LOOKUP(2,1/SEARCH(H$2,B$3:B$5),C$3:C$5))


And a shorter version:

Book3.xlsx
ABCDEFGH
1Date4/6/2022
2Date1ProjectstatusmanagerProjectC
34/4/2022Ain progressM1/M2ManagerStatus Project
44/5/2022Bin progressM3M4other
54/6/2022CotherM4/M5/M6M5other
6M6other
Sheet1065
Cell Formulas
RangeFormula
G4:G6G4=TRIM(MID(SUBSTITUTE(VLOOKUP(H$2,B$3:D$5,3,0),"/",REPT(" ",100)),ROWS(G$4:G4)*100-99,100))
H4:H6H4=IF(G4="","",VLOOKUP(H$2,B$3:C$5,2,0))
Click to expand...
Thank you. It's working. Can you explain the formula for G4 and H4. Thank you
 
Upvote 0
haha97 said:
Thank you. It's working. Can you explain the formula for G4 and H4. Thank you
Click to expand...

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.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
0
Views
63
mellojeff
M
J
  • Locked
  • Question
Excel Formula
Replies
1
Views
241
Joe4
Joe4
I
  • Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
152
StephenCrump
S
C
  • Solved
Index match function
Replies
3
Views
476
Alex Blakenburg
A
G
  • Question
Sum a range of a nested index match
Replies
3
Views
605
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,493
Messages
6,125,128
Members
449,206
Latest member
burgsrus

Share this page

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
Back
Top