Google-sheets – Comparing values in two different columns, detecting those that are also present in the second

google sheetsgoogle-apps-script

I have two columns I'd like to compare data in. The first column has about 50 rows, whereas the second column has 200 rows.

I would like to be able to tell if any cell in the first column matches up with a value in the second column.

I first tried this with the QUERY() function

=QUERY(A12:B, "Select A where A = B")

but this didn't seem to work, I got a circular dependency error.

I then tried the array function.

=ARRAYFORMULA(if(A12:A=B12:B,"REMOVE_ME", "DON'T REMOVE ME"))

but this was outputting incorrect values. Is there an easier way to do this or am I missing something?

Here is sample data. The value 2 is given REMOVE ME because there is a value 2 in Column B. (sorry for the horrible formatting, I'm not sure how to do the grid correctly)

Column A   |    Column B   |     Column c   |
----------------------------------------------
1          |       2       |     DON'T REMOVE ME
-----------------------------------------------
5          |       7       |     DON'T REMOVE ME 
-----------------------------------------------
8          |       10      |     DON'T REMOVE ME
------------------------------------------------
2          |       13      |     REMOVE ME
-------------------------------------------------
 (blank)   |       24      |     (blank)
-------------------------------------------------
(Blank)    |       31      |     (blank)
-------------------------------------------------

Best Answer

This can be done by invoking match inside arrayformula, as follows:

=arrayformula(if(len(A12:A), if(isna(match(A12:A, B12:B, 0)), "Don't remove", "Remove"), ))

or with linebreaks,

=arrayformula(
   if(len(A12:A), 
     if(isna(match(A12:A, B12:B, 0)), 
       "Don't remove", "Remove"
     ), ))

The combination if(isna(match( means "if not found", literally "if match returned #N/A error". The outer if takes care of empty cells. Note that the trailing comma is intentional: this is how one correctly leaves a cell blank based on IF statement.

Related Solutions

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

Google Sheets – Make All Cells in Column Links from Values

I wasn't able to reproduce your results. As a matter of fact, it worked perfectly.

enter image description here

What you tried to do is most probably the following:

In A1 you typed in =HYPERLINK(CONCATENATE("http://www.example.com/id/",A1);A1) and this yields an error of coarse.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.