Combine Names with the same and Different Last Names

[Yvia]

Hi, everyone! I need help in combining names from two Rollup fields with the result below:

First Name                        Last name                        Formula Field (Result I want)

Mary                                  A.                                       Mary A

Mary & John                     A & A                                Mary & John A

Mary & John                    A & B                                 Mary A and John B

Thank you in advance!

[Ben_Young1 · Accepted solution]

Okay. I'll admit that this one stumped me.
Out of full transparency, I'm not a fan of this method, but I'll hold my tongue.

Here's some context:

I created two tables. In the first one, we have our people records. In order for to work, we need the first and last name in separate fields. You also need a field that brings the first and last name together to form a full name string.

On our "target" table, we have the relationship field to the people table. We also have rollup fields for the first and last names.

Here's the table and the rollup configurations for each field:

With our fields we can now create our final field.
This field is going to be a rollup field. It should point to the field on your people table that contains the full name of your contacts.

From there, I'll use the following formula:

IF(
  values,
  IF(
    {Last Names},
    IF(
      REGEX_MATCH({Last Names}, ","),
      ARRAYJOIN(values, " & "),
      {First Names} & " " & {Last Names}
    )
  )
)

Here's the field configuration and the final result:

Okay, there's a few things you need to know if you're going to use this design pattern.
The last names must be completely identical in order for this to work. This includes whitespace.

As an example I added a trailing space to the end of the Mary Bradley record's last name field value.

Because of this added whitespace, the formula determines that the last names of these two records are different and not unique, therefore the last names and returned for each person contact.

The second important thing to note is that this pattern doesn't hold up well if you intend to link three or more people to a record. It quickly becomes bloated and the formula is no longer able to keep up the façade.

Here's what happens if I add three people to a record with two of the people sharing a last name and the third record having a unique last name.

[Yvia]

Sorry for the trouble but thank you so much this works!!!

[AsfandAli]

All the comments are valuable regarding combining names  with the last name.  There are many other ways and online websites for this but I like this solution First, I have checked my self whether it's working or not. Hopefully, it's working.

Thanks😊