Comparing two columns and filling another column with duplicate values

[I_K]

Hello, I’m trying to compare A, B columns and fill C column with the equal values. Simple example of how it has to look like:

I know that Airtable is not a spreadsheet and rows cannot be cross connected but anyway what is the best way to solve this (Links/Rollups, Zapier…?) and how to implement?
I would be grateful for the help.

[kuovonne]

Can you explain the logic you use to determine which value you want in {Column C}? Is there supposed to be anything in {Column B} or {Column C} for the value “100-2”?

In general, if you want the value in a column to be based on a comparison of other values in the row, you would use a formula field with the IF(logical, value1, value2) formula.

If you want to “header” rows, you can try Airtable’s grouping feature. If you provide a bit more information on how your data is structured, we might be able to guide you more to see if links or rollups would make sense for you.

[I_K]

In fact, we can perceive the values ​​of {Column B} as an array with which we want to compare all the values ​​from {Column A} and display all the duplicates in {Column C}.

Suppose there are three cells in {Column A} with the value “100-1”, we compare them with {Column B}, in which there is one cell with the value “100-1”, then, since the values ​​coincide, all three values ​​of “100-1” from {Column A} are displayed in {Column C}.

The principle is that if the value exists in {Column B}, then it will be identical with the value of the cell in {Column A} and the entire list with an identical value of {Column A} should be displayed in {Column C}.

If there is no “100-2” in {Column B}, then the value is ignored and {Column C} remains empty for cells “100-2”.

[kuovonne · Accepted solution]

Thank you for the explanation. I understand your example better now.

While you cannot have a formula that searches {Column B} for a value, you can put the values in {Column B} in a separate table and link to them. Then, you can have a formula that calculates {Column C} based on the link.

Airtable does not currently support direct automatic linking, but it can generate links when you copy/paste into a link field.

Table Setup

Original Table, slightly modified

• {Column A} has your values,
• {Column B (link)} is a link to a record in the [Column B] table
• {Column C} is automatically generated from a formula
• {Column B active} is automatically generated from a lookup.

Here is the formula for {Column C}:
IF({Column B active},{Column A})

New table

Notice that the new table for {Column C} has only three records, one for each of the values in the “array”.

The table has an {active} field that you check for any values that you want in the “array”. Eventually there will be more records that will not have {active} checked. More on this later.

The table also has a column for {Table 1} that is automatically generated when you create the link field in the first table.

The Workflow

In your workflow, you enter {Column A} normally, and then you copy/paste the value from {Column A} to {Column B (link)}. That’s it.

It doesn’t matter if the value exists in the table [Column B] or not.

• If the value of {Column A} already exists in the table [Column B] and {active} is checked, the value from {Column A} appears in {Column C}.

• If the value of {Column A} does NOT exist in the table [Column B], it will be created as a new row in the table [Column B] but it will not be {active}. As it is not active, {Column C} will be blank.

---

Hope this helps.

[I_K]

It works really perfectly, it helps a lot in work. Thanks for the help and for your time!

I added automatic check marks to the values ​​from the {Active} column in the {Column B} tab.

To do this, in the {Column B} tab, I created a rollup (with the corresponding name) that refers to {Column C} in the {Table 1} tab, and the formula for rollup is ARRAYUNIQUE (values). And in the {Column B} tab, I created a column with the formula IF (Rollup = BLANK (), "", "✔"), which {Column B active} should refer to in {Table 1}.

Perhaps this information will be useful to someone.
Thanks again kuovonne! The topic can be considered closed.