Combine cells conditionally

[tigerzen]

Book4
	A	B	C
2	Tom		Tom
3	Jones		Jones Tom
4	Francis	Next
5	Albert		Albert
6	John		John Albert
7	Sinatra		Sinatra John Albert
8	Peter	Next
9	Steven		Steven
10	Townsend		Townsend Steven
11	Michael	Next
12	V		V
13	Jackson		Jackson V
14
15			Desired
16	Tom
17	Jones		Tom Jones
18	Francis	Next
19	Albert
20	John
21	Sinatra		Francis Albert John Sinatra
22	Peter	Next
23	Steven
24	Townsend		Peter Steven Townsend
25	Michael	Next
26	V
27	Jackson		Michael V Jackson
Sheet3

Cell Formulas
Range	Formula
C2:C13	C2	=IF(B2="",A2&" "&C1,"")

Data is received as per attached information. Column C indicates what needs to happen, I have attempted a solution which doesn't work, it is incomplete and returns the name in the wrong order. Looking for a formula here that will do the job, please note that I'm assisting someone who only has Excel 2016 thus no Textjoin function which may or may not impact on the solution.

 

[nemmi69]

Do you mean this?

Note: C1 formula would be =If(B1="",A1,"")

 

[tigerzen]

Hi Nemmi69, that is close but it needs to be in the desired format in my first post and it must contain the first entry eg Francis Albert John Sinatra rather than just Albert John Sinatra. Also the Albert, Albert John should not be part of the final output although I suppose they can be filtered out at the end.

 

[nemmi69]

 

[Peter_SSs]

See if this does what you want.

23 05 03.xlsm
	A	B	C
1	Tom
2	Jones		Tom Jones
3	Francis	Next
4	Albert
5	John
6	Sinatra		Francis Albert John Sinatra
7	Peter	Next
8	Steven
9	Townsend		Peter Steven Townsend
10	Michael	Next
11	V
12	Jackson		Michael V Jackson
Combine names

Cell Formulas
Range	Formula
C1:C12	C1	=IF(OR(B2="Next",A2=""),TEXTJOIN(" ",,XLOOKUP("Next",B$1:B1,A$1:A1,A$1,,-1):A1),"")

@nemmi69
I think that you may be missing this part of the original post?

 

[tigerzen]

Hi Peter, that is indeed the solution however I need to come up with a formula that will work on an older version of Excel.

 

[Peter_SSs]

Older version do not lend themselves to text joining functions very well. Perhaps you could use what I have in column C or columns C & D below.
If you want the results shown in column D but without the helper column then I think that you will need to use vba. If that would be better for you then post back to say so.

23 05 03.xlsm
	A	B	C	D
1	Tom		Tom
2	Jones		Tom Jones	Tom Jones
3	Francis	Next	Francis
4	Albert		Francis Albert
5	John		Francis Albert John
6	Sinatra		Francis Albert John Sinatra	Francis Albert John Sinatra
7	Peter	Next	Peter
8	Steven		Peter Steven
9	Townsend		Peter Steven Townsend	Peter Steven Townsend
10	Michael	Next	Michael
11	V		Michael V
12	Jackson		Michael V Jackson	Michael V Jackson
13
Combine names (2)

Cell Formulas
Range	Formula
C1	C1	=A1
D1:D12	D1	=IF(OR(B2="Next",A2=""),C1,"")
C2:C12	C2	=IF(B2="Next",A2,C1&" "&A2)

 

[nemmi69]

you can use CONCATENATE back to 2007 (Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010)

 

[Peter_SSs]

you can use CONCATENATE back to 2007 (Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010)

Your latest formula still does not produce the results that the OP has listed under "Desired" in post #1

 

[nemmi69]

So from Peter SSs formula

Change C2 to =IF(B2="Next", A2, CONCATENATE(C1, " ",A2)). Then drag down