TextJoin Function

[Kathryn_Marie]

Hi All,
I need to create a folder path from a raw data source which will update often. The data is on this format:

Material (SAP L.1-5 Functional Process Hierarchy)
	_Simulation Guide
	_eLearning Guide
		_Title Page
		_Audience and Prerequisites
		_Housekeeping
		_Topics
		Topic 1 - <Title>
			_Topic
			_Objectives
			_Introduction
			_Simulation - <Title>
		_Conclusion

I have used this formula which almost works, but it keeps adding previous entries from the same column which I do not want. Wondering is anyone may know how I can use this formula till it finds an entry, then starts again, or if there is another way to do this. This is what I have which gives the result below:

=TEXTJOIN("/", TRUE, E$2:E2, F$2:F2, G$2:G2, H$2:H2, I$2:I2, J$2:J2)

Content

Content/Material (SAP L.1-5 Functional Process Hierarchy)

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic/_Objectives

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic/_Objectives/_Introduction

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Topic/_Objectives/_Introduction/_Simulation - <Title>

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Conclusion/_Topic/_Objectives/_Introduction/_Simulation - <Title>

Content/Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide/_eLearning Guide/_Quiz Guide/_Title Page/_Audience and Prerequisites/_Housekeeping/_Topics/Topic 1 - <Title>/_Conclusion/_Topic/_Objectives/_Introduction/_Simulation - <Title>

 

[Ron Coderre]

You'd need to keep referencing the last value in each column.
Try something like this ugly regular formula:
=TEXTJOIN("\",TRUE,IFERROR(LOOKUP("ZZZZZZ",D$2:D2),""),IFERROR(LOOKUP("ZZZZZZ",E$2:E2),"")
,IFERROR(LOOKUP("ZZZZZZ",F$2:F2),""),IFERROR(LOOKUP("ZZZZZZ",G$2:G2),""),IFERROR(LOOKUP("ZZZZZZ",H$2:H2),""))

 

[Kathryn_Marie]

Might be ugly, but it works perfectly! Thanks

 

[Kathryn_Marie]

Not quite Ron, from the last 3 rows in my example, the results I get are this:

Content\Material (SAP L.1-5 Functional Process Hierarchy)\_eLearning Guide\Topic 1 - <Title>\_Simulation - <Title>

Content\Material (SAP L.1-5 Functional Process Hierarchy)\_eLearning Guide\_Conclusion\_Simulation - <Title>

Content\Material (SAP L.1-5 Functional Process Hierarchy)\_Quiz Guide\_Conclusion\_Simulation - <Title>

So it is going forwards perfectly, but when the folder structure is one less, it continues to put in the values from the next columns from previous :/

 

[Ron Coderre]

OK...Let's see if this works. It requires some of the latest updates to O365 (which you indicate you are using):
(Side note: Building this was great fun)
I2: =TEXTJOIN("|",TRUE,CHOOSE(SEQUENCE(1,XLOOKUP("?*",D2:H2,{1,2,3,4,5},0,2,-1)),CONCAT(D$2:D2),CONCAT(E$2:E2),CONCAT(F$2:F2),CONCAT(G$2:G2),CONCAT(H$2:H2)))

(Edited to shorten it a bit)

Copy I2 down through I15.

Is that something you can work with?

 

[Kathryn_Marie]

It still seems to be giving duplicates of the items above it :/

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)/_Title Page_Audience and Prerequisites_Housekeeping_TopicsTopic 1 - <Title>_ConclusionApplication Basics (AB)

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)/_Title Page_Audience and Prerequisites_Housekeeping_TopicsTopic 1 - <Title>_ConclusionApplication Basics (AB)Basis Components (BC)

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)/_Title Page_Audience and Prerequisites_Housekeeping_TopicsTopic 1 - <Title>_ConclusionApplication Basics (AB)Basis Components (BC)Business Warehouse (BW)

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)/_Title Page_Audience and Prerequisites_Housekeeping_TopicsTopic 1 - <Title>_ConclusionApplication Basics (AB)Basis Components (BC)Business Warehouse (BW)Environment, Health & Safety (EHS)

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)/_Title Page_Audience and Prerequisites_Housekeeping_TopicsTopic 1 - <Title>_ConclusionApplication Basics (AB)Basis Components (BC)Business Warehouse (BW)Environment, Health & Safety (EHS)Governance, Risk & Compliance (GRC)

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)/_Title Page_Audience and Prerequisites_Housekeeping_TopicsTopic 1 - <Title>_ConclusionApplication Basics (AB)Basis Components (BC)Business Warehouse (BW)Environment, Health & Safety (EHS)Governance, Risk & Compliance (GRC)Knowledge Management (KM)

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide_eLearning Guide_Quiz GuideCross Functional Elements (CFE)Customer Relationship Management (CRM)

 

[Akuini]

Hi, Kathryn_Marie. Welcome to the Forum.

This is what I have which gives the result below:

=TEXTJOIN("/", TRUE, E$2:E2, F$2:F2, G$2:G2, H$2:H2, I$2:I2, J$2:J2)

It doesn't clear to me.
The second table in post #1, is that the result that you want or the result when you use your formula?
If it is the result when you use your formula, then can you show us the result that you want?

 

[Kathryn_Marie]

That's the result I am getting, the result I want would be:

Material (SAP L.1-5 Functional Process Hierarchy)/_Simulation Guide
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Title Page
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Audience and Prerequisites
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Housekeeping
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Topics
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Topic
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Objectives
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Introduction
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/Topic 1 - <Title>/_Simulation - <Title>
Material (SAP L.1-5 Functional Process Hierarchy)/_eLearning Guide/_Conclusion

From this:

Material (SAP L.1-5 Functional Process Hierarchy)
	_Simulation Guide
	_eLearning Guide
		_Title Page
		_Audience and Prerequisites
		_Housekeeping
		_Topics
		Topic 1 - <Title>
			_Topic
			_Objectives
			_Introduction
			_Simulation - <Title>
		_Conclusion

 

[Akuini]

Try this code. It will insert the blank cells with correct data. After that you can easily use TextJoin formula to get the result.

Sub a1122849a()
'https://www.mrexcel.com/board/threads/textjoin-function.1122849
Dim a As Long, b As Long
a = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
b = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    With Range(Cells(2, "A"), Cells(a, b))
        .SpecialCells(xlBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",R[-1]C,"""")"
       .Value = .Value
    End With

End Sub

EXAMPLE:

Book1
	A	B	C	D
1	test
2		_Simulation Guide
3		_eLearning Guide
4			_Title Page
5			_Audience and Prerequisites
6			_Housekeeping
7			_Topics
8			Topic 1 - <Title>
9				_Topic
10				_Objectives
11				_Introduction
12				_Simulation - <Title>
13			_Conclusion
14		xx
15			yy
16				zz
Sheet8

RESULT:

Book1
	A	B	C	D
1	test
2	test	_Simulation Guide
3	test	_eLearning Guide
4	test	_eLearning Guide	_Title Page
5	test	_eLearning Guide	_Audience and Prerequisites
6	test	_eLearning Guide	_Housekeeping
7	test	_eLearning Guide	_Topics
8	test	_eLearning Guide	Topic 1 - <Title>
9	test	_eLearning Guide	Topic 1 - <Title>	_Topic
10	test	_eLearning Guide	Topic 1 - <Title>	_Objectives
11	test	_eLearning Guide	Topic 1 - <Title>	_Introduction
12	test	_eLearning Guide	Topic 1 - <Title>	_Simulation - <Title>
13	test	_eLearning Guide	_Conclusion
14	test	xx
15	test	xx	yy
16	test	xx	yy	zz
Sheet9

 

[Kathryn_Marie]

That worked well thanks. I had to remove zeros in the cells where there was no column name, and if I put in a column name it duplicated that instead of leaving blank cells, but that's no biggie