search and then textjoin certain cells

jbarrick007

New Member
Joined
Jul 9, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
OK I hate when I know something can be done but I cant figure out how. Im not even sure what keywords to search the forums for to find something similar I can try building off of.
I have a worksheet that outlines a project workflow. Each Phase, Milestone, and Task is numbered. I want to be able to textjoin all tasks for each milestone.

So to get my goal I currently I am using =TEXTJOIN(CHAR(10), TRUE,(H2:I10)) and manually selecting the cell range.

I would like to have a formula that would textjoin based off the workflow task #. While I would still have to create each formula to search for any task numbers such as 1.03.xx, I wouldn't have to worry about correcting the cell range anytime we adjust the number of tasks with each milestone. My file in all has 400 lines consisting of 7 phases with upwards of 16 milestones. Each milestone may have 1-17 tasks.

This may be easier with VBA, but I have limited experience with VBA, and thus try to stay with formulas which I better understand. But one day I need to start learning VBA.
Any suggestions are greatly appreciated.

ABCDEFGHIJ
Workflow #Task #PWA #DurationStartFinishPhase NameMilestone NameKey Milestone TasksGoal
1.031.03.001.28 wks5/26/207/18/20»Planning| ESTABLISH PROJECT NEEDS| ESTABLISH PROJECT NEEDS
• Gather Property data and add to Proposal plan
• Prepare assumptions, questions, and materials for clients.
• Discuss assumptions with Property Managers
• Schedule an Investigation Meeting
• Prepare for and hold meeting
• Gather client remarks
• Update draft DR-24 and POR package as needed
• Send a Meeting Follow-up Letter
1.03.01xxx• Gather Property data and add to Proposal plan
1.03.02xxx• Prepare assumptions, questions, and materials for clients.
1.03.03xxx• Discuss assumptions with Property Managers
1.03.04xxx• Schedule an Investigation Meeting
1.03.05xxx• Prepare for and hold meeting
1.03.06xxx• Gather client remarks
1.03.07xxx• Update draft DR-24 and POR package as needed
1.03.08xxx• Send a Meeting Follow-up Letter
1.041.04.001.3xxx| POR ADOPTION| POR ADOPTION
• Property MGMT approves POR concept
1.04.01xxx• Property MGMT approves POR concept
22.00.00xxx»Design
2.012.01.001.4xxx| CREATE PROJECT BUDGET| CREATE PROJECT BUDGET
• ESTIMATOR Prepares Budget
• CNST Team finalizes Budget
• Property MGMT approves Budget
2.01.01xxx• ESTIMATOR Prepares Budget
2.01.02xxx• CNST Team finalizes Budget
2.01.03xxx• Property MGMT approves Budget
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try this. Adjust the 1000 values if required. I was a little unsure about the logic of what to do with row 13.

jonathanbarrick 2020-08-15 1.xlsm
ABHIJ
1Workflow #Task #Milestone NameKey Milestone TasksGoal
21.031.03.00| ESTABLISH PROJECT NEEDS| ESTABLISH PROJECT NEEDS • Gather Property data and add to Proposal plan • Prepare assumptions, questions, and materials for clients. • Discuss assumptions with Property Managers • Schedule an Investigation Meeting • Prepare for and hold meeting • Gather client remarks • Update draft DR-24 and POR package as needed • Send a Meeting Follow-up Letter
31.03.01• Gather Property data and add to Proposal plan 
41.03.02• Prepare assumptions, questions, and materials for clients. 
51.03.03• Discuss assumptions with Property Managers 
61.03.04• Schedule an Investigation Meeting 
71.03.05• Prepare for and hold meeting 
81.03.06• Gather client remarks 
91.03.07• Update draft DR-24 and POR package as needed 
101.03.08• Send a Meeting Follow-up Letter 
111.041.04.00| POR ADOPTION| POR ADOPTION • Property MGMT approves POR concept
121.04.01• Property MGMT approves POR concept 
1322.00.00 
142.012.01.00| CREATE PROJECT BUDGET| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget • CNST Team finalizes Budget • Property MGMT approves Budget
152.01.01• ESTIMATOR Prepares Budget 
162.01.02• CNST Team finalizes Budget 
172.01.03• Property MGMT approves Budget 
Sheet1
Cell Formulas
RangeFormula
J2:J17J2=IF(ISNUMBER(FIND(".",A2)),TEXTJOIN(CHAR(10),1,FILTER(H2:I$1000,LEFT(B2:B$1000,LEN(A2)+1)=A2&".","")),"")
 
Upvote 0
Try this. Adjust the 1000 values if required. I was a little unsure about the logic of what to do with row 13.

jonathanbarrick 2020-08-15 1.xlsm
ABHIJ
1Workflow #Task #Milestone NameKey Milestone TasksGoal
21.031.03.00| ESTABLISH PROJECT NEEDS| ESTABLISH PROJECT NEEDS • Gather Property data and add to Proposal plan • Prepare assumptions, questions, and materials for clients. • Discuss assumptions with Property Managers • Schedule an Investigation Meeting • Prepare for and hold meeting • Gather client remarks • Update draft DR-24 and POR package as needed • Send a Meeting Follow-up Letter
31.03.01• Gather Property data and add to Proposal plan 
41.03.02• Prepare assumptions, questions, and materials for clients. 
51.03.03• Discuss assumptions with Property Managers 
61.03.04• Schedule an Investigation Meeting 
71.03.05• Prepare for and hold meeting 
81.03.06• Gather client remarks 
91.03.07• Update draft DR-24 and POR package as needed 
101.03.08• Send a Meeting Follow-up Letter 
111.041.04.00| POR ADOPTION| POR ADOPTION • Property MGMT approves POR concept
121.04.01• Property MGMT approves POR concept 
1322.00.00 
142.012.01.00| CREATE PROJECT BUDGET| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget • CNST Team finalizes Budget • Property MGMT approves Budget
152.01.01• ESTIMATOR Prepares Budget 
162.01.02• CNST Team finalizes Budget 
172.01.03• Property MGMT approves Budget 
Sheet1
Cell Formulas
RangeFormula
J2:J17J2=IF(ISNUMBER(FIND(".",A2)),TEXTJOIN(CHAR(10),1,FILTER(H2:I$1000,LEFT(B2:B$1000,LEN(A2)+1)=A2&".","")),"")
That worked great!!!

If I wanted row 13 to do the same thing, how would I change the formula to include similar lines like 13 with task # like 2.00.00 or 5.00.00?
 
Upvote 0
That worked great!!!
Great news! :)


If I wanted row 13 to do the same thing, how would I change the formula to include similar lines like 13 with task # like 2.00.00 or 5.00.00?
I didn't understand that. If row 13 did "the same thing" then that row would textjoin what is in columns H:I which is nothing. My formula replicated what you showed in column J in post 1.

Can you show what you actually expect from row 13 and explain why?
 
Upvote 0
Upon further review, this one off is so different it would just be better to manually do it. Thank you for the quick reply.
 
Upvote 0
OK, no problem. Thanks for letting us know. (y)
 
Upvote 0
Try this. Adjust the 1000 values if required. I was a little unsure about the logic of what to do with row 13.

jonathanbarrick 2020-08-15 1.xlsm
ABHIJ
1Workflow #Task #Milestone NameKey Milestone TasksGoal
21.031.03.00| ESTABLISH PROJECT NEEDS| ESTABLISH PROJECT NEEDS • Gather Property data and add to Proposal plan • Prepare assumptions, questions, and materials for clients. • Discuss assumptions with Property Managers • Schedule an Investigation Meeting • Prepare for and hold meeting • Gather client remarks • Update draft DR-24 and POR package as needed • Send a Meeting Follow-up Letter
31.03.01• Gather Property data and add to Proposal plan 
41.03.02• Prepare assumptions, questions, and materials for clients. 
51.03.03• Discuss assumptions with Property Managers 
61.03.04• Schedule an Investigation Meeting 
71.03.05• Prepare for and hold meeting 
81.03.06• Gather client remarks 
91.03.07• Update draft DR-24 and POR package as needed 
101.03.08• Send a Meeting Follow-up Letter 
111.041.04.00| POR ADOPTION| POR ADOPTION • Property MGMT approves POR concept
121.04.01• Property MGMT approves POR concept 
1322.00.00 
142.012.01.00| CREATE PROJECT BUDGET| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget • CNST Team finalizes Budget • Property MGMT approves Budget
152.01.01• ESTIMATOR Prepares Budget 
162.01.02• CNST Team finalizes Budget 
172.01.03• Property MGMT approves Budget 
Sheet1
Cell Formulas
RangeFormula
J2:J17J2=IF(ISNUMBER(FIND(".",A2)),TEXTJOIN(CHAR(10),1,FILTER(H2:I$1000,LEFT(B2:B$1000,LEN(A2)+1)=A2&".","")),"")

Peter Can you help me with one more tweak to your formula?
I wanted to add more more detail. I want to include the assigned role for each task, found in column Q. I know how to textjoin it in a simpler formula but i cant get it replicated with this one. I am getting hung up on it including for example in Q2 returning me with an undesirable result of R2. Maybe I just need to rearrange my helper columns.
| ESTABLISH PROJECT NEEDS (FO,PO)
• Gather Property data and add to Proposal plan (FO)
• Prepare assumptions, questions, and materials for clients. (FO)
• Discuss assumptions with Property Managers (FO)
• Schedule an Investigation Meeting (PO)
• Prepare for and hold meeting (PO)
• Gather client remarks (FO)
• Update draft DR-24 and POR package as needed (PO)
• Send a Meeting Follow-up Letter (PO)


Phase 7 - Project Workflow Calculator (V1.3.3).xlsx
ABHIQR
1Workflow #Task #Milestone NameKey Milestone TasksRoleGoal
21.031.03.00| ESTABLISH PROJECT NEEDSFO, PO| ESTABLISH PROJECT NEEDS • Gather Property data and add to Proposal plan (FO) • Prepare assumptions, questions, and materials for clients. (FO) • Discuss assumptions with Property Managers (FO) • Schedule an Investigation Meeting (PO) • Prepare for and hold meeting (PO) • Gather client remarks (FO) • Update draft DR-24 and POR package as needed (PO) • Send a Meeting Follow-up Letter (PO)
31.03.01• Gather Property data and add to Proposal planFO
41.03.02• Prepare assumptions, questions, and materials for clients.FO
51.03.03• Discuss assumptions with Property ManagersFO
61.03.04• Schedule an Investigation MeetingPO
71.03.05• Prepare for and hold meetingPO
81.03.06• Gather client remarksFO
91.03.07• Update draft DR-24 and POR package as neededFO
101.03.08• Send a Meeting Follow-up LetterPO
111.041.04.00| POR ADOPTIONPM| POR ADOPTION • Property MGMT approves POR concept (PM)
121.04.01• Property MGMT approves POR conceptPM
1322.00.00
142.012.01.00| CREATE PROJECT BUDGETEST, CG, PO| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget (EST) • CNST Team finalizes Budget (CG) • Property MGMT approves Budget (PO)
152.01.01• ESTIMATOR Prepares BudgetEST
162.01.02• CNST Team finalizes BudgetCG
172.01.03• Property MGMT approves BudgetPO
Sheet1
 
Upvote 0
I am getting hung up on it including for example in Q2 returning me with an undesirable result of R2.
If R2 is an undesirable result, then what is the desirable result?

If R2 is the desirable result then what formula did you use that resulted in an undesirable result?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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