SOLVED

# Formula

Brass Contributor

# Formula

Hello,

I was wondering if there was formula I could use that would essentially list out any person who is deceased (Column AR would indicate a "yes"), and fill each row (Photo below that ) by last name first name and date of birth in each cell combined. example " Doe, John 7/7/1924"

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Formula

You can use a variant of this:

``````=LET(
ded, FILTER(B2:D5, AR2:AR5 = "Yes"),
TAKE(ded, , 1) & ", " & CHOOSECOLS(ded, 2) & " " &
TEXT(TAKE(ded, , -1), "mm/dd/yyyy")
)``````

i.e.

1. Filter the last name, first name and date of birth columns for those rows where column AR is "Yes".

2. Take the 1st column, concatenate a comma-space, concatenate the 2nd column, concatenate a space, concatenate the last column formatted according to your desired date format

# Re: Formula

thank you so much!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Formula

You can use a variant of this:

``````=LET(
ded, FILTER(B2:D5, AR2:AR5 = "Yes"),
TAKE(ded, , 1) & ", " & CHOOSECOLS(ded, 2) & " " &
TEXT(TAKE(ded, , -1), "mm/dd/yyyy")
)``````

i.e.

1. Filter the last name, first name and date of birth columns for those rows where column AR is "Yes".

2. Take the 1st column, concatenate a comma-space, concatenate the 2nd column, concatenate a space, concatenate the last column formatted according to your desired date format