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.

 

[Peter_SSs]

Change C2 to =IF(B2="Next", A2, CONCATENATE(C1, " ",A2))

Is there any advantage or reason to change from
=IF(B2="Next",A2,C1&" "&A2)
to the longer
=IF(B2="Next", A2, CONCATENATE(C1, " ",A2))

 

[nemmi69]

He asked about older versions of Excel which do not accept joining using &

 

[Peter_SSs]

older versions of Excel which do not accept joining using &

Joining using "&" has been acceptable as far back as I can remember. That is at least as far back as Excel 1997

 

[nemmi69]

Just giving the option

 

[Peter_SSs]

Just giving the option

That's fair enough - but quite different to the statement in post #12. And post #10 sounds like an instruction to change, not simply an option.

Anyway, it seems that the OP has a satisfactory result earlier than all that and that is the main thing.

 

[kvsrinivasamurthy]

In C2 copy down. C1='''

=IF(B2="",IF(A2="","",TRIM(C1&" "&A2)),"")

 

[tigerzen]

Thank you everyone who has contributed to this solution. Helper columns fine as they can always be hidden.