Help

Re: Comparing two columns and filling another column with duplicate values

Solved
Jump to Solution
2270 0
cancel
Showing results for 
Search instead for 
Did you mean: 
I_K
4 - Data Explorer
4 - Data Explorer

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.

1 Solution

Accepted Solutions

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

image

  • {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

image

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.

See Solution in Thread

4 Replies 4

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.

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”.

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

image

  • {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

image

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
4 - Data Explorer
4 - Data Explorer

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.