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.
Best answer by kuovonne
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.
Here is the formula for {Column C}:
IF({Column B active},{Column A})
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.
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.
+29Can 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.
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”.
+29In 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.
Here is the formula for {Column C}:
IF({Column B active},{Column A})
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.
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.
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.