CONCAT formula to generate 3 outcomes depending on data in a specific column

[timjo]

Hi Forum Members,

I have a table that has a variety of data, and I want to leverage that data to generate folder names. I plan to use the CONCAT formula, and I have the structure sorted with the formulas in the Folder Names sheet. But I need help to finalise the formula as there are 3 possible outcomes depending on the data in column E in the Schedule Tracker sheet. I have left 2 columns without for privacy reasons.

Active Tasks.xlsx
	A	B	C	D	E
1	ID	Job #	Building	Supervisor	Task Name
2		24481	St Helens Community Centre		Height Safety System - 12 Months
3		24482	St James Park Tennis Courts and Clubhouse		Height Safety System - 12 Months
4		24483	Public Toilets - Watson Road		Height Safety System - 12 Months
5		24484	Town Hall House		Height Safety System - 12 Months
6		24485	The Crescent Early Learning Centre		Height Safety System - 12 Months
7		24486	Redfern Community Centre		Height Safety System - 12 Months
8		24487	Public Toilets and Garden Shed -Bourke Street Park		Height Safety System - 12 Months
9		24488	Public Toilet - 13A Refinery Drive		Height Safety System - 12 Months
10		24598	Huntley Street Early Learning Centre		Height Safety System - 12 Months
11		23866	Erskineville Town Hall		Height Safety System - 12 Months
12		24454	Erskineville Arts Centre		Height Safety System - 12 Months
13		24455	Kiosk and Exeloo at Joynton Park - Zetland		Height Safety System - 12 Months
14		24456	East Sydney Community and Arts Centre (ESCAC)		Height Safety System - 12 Months
15		24457	Perry Park Recreation Centre		Height Safety System - 12 Months
16		24458	Darlington Activity Centre		Height Safety System - 12 Months
17		24459	Hilda Booler Child Care Centre		Height Safety System - 12 Months
18		24460	Nurses Quarters		Height Safety System - 12 Months
19		24461	Andrew Boy Charlton Pool		Height Safety System - 12 Months
20		24462	Cafe - Hyde Park South		Height Safety System - 12 Months
21		24463	410-416 Sydney Park Rd - Cycling Cnt - Sydney Park		Height Safety System - 12 Months
22		24464	Broughton Street Kindergarten		Height Safety System - 12 Months
23		24572	Depot - Cumberland Street		Height Safety System - 12 Months
24		24573	Breezeway 8/9 Passageway with overhead roof		Height Safety System - 12 Months
25		24481	City Recital Hall		Height Safety System - 12 Months
26		24482	101-111 William Street		Height Safety System - 12 Months
27		24483	Commonwealth Bank Building		Height Safety System - 12 Months
28		24484	343 George Street		Height Safety System - 12 Months
29		24485	Goulburn Street Parking Station		Height Safety System - 12 Months
30		24486	Phillip Park Community & Childrens Centre		Height Safety System - 12 Months
31		24487	Known as Jane Evans Day Centre		Height Safety System - 12 Months
32		24488	Known as Jane Evans Day Centre		Height Safety System - 12 Months
33		24598	Coronation Hotel		Height Safety System - 12 Months
34		23866	King George V Recreation Centre		Height Safety System - 12 Months
35		24454	Juanita Nielsen Community Centre		Height Safety System - 12 Months
36		24455	Gunyama Park Aquatics and Recreation Centre		Height Safety System - 12 Months
37		24456	Joynton Avenue Creative Centre (JACC)		Gutters and Downpipes - 12M
38		24457	Sydney Town Hall		Gutters and Downpipes - EWP Required - 12M
39		24458	Haymarket Library		Gutters and Downpipes - 12M
40		24459	Paddington Town Hall		Gutters and Downpipes - 3M
41		24460	Tabernacle		Gutters and Downpipes - 12M
42		24461	119 Redfern Street		Gutters and Downpipes - 12M
43		24462	Joynton Avenue Creative Centre (JACC)		Drains - 12M
44		24463	Sydney Town Hall		Drains - EWP Required - 12M
45		24464	Haymarket Library		Drains - 12M
46		24572	Paddington Town Hall		Drains - 6M
47		24573	Ian Thorpe Aquatic Centre		Drains - EWP Required - 3M
48		24481	Tabernacle		Drains - 12M
49		24482	119 Redfern Street		Drains - 12M
50		24483	St James Cafe		Height Safety System - 12 Months
51		24484	Kings Cross Neighbourhood Service Centre & Library		Gutters and Downpipes - AWNING ONLY - 1M
52		24485	Kings Cross Neighbourhood Service Centre & Library		Gutters and Downpipes - AWNING ONLY - 1M
53		24486	Kings Cross Neighbourhood Service Centre & Library		Drains - AWNING ONLY - 1M
54		24487	Kings Cross Neighbourhood Service Centre & Library		Drains - AWNING ONLY - 1M
55		24488	171-203 Euston Rd - Public Toilet - Sydney Park		Height Safety System - 12 Months
56		24598	Doody Street Industrial Complex		Priority 4-Roofing & Skylights
57		23866	107 Redfern Street		Priority 4-Interior Fixtures & Fittings Maintenance
58		24454	Green Square Library		Priority 4-External-Fittings & Equipment
59		24455	PACT Theatre		Priority 4-Fall Arrest / Height Safety Equipment
60		24456	Paddington Town Hall		Priority 4-External-Fittings & Equipment
61		24457	Tabernacle		Priority 4-Fall Arrest / Height Safety Equipment
62		24458	Huntley Street Early Learning Centre		Priority 4-Fall Arrest / Height Safety Equipment
Schedule Tracker

Below is a list of variations, along with the data triggers.

• Schedule Tacker column E - First word "Height"
  

  • =CONCAT(Schedule_Tracker[@[Job '#]], " - HSR Annual Inspection - ", Schedule_Tracker[@Building])

  • Example: 24481 - HSR Annual Inspection - St Helens Community Centre

• Schedule Tacker column E - First word "Gutters"
  

  • =CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building])

  • Example: 24458 - RA Gutters and Downpipes - 12M - Haymarket Library

• Schedule Tacker column E - First word "Drains"
  

  • =CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building])

  • Example: 24464 - RA Drains - 12M - Haymarket Library

Below is a copy of my Folder Names sheet.

Active Tasks.xlsx
	A
1	Folder Names
2	24481 - HSR Annual Inspection - St Helens Community Centre
3	24482 - HSR Annual Inspection - St James Park Tennis Courts and Clubhouse
4	24483 - HSR Annual Inspection - Public Toilets - Watson Road
5	24484 - HSR Annual Inspection - Town Hall House
6	24485 - HSR Annual Inspection - The Crescent Early Learning Centre
7	24486 - HSR Annual Inspection - Redfern Community Centre
8	24487 - HSR Annual Inspection - Public Toilets and Garden Shed -Bourke Street Park
9	24488 - HSR Annual Inspection - Public Toilet - 13A Refinery Drive
10	24598 - HSR Annual Inspection - Huntley Street Early Learning Centre
11	23866 - HSR Annual Inspection - Erskineville Town Hall
12	24454 - HSR Annual Inspection - Erskineville Arts Centre
13	24455 - HSR Annual Inspection - Kiosk and Exeloo at Joynton Park - Zetland
14	24456 - HSR Annual Inspection - East Sydney Community and Arts Centre (ESCAC)
15	24457 - HSR Annual Inspection - Perry Park Recreation Centre
16	24458 - HSR Annual Inspection - Darlington Activity Centre
17	24459 - HSR Annual Inspection - Hilda Booler Child Care Centre
18	24460 - HSR Annual Inspection - Nurses Quarters
19	24461 - HSR Annual Inspection - Andrew Boy Charlton Pool
20	24462 - HSR Annual Inspection - Cafe - Hyde Park South
21	24463 - HSR Annual Inspection - 410-416 Sydney Park Rd - Cycling Cnt - Sydney Park
22	24464 - HSR Annual Inspection - Broughton Street Kindergarten
23	24572 - HSR Annual Inspection - Depot - Cumberland Street
24	24573 - HSR Annual Inspection - Breezeway 8/9 Passageway with overhead roof
25	24481 - HSR Annual Inspection - City Recital Hall
26	24482 - HSR Annual Inspection - 101-111 William Street
27	24483 - HSR Annual Inspection - Commonwealth Bank Building
28	24484 - HSR Annual Inspection - 343 George Street
29	24485 - HSR Annual Inspection - Goulburn Street Parking Station
30	24486 - HSR Annual Inspection - Phillip Park Community & Childrens Centre
31	24487 - HSR Annual Inspection - Known as Jane Evans Day Centre
32	24488 - HSR Annual Inspection - Known as Jane Evans Day Centre
33	24598 - HSR Annual Inspection - Coronation Hotel
34	23866 - HSR Annual Inspection - King George V Recreation Centre
35	24454 - HSR Annual Inspection - Juanita Nielsen Community Centre
36	24455 - HSR Annual Inspection - Gunyama Park Aquatics and Recreation Centre
37	24456 - HSR Annual Inspection - Joynton Avenue Creative Centre (JACC)
38	24457 - HSR Annual Inspection - Sydney Town Hall
39	24458 - RA Gutters and Downpipes - 12M - Haymarket Library
40	24459 - HSR Annual Inspection - Paddington Town Hall
41	24460 - HSR Annual Inspection - Tabernacle
42	24461 - HSR Annual Inspection - 119 Redfern Street
43	24462 - HSR Annual Inspection - Joynton Avenue Creative Centre (JACC)
44	24463 - HSR Annual Inspection - Sydney Town Hall
45	24464 - RA Drains - 12M - Haymarket Library
46	24572 - HSR Annual Inspection - Paddington Town Hall
47	24573 - HSR Annual Inspection - Ian Thorpe Aquatic Centre
48	24481 - HSR Annual Inspection - Tabernacle
49	24482 - HSR Annual Inspection - 119 Redfern Street
50	24483 - HSR Annual Inspection - St James Cafe
51	24484 - HSR Annual Inspection - Kings Cross Neighbourhood Service Centre & Library
52	24485 - HSR Annual Inspection - Kings Cross Neighbourhood Service Centre & Library
53	24486 - HSR Annual Inspection - Kings Cross Neighbourhood Service Centre & Library
54	24487 - HSR Annual Inspection - Kings Cross Neighbourhood Service Centre & Library
55	24488 - HSR Annual Inspection - 171-203 Euston Rd - Public Toilet - Sydney Park
56	24598 - HSR Annual Inspection - Doody Street Industrial Complex
57	23866 - HSR Annual Inspection - 107 Redfern Street
58	24454 - HSR Annual Inspection - Green Square Library
59	24455 - HSR Annual Inspection - PACT Theatre
60	24456 - HSR Annual Inspection - Paddington Town Hall
61	24457 - HSR Annual Inspection - Tabernacle
62	24458 - HSR Annual Inspection - Huntley Street Early Learning Centre
Folder Names

Cell Formulas
Range	Formula
A2:A38,A40:A44,A46:A62	A2	=CONCAT(Schedule_Tracker[@[Job '#]], " - HSR Annual Inspection - ", Schedule_Tracker[@Building])
A39,A45	A39	=CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name]], " - ", Schedule_Tracker[@Building])

Any assistance with this formula will be greatly appreciated.

Many thanks,
Tim

 

[myall_blues]

For 365 (and I think 2021), and assuming there are not too many (if there are then an alternative to a nested IF would be recommended);

=IF(TEXTBEFORE(E2," ",1)="Height",CONCAT(Schedule_Tracker[@[Job '#]], " - HSR Annual Inspection - ", Schedule_Tracker[@Building]),IF(TEXTBEFORE(E2," ",1)="Gutters",=CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building]),IF(TEXTBEFORE(E2," ",1)="Drains",=CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building]),"Not found")))

 

[timjo]

For 365 (and I think 2021), and assuming there are not too many (if there are then an alternative to a nested IF would be recommended);

=IF(TEXTBEFORE(E2," ",1)="Height",CONCAT(Schedule_Tracker[@[Job '#]], " - HSR Annual Inspection - ", Schedule_Tracker[@Building]),IF(TEXTBEFORE(E2," ",1)="Gutters",=CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building]),IF(TEXTBEFORE(E2," ",1)="Drains",=CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building]),"Not found")))

I entered the formula and substituted E2 for the correct reference on the Schedule Tracker sheet. However, it didn't recognise the formula.

=IF(TEXTBEFORE(Schedule_Tracker[@[Task Name]]," ",1)="Height",CONCAT(Schedule_Tracker[@[Job '#]], " - HSR Annual Inspection - ", Schedule_Tracker[@Building]), IF(TEXTBEFORE(Schedule_Tracker[@[Task Name]]," ",1)="Gutters",'Folder Name=CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building])), IF(TEXTBEFORE(Schedule_Tracker[@[Task Name]]," ",1)="Drains",=CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name], " - ", Schedule_Tracker[@Building]),"Not found")))

Any suggestions?

 

[myall_blues]

A few misplaced brackets and equals signs. Try this:

=IF(TEXTBEFORE(Schedule_Tracker[@[Task Name]]," ",1)="Height",CONCAT(Schedule_Tracker[@[Job '#]], " - HSR Annual Inspection - ", Schedule_Tracker[@Building]),IF(TEXTBEFORE(E2," ",1)="Gutters",CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name]], " - ", Schedule_Tracker[@Building]),IF(TEXTBEFORE(E2," ",1)="Drains",CONCAT(Schedule_Tracker[@[Job '#]], " - RA ", Schedule_Tracker[@[Task Name]], " - ", Schedule_Tracker[@Building]),"Not found")))

 

[myall_blues]

As an afterthought, this formula can be shortened to:

=LET(_tb,TEXTBEFORE(Schedule_Tracker[@[Task Name]]," ",1),_tex1,Schedule_Tracker[@[Job '#]],IF(_tb="Height",CONCAT(_tex1, " - HSR Annual Inspection - ", Schedule_Tracker[@Building]),IF(_tb="Gutters",CONCAT(_tex1, " - RA ", Schedule_Tracker[@[Task Name]], " - ", Schedule_Tracker[@Building]),IF(_tb="Drains",CONCAT(_tex1, " - RA ", Schedule_Tracker[@[Task Name]], " - ", Schedule_Tracker[@Building]),"Not found"))))

and further if the basic form for gutters and drains is meant to be the same (as it appears here) then it can be further shortened to:

=LET(_tb,TEXTBEFORE(Schedule_Tracker[@[Task Name]]," ",1),_tex1,Schedule_Tracker[@[Job '#]],IF(_tb="Height",CONCAT(_tex1, " - HSR Annual Inspection - ", Schedule_Tracker[@Building]),IF(OR(_tb="Gutters",_tb="Drains"),CONCAT(_tex1, " - RA ", Schedule_Tracker[@[Task Name]], " - ", Schedule_Tracker[@Building]),"Not found")))

 

[timjo]

Works a treat. Thansk @myall_blues

 

[Peter_SSs]

Another suggestion

1. Make another column (called Folder) in the table with the shorter formula
   =LET(n,IFNA(MATCH(TEXTBEFORE([@[Task Name]]," "),{"Height","Gutters","Drains"},0),0),IF(n=0,"Not found",[@[Job '#]]&IF(n=1," - HSR Annual Inspection - "," - RA "&[@[Task Name]]&" - ")&[@Building]))
   
   
2. If you still need the folders on the 'Folder Name' sheet just use the formula
   =Schedule_Tracker[Folder]

If you don't want the folder name showing on the 'Schedule Tracker' sheet then hide that column.