search and then textjoin certain cells

[jbarrick007]

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.

A	B	C	D	E	F	G	H	I	J
Workflow #	Task #	PWA #	Duration	Start	Finish	Phase Name	Milestone Name	Key Milestone Tasks	Goal
1.03	1.03.00	1.2	8 wks	5/26/20	7/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.01		x	x	x			• Gather Property data and add to Proposal plan
	1.03.02		x	x	x			• Prepare assumptions, questions, and materials for clients.
	1.03.03		x	x	x			• Discuss assumptions with Property Managers
	1.03.04		x	x	x			• Schedule an Investigation Meeting
	1.03.05		x	x	x			• Prepare for and hold meeting
	1.03.06		x	x	x			• Gather client remarks
	1.03.07		x	x	x			• Update draft DR-24 and POR package as needed
	1.03.08		x	x	x			• Send a Meeting Follow-up Letter
1.04	1.04.00	1.3	x	x	x		| POR ADOPTION		| POR ADOPTION • Property MGMT approves POR concept
	1.04.01		x	x	x			• Property MGMT approves POR concept
2	2.00.00		x	x	x	»Design
2.01	2.01.00	1.4	x	x	x		| CREATE PROJECT BUDGET		| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget • CNST Team finalizes Budget • Property MGMT approves Budget
	2.01.01		x	x	x			• ESTIMATOR Prepares Budget
	2.01.02		x	x	x			• CNST Team finalizes Budget
	2.01.03		x	x	x			• Property MGMT approves Budget

 

[Peter_SSs]

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
	A	B						H	I	J
1	Workflow #	Task #						Milestone Name	Key Milestone Tasks	Goal
2	1.03	1.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
3		1.03.01							• Gather Property data and add to Proposal plan
4		1.03.02							• Prepare assumptions, questions, and materials for clients.
5		1.03.03							• Discuss assumptions with Property Managers
6		1.03.04							• Schedule an Investigation Meeting
7		1.03.05							• Prepare for and hold meeting
8		1.03.06							• Gather client remarks
9		1.03.07							• Update draft DR-24 and POR package as needed
10		1.03.08							• Send a Meeting Follow-up Letter
11	1.04	1.04.00						| POR ADOPTION		| POR ADOPTION • Property MGMT approves POR concept
12		1.04.01							• Property MGMT approves POR concept
13	2	2.00.00
14	2.01	2.01.00						| CREATE PROJECT BUDGET		| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget • CNST Team finalizes Budget • Property MGMT approves Budget
15		2.01.01							• ESTIMATOR Prepares Budget
16		2.01.02							• CNST Team finalizes Budget
17		2.01.03							• Property MGMT approves Budget
Sheet1

Cell Formulas
Range	Formula
J2:J17	J2	=IF(ISNUMBER(FIND(".",A2)),TEXTJOIN(CHAR(10),1,FILTER(H2:I$1000,LEFT(B2:B$1000,LEN(A2)+1)=A2&".","")),"")

 

[jbarrick007]

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
	A	B						H	I	J
1	Workflow #	Task #						Milestone Name	Key Milestone Tasks	Goal
2	1.03	1.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
3		1.03.01							• Gather Property data and add to Proposal plan
4		1.03.02							• Prepare assumptions, questions, and materials for clients.
5		1.03.03							• Discuss assumptions with Property Managers
6		1.03.04							• Schedule an Investigation Meeting
7		1.03.05							• Prepare for and hold meeting
8		1.03.06							• Gather client remarks
9		1.03.07							• Update draft DR-24 and POR package as needed
10		1.03.08							• Send a Meeting Follow-up Letter
11	1.04	1.04.00						| POR ADOPTION		| POR ADOPTION • Property MGMT approves POR concept
12		1.04.01							• Property MGMT approves POR concept
13	2	2.00.00
14	2.01	2.01.00						| CREATE PROJECT BUDGET		| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget • CNST Team finalizes Budget • Property MGMT approves Budget
15		2.01.01							• ESTIMATOR Prepares Budget
16		2.01.02							• CNST Team finalizes Budget
17		2.01.03							• Property MGMT approves Budget
Sheet1

Cell Formulas
Range	Formula
J2:J17	J2	=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?

 

[Peter_SSs]

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?

 

[jbarrick007]

Upon further review, this one off is so different it would just be better to manually do it. Thank you for the quick reply.

 

[Peter_SSs]

OK, no problem. Thanks for letting us know.

 

[jbarrick007]

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
	A	B						H	I	J
1	Workflow #	Task #						Milestone Name	Key Milestone Tasks	Goal
2	1.03	1.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
3		1.03.01							• Gather Property data and add to Proposal plan
4		1.03.02							• Prepare assumptions, questions, and materials for clients.
5		1.03.03							• Discuss assumptions with Property Managers
6		1.03.04							• Schedule an Investigation Meeting
7		1.03.05							• Prepare for and hold meeting
8		1.03.06							• Gather client remarks
9		1.03.07							• Update draft DR-24 and POR package as needed
10		1.03.08							• Send a Meeting Follow-up Letter
11	1.04	1.04.00						| POR ADOPTION		| POR ADOPTION • Property MGMT approves POR concept
12		1.04.01							• Property MGMT approves POR concept
13	2	2.00.00
14	2.01	2.01.00						| CREATE PROJECT BUDGET		| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget • CNST Team finalizes Budget • Property MGMT approves Budget
15		2.01.01							• ESTIMATOR Prepares Budget
16		2.01.02							• CNST Team finalizes Budget
17		2.01.03							• Property MGMT approves Budget
Sheet1

Cell Formulas
Range	Formula
J2:J17	J2	=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
	A	B						H	I								Q	R
1	Workflow #	Task #						Milestone Name	Key Milestone Tasks								Role	Goal
2	1.03	1.03.00						| ESTABLISH PROJECT NEEDS									FO, 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)
3		1.03.01							• Gather Property data and add to Proposal plan								FO
4		1.03.02							• Prepare assumptions, questions, and materials for clients.								FO
5		1.03.03							• Discuss assumptions with Property Managers								FO
6		1.03.04							• Schedule an Investigation Meeting								PO
7		1.03.05							• Prepare for and hold meeting								PO
8		1.03.06							• Gather client remarks								FO
9		1.03.07							• Update draft DR-24 and POR package as needed								FO
10		1.03.08							• Send a Meeting Follow-up Letter								PO
11	1.04	1.04.00						| POR ADOPTION									PM	| POR ADOPTION • Property MGMT approves POR concept (PM)
12		1.04.01							• Property MGMT approves POR concept								PM
13	2	2.00.00
14	2.01	2.01.00						| CREATE PROJECT BUDGET									EST, CG, PO	| CREATE PROJECT BUDGET • ESTIMATOR Prepares Budget (EST) • CNST Team finalizes Budget (CG) • Property MGMT approves Budget (PO)
15		2.01.01							• ESTIMATOR Prepares Budget								EST
16		2.01.02							• CNST Team finalizes Budget								CG
17		2.01.03							• Property MGMT approves Budget								PO
Sheet1

 

[Peter_SSs]

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?