Combining several 'multiple select' columns

[Harvey_Mason]

Hi guys

We have several columns including ‘multiple select’ options within and wish to combine all these columns into one.

Is there a way to do this across the entire table, or do we need to manually duplicate the options and then manually go through each record to combine everything?

Many thanks in advance

Harvey

[Elias_Gomez_Sai · Accepted solution]

Maybe a formula with a CONCATENATE function of 2 fields, and then Converting it to Multiple Select? Let me try :grinning_face_with_sweat:

Demo: https://airtable.com/shrHwcqyUJjAmatho

Formula: Multiple1 & ',' & {Multiple2}.

I think if would be more Multiple Select fields we would need any function, but it works :slightly_smiling_face:

[Paul_Sorauer]

Hi Elias

It’s easy to do the concatenate as per the formula here, but in the demo the formula or the function you’re using to convert that into a multi-select isn’t visible and I can’t seem to work out how you did it…

Could you please describe how you made the ‘Converted’ field?

Thanks,

Paul

[Justin_Barrett]

I believe the {Converted} field is a multiple-select, and its contents were set by copying and pasting the contents from the {Formula} field. Airtable interprets the commas between items as item separators when pasting. If those items don’t exist as options in the multiple-select setup, Airtable will create them on the fly and add them.

[Paul_Sorauer]

Wow, ok, so it’s manually created? So what’s the point? Copy paste every time one of the other fields change? I was really hoping for something dynamic but from what I can see there’s no way to to create dynamic linked records or, dynamically combine linked records, or even do the equivalent of an Excel style VLOOKUP in a formula… :unamused:

[Justin_Barrett]

Paul_Sorauer:

Wow, ok, so it’s manually created? So what’s the point? Copy paste every time one of the other fields change?

If the options are going to change a lot, then it may not be the way to go. However, in other situations it might not be as bad as it sounds. It certainly beats manually selecting each item from a list. Here’s one situation where the user felt it would work (it’s applied to a link field, not a multiple select, but the process is the same):

Possible to Automate the Creation of Linked Records? Automations

There may be a way to do this using the help of an integration service like Zapier or Integromat. Aside from that, the best I can come up with involves a slight change to your process. Rather than manually build links from the [Words] table to the [Phrases] table, work the other way around, and let Airtable help with the link building. In your [Phrases] table, add a formula field (I called mine {Split}) that replaces the spaces in your phrases with comma-space combos, like so: SUBSTITUTE(Phras…

My guess is that a lot of users would like the added power you’re seeking, but it’s not available yet. Airtable is still growing and adding new features, and something like that might be added in the future. For now, this is probably the best we can do.

[Elias_Gomez_Sai]

Paul_Sorauer:

Could you please describe how you made the ‘Converted’ field?

Just changed the field type.

Paul_Sorauer:

So what’s the point? Copy paste every time one of the other fields change?

The inicial question didn’t asked to be over time.

[Sylvie_M]

So how was it done in the demo link??? Demo: https://airtable.com/shrHwcqyUJjAmatho

can somone please explain in simple terms or steps how to apply the formula?
I understand i make a new column / field and choose fx for formula but this did not work. How do I make a multi select table after i apply the forumula since the field is formula?

thank you

[Justin_Barrett]

The formula simply concatenates the output of both multiple-select fields. Strangely enough that formula field converts into a long text field when copying the data (whoever made it didn’t set up the share link to allow the actual formula to be copied), but my gut is that the formula is something like this:

{Mutiple1} & "," & {Multiple2}

From there you select the data in the formula field, copy it, then paste it into the {Converted} multiple-select field. Any options that don’t already exist will be added when pasting the data. They won’t necessarily match the colors of the originals, but the text will match.

[Peter_Knupffer]

I was with you all the way up to “Simply”