Merging Individual and Spouse Name for Mailing

[Xela_Cad]

Hello,

Here's my dilemma. I need to combine the first and last names of the individual and spouse without duplicating last names. (Spouses may have different last names in our spreadsheet). We also may not have a value for the spouse.

I have:
Column K with individual first name,
Column M for the individual's last name,
Column R for spouse's first name
Column T with spouse's last name.
They need to be combined in Column J

Any help would be greatly appreciated. There are 20,000 entries and I don't want to add them individually.

 

[Joe4]

Welcome to the Board!

Here are 3 examples showing your various situations and expected results, as I understand them:

Here is the formula I used (in cell V2 and copy down for all rows):

=IF(T2="",CONCATENATE(K2," ",M2),IF(M2=T2,CONCATENATE(K2," & ",R2," ",M2),CONCATENATE(K2," ",M2," & ",R2," ",T2)))

 

[Cubist]

Another option.

Book1
	J	K	L	M					R	S	T
1
2	Adam and Beck Jones	Adam	Jones						Beck		Jones
3	Ben Smith and Maggie Wilson	Ben	Smith						Maggie		Wilson
4	Mark Powell	Mark	Powell
Sheet5

Cell Formulas
Range	Formula
J2:J4	J2	=TEXTJOIN(" ",,K2,IF(L2=T2,"", L2),IF(R2="","","and " & R2),T2)

 

[Scott Huish]

=TEXTJOIN(" and ",TRUE,K2 & " " &M2,TRIM(R2&" " &T2))

Adam Jones and Beck Jones	Adam		Jones					Beck		Jones
Ben Smith and Maggie Wilson	Ben		Smith					Maggie		Wilson
Mark Powell	Mark		Powell

 

[Rick Rothstein]

This is @Joe4's formula without the CONCATENATE function calls and where this single formula will output the results for all the cells in the given range (just change all the 4's to the row number of the last data cell)...

=LET(
  k,K2:K4,
  m,M2:M4,
  r,R2:R4,
  t,T2:T4,
  IF(t="",k&" "&m,IF(m=t,k&" & "&r&" "&m,k&" "&m&" & "&r&" "&t))
)