Excel Do While cell text is true -

Excel Do While cell text is true -

i have spreadsheet set so:

col ----- text 1 mccartney, paul lennon, john harrison, george starr, ringo text 2 lee, geddy lifeson, alex peart, neil

in column b want add together text long haven't reached 'text 2' yet. instance, info might like:

col col b ----- ----- text 1 mccartney, paul beatles lennon, john beatles harrison, george beatles starr, ringo beatles text 2 lee, geddy rush lifeson, alex rush peart, neil rush

i tried several combinations of 'do while' , 'if ... then... end if', nil working. how write code?

i have 3 'title' rows in column a: open, gain, , limited. names static. number of rows between them going different. info between rows peoples names in format 'last, first'. i'm trying add together column @ end says 'open' when row between 'open' row , 'gain' row. want set word 'gain' in new column between 'gain' , 'limited' rows. there downwards new column 'limited'. hope makes sense. – mr_thomas 3 hours ago

is trying?

put formula in cell b1 , re-create down

=if(isna(index(match("limited",$a$1:$a1,0 ),1,1)),if(isna(index(match("gain",$a$1:$a1,0 ),1,1)),if(isna(index(match("open",$a$1:$a1,0 ),1,1)),"","open"),"gain"),"limited")

screenshot

excel excel-vba