If formula with many different outcomes and also to combine names for a mail merge

HI,

I have recently updated out company database to include insurance details and mailing details for customers. Database was the easy part it seems now I have to get a report to run off it which works perfect I just cant figure out an easy way for it to filter out deceased parties and create a name mail merge as required.

IE I have Vendor1 & Vendor 2 (Column A & Column B) both details on the system , I also have dates deceased ( Column S & Column T) So there are various outcomes to this scenario ... ONe Vendor , Two Vendors , One Alive , Two Alive , Both deceased, so that would be one part , once we work out that side then we have to concantenate the name to include "&" as necessary for a mail merge.

I always come here when stuck and hopefully someone can just look at and go EASY fella this is the best method for that.

Dont want to over complicate with nested If statements if possible want it to be simple enough for someone else to read the formulas so can be broken down in to steps if need be.

So really how I see it (could be wrong) We have a formula that compares the name against the date of death .. if alive copy their name to another cell , if deceased leave blank..... then a formula that works out If person 1 deceased then person 2 becomes main contact and not include and ampersand ,if both deceased flag it as a terminated contract.

Hopefully that makes sense... if you have any questions let me know.

Would be much appreciated. There are other factors I have to take in to account but this is the one that has me a bit puzzled as my formula skills are patchy Ive realised.


many thanks

Hi ,

Appreciate any input I have pulled a sample in to a fresh spreadsheet .... links back to the Main register so have pasted the actual formula in the top 3 rows and pasted the values in a couple of rows down in case you just get #REF# instead of the data....

Columns V onwards is just me playing around.... my thinking in simple terms is ....breaking it down before combining ... compare names to date of death , then have a column with and & if there is more than one vendor after deceased party is removed (ie both alive) ... hopefully Im making sense... sounded simple when I though of it but just have a brain fart on best way of doing it !! thanks

Sample Insurance Register.xls

Almost there but something still has me confused IF statement returning the wrong value is my syntax out or ;

=IF(C1="U","",C1)- if it equals U then replace with blank - but it returns result as false when it should be true.
but if I do this ;



=IF(AND(C1>"T",C1<"V"),"",C1) this returns the correct result but does the same job as formula above does it not.