Pull Unique Value into New Column?
One point that’s a little fuzzy with your description is exactly what you want to be in each of the separate Low/Medium/High fields (columns). You say:
…but does that mean
- ALL instances of each Low/Medium/High are tallied, so that a single Low field (column) entry could be, based on your example, “Low, Low, Low, Low”; i.e. “all of the lows” meaning literally all of the lows? Or
- Only one Low/Medium/High entry in each respective field depending on whether or not that choice was selected at least once in the lookup?
The solution will depend on which way you want to go.
If it’s #1, you could write a formula with a bunch of nested SUBSTITUTE functions that progressively strip out the bits you don’t want in each field by replacing them with empty strings. First off, though, it’s important to note that while Airtable displays the Lookup field results as a comma-separated list of items, that’s just for display. If you add a formula field with the following:
{Lookup} & ""
Your example above would output:
LowLowMediumLowHighMediumLow
This actually makes your work easier because you don’t have to remove the commas, only the unwanted levels. However, you probably want the end result with the commas in place, which is actually easy to do by taking advantage of the case-sensitivity of the level names. For example, with the Low level, your final substitution would replace “wL” with “w, L”, meaning that the comma-space separators would only appear between words, and never at the end. Similar substitutions could be done for the others.
Here’s the final formula for the Low field (replace {Lookup} with your actual Lookup field name):
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Lookup} & "", "High", ""
)
, "Medium", ""
)
, "wL", "w, L"
)
…and the Medium field:
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Lookup} & "", "High", ""
)
, "Low", ""
)
, "mM", "m, M"
)
…and the High field:
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Lookup} & "", "Low", ""
)
, "Medium", ""
)
, "hH", "h, H"
)
Here’s how it looks in a quick test table I built:
However, if #2 is your goal and you simply want to know if Low (or Medium or High) exists anywhere in the Lookup values, then your field formulas would be more like this (example with the Low field only, and using an emoji as an indicator):
IF(FIND("Low", {Lookup} & ""), "✅")As I pointed out, “Lookup” is just the field name I used in my test. Replace that with the name of the lookup field in your own table. Also remember that field names are case-sensitive, so “lookup” and “Lookup” are viewed differently.
If a count of the resulting values is your final goal, the count functions won’t get you there. However, a small modification to the formulas above will work. Instead of adding commas between each level name as the final substitution, convert the name into a single character (any character will do), then use LEN to count those characters. Here are the above field formulas converted to operate that way:
LEN(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Lookup} & "", "High", ""
)
, "Medium", ""
)
, "Low", "X"
)
)
LEN(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Lookup} & "", "High", ""
)
, "Low", ""
)
, "Medium", "X"
)
)
LEN(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Lookup} & "", "Low", ""
)
, "Medium", ""
)
, "High", "X"
)
)
