Two People, Same Address

[Olivia_Fagan]

I’ve tried searching for a straightforward answer on this and haven’t been able to find one.

We send out multiple communications in our office such as newsletters and invitations.
I’m having a hard time combining/formatting names at the same address so that it’s something like “John & Jane Doe 123 Main St Orlando, FL 12345” without having a separate spreadsheet.

I have individuals as separate records, including spouses/people living at the same address. Each record has many columns to separate out first/last name and street/city/state/zip code as well as formulas to combine names/addresses.

How can I create a new view or linked table that combines names (if applicable, i.e. spouses) at the same address into one record? (Like “Record1 First Name” & “Record2 First Name” “Last Name”). Perhaps this is a many-to-one relationship, but I haven’t figured out how to format it correctly. (For example, a rollup or lookup of the first names plus a formula could work if I could change the comma to " &")

I’m hoping this is something easy to do and I just don’t have enough work experience to have figured it out. Thanks in advance!

[Jeremy_Oglesby]

One idea that comes to mind is to have a separate “Addresses” table.

When you create a “Person” record, you would have one Field(column) for “Address(es)”, and it would be a linked record field pointing to the “Addresses” table. You would create an “Address” record for the person, rather than filling in their address information in the “Person” record itself. Then, when a person has an address that already exists (somebody else has the same address), you link that person to the same address.

This also opens up the possibility of people having more than one address, since you can link more than one address record to a person, if you so chose.

There may be other ways to accomplish this as well - this is just what came to mind.

[Olivia_Fagan]

Thanks Jeremy! I started playing around with that separate table method to see if that could work. I just figured out how to combine the first names in one column using ARRAYJOIN({First name(s)}," &; "), keeping last name in a separate column, which will work fine for exporting to InDesign’s Data Marge.

The issue here now then lies when people have different last names (i.e. Tom Walker & Christine Heinzen in below screen shot). I could hand write those addresses since there aren’t that many of them, but it would be great if we could do it all within AirTable.

[Jeremy_Oglesby]

Try pasting this into your formula field called {First & Last Name}

IF(
   SEARCH(
      ",",
      {Last Name(s)}
   ),
   LEFT(
      {First Name/s},
      SEARCH(
         "&",
         {First Name/s}
      ) - 2
   ) & 
   " " & 
   LEFT(
      {Last Name(s)},
      SEARCH(
         ",",
         {Last Name(s)}
      ) - 1
   ) & 
   " & " & 
   RIGHT(
      {First Name/s},
      LEN({First Name/s}) - SEARCH(
         "&",
         {First Name/s}
      ) - 1
   ) & 
   " " & 
   RIGHT(
      {Last Name(s)},
      LEN({Last Name(s)}) - SEARCH(
         ",",
         {Last Name(s)}
      ) - 1
   ),
   {First Name/s} & " " & {Last Name(s)}
)

[Olivia_Fagan]

Jeremy_Oglesby:

IF( SEARCH( “,”, {Last Name(s)} ), LEFT( {First Name/s}, SEARCH( “&”, {First Name/s} ) - 2 ) & " " & LEFT( {Last Name(s)}, SEARCH( “,”, {Last Name(s)} ) - 1 ) & " & " & RIGHT( {First Name/s}, LEN({First Name/s}) - SEARCH( “&”, {First Name/s} ) - 1 ) & " " & RIGHT( {Last Name(s)}, LEN({Last Name(s)}) - SEARCH( “,”, {Last Name(s)} ) - 1 ), {First Name/s} & " " & {Last Name(s)} )

Thanks, I tried pasting that in but it only resulted in the “#ERROR” output. I’ll play around with this a bit more and post back here if I can figure something else out.

Thanks for your help!

[Jeremy_Oglesby]

Hmm… I have it working in my test base, and I’m pretty sure I just copied and pasted the formula from my base. Maybe I messed something up in trying to format it all pretty for you, so here’s just a raw copy-paste from my base:

IF(SEARCH(",",{Last Name(s)}),LEFT({First Name/s},SEARCH("&",{First Name/s})-2) & " " & LEFT({Last Name(s)},SEARCH(",",{Last Name(s)})-1) & " & " & RIGHT({First Name/s},LEN({First Name/s})-SEARCH("&",{First Name/s})-1) & " " & RIGHT({Last Name(s)},LEN({Last Name(s)})-SEARCH(",",{Last Name(s)})-1),{First Name/s} & " " & {Last Name(s)})

That exact formula works in my test base, so it should work in yours too, assuming the field names match. Double check that all the field names match up to how you have your field names formatted - case sensitivity and all.