Is there someway to exclude certain rows when using UNIQUE formula in google sheets

Question

I have sales order data coming through into a google sheet every 30 mins via a parser, I then use the unique formula to extract the unique sales lines into another sheet but I have a problem.

Due to our system, some orders can take up to 24 hours to properly convert to an actual sale in our system and these come through with 'N/A' as the sales order number. When these do convert and are given a sales number, both entries come through with the unique formula as they are technically unique but result in an increased sales total figure.

Is there some way to 'exclude' these sales lines that have 'N/A' as the sales order number?

The fields of data I have per line are:

Customer Number, Cust Name, City, Item Name, Created Time/Date, Increment ID (sales order number).

Answer 1

Method 1 - using a FILTER function for your Spreadsheet

Assuming that Increment ID (Sales Order) is in the F column and your Spreadsheet looks like this, you can use a FILTER function to filter the results by choosing only the rows that don't contain the N/A as the Increment ID (Sales Order).

=FILTER(A1:F6,F1:F6<>"N/A");

The A1:A6 refers to the range of the data in the whole table and F1:F6 is the range corresponding to the column which contains the Increment ID (Sales Order).

The Spreadsheet before the filter

The Spreadsheet after the filter

Method 2 - using Apps Script

Apps Script is a powerful development platform which can be used to build web apps and automate tasks. What makes it special is the fact that it is easy to use and to create applications that integrate with G Suite.

Therefore, the script below, written in Apps Script can be used to solve your issue:

function removeRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("NAME_OF_YOUR_SHEET");
  var data = sheet.getDataRange();
  var numRows = data.getNumRows();
  var vals = data.getValues();

  var rowsDel = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var data= vals[i];
    if (data[5] == 'N/A') { 
      sheet.deleteRow((i+1) - rowsDel);
      rowsDel++;
    }
  }
}

The above script works by removing the rows which contain the N/A value in the F column.

Furthermore, I suggest you check the following links since they might be of help:

• FILTER function;

• Apps Script;

• Sheet Class - deleteRow().