concatenate IF ISBlank Help

[KClem]

Hi

I need to concatenate some information and i am a little stuck

Based on diffiefrnt cells i need to produce the following (Bold Italic are information in the cells)

Jane is 63 and bob is 65. Jane is retired and it is unknown when this occurred.

I have attached a spreadsheet with the example.

thanks for any help :-)

[etaf]

how does the rest of the data look ?

=B1&" is "&B2&" and "&C1&" is "&C2&". "&B1&" is "&B3

[KClem]

i am sorry i should have been clearer in my question. I have got this far
=CONCATENATE(B1," was ",B2,".",IF(B3="retired"," Jane was retired, but when is ",B4))

Sometimes there is only one person, sometimes a couple. I need the sentence to deal with one person or add the second column in if there is data added

[gak67]

You need to provide more examples of your data and expected outcomes. For example, can both people in the couple be retired and do you want the information about the retirement date of the second person in the couple as well?

[KClem]

i have attached more examples.

[gak67]

Great, and what outcomes are you expecting for each of those examples?

[gak67]

For example, for the first example data do you just want: "Jane is 63 and Bob is 66. Jane is retired and it is unknown when this occurred.", or do you want: "Jane is 63 and Bob is 66. Jane is retired and it is unknown when this occurred, Bob is employed.", or something else?

For the second example do you just want: "Jane is 63 and Bob is 66. Jane is retired and it is unknown when this occurred.", or do you want: "Jane is 63 and Bob is 66. Jane & Bob are both retired and it is unknown when this occurred.", or something else?

For the third example do you want: "Jane is 63 and Bob is 66. Both are employed", or do you want: "Jane is 63 and Bob is 66. Neither are retired.", or something else?

[KClem]

if there is only Jane and she is retired = Jane was 63. Jane is retired but it is unknown when.
if there is only Bob and he is retired = Bob was 63. Bob is retired but it is unknown when.
if there is Jane & bob retired = Jane was 63 and Bob 66. Bob is retired but it is unknown when.
if there is Jane & bob and both retired = Jane was 63 and Bob 66. Jane and bob are retired but it is unknown when.

I need the sentence to tell me the age of C1 & C2 and if they are retired.

[KClem]

usually the date is unknown, i have never had one where i know what date they retired, but if there was a date in the both then it would 'they retired on ddmmyyyy'

[gak67]

Ok, it's pretty complicated, but it seems to work on the examples I have from you and some examples I have created:
=IF(B2="","",B2&" is "&B3)&IF(AND(B2<>"",C2<>"")," and ","")&IF(C2="","",C2&" is "&C3)&". "&IF(AND(B4<>"Retired",C4<>"Retired"),"",IF(AND(B4="Retired",C4="Retired"),IF(AND(OR(B5="Unknown",B5=""),OR(C5="Unknown",C5=""))," Both "&B2&" and "&C2&" are "&"retired but it is unknown when this occurred",B2&IF(OR(B5="Unknown",B5="")," is "&"retired but it is unknown when this occurred"," retired on "&TEXT(B5,"DD MMM YYYY"))&" and "&C2&IF(OR(C5="Unknown",C5="")," is "&"retired but it is unknown when this occurred"," retired on "&TEXT(C5,"DD MMM YYYY"))),IF(B4="Retired",B2&IF(OR(B5="Unknown",B5="")," is "&"retired but it is unknown when this occurred"," retired on "&TEXT(B5,"DD MMM YYYY")),C2&IF(OR(C5="Unknown",C5="")," is "&"retired but it is unknown when this occurred"," retired on "&TEXT(C5,"DD MMM YYYY"))))&".")

[KClem]

That's great, thanks