Google-sheets – Remove specific values based on the presence of one value

google sheets

I'm making a roller that randomly picks from one or two lists to assemble a new vertical list. I'm trying to prevent a specific few values from appearing together in one assembled list. These values, for reference, are W-2, W-1, W-0, W+1, W+2, and W+3. There should only ever be one of these in a list. For an example, here's something that my roller may generate:

Tn  
AA  
En  
SpM  
W-1  
Sp-2  
W-2  
Co

The problem here is that W-1 and W-2 are on this same list. Ideally every "W" cell except for one would be made blank or prevented from happening to begin with. Whether the one remainder is random or the first/last one doesn't matter (if I can be specific though, the ideal would be for the highest number to be left. So between W-1 and W+2, W-1 would be removed. However I'll gladly accept the simplest route whatever it may be.)

Here's a copy of my roller.

This is on the Data sheet.

Best Answer

There might be a easier way to do this, but, Here's a solution:
Add a extra column after BH

BI2:

=ARRAYFORMULA(ARRAY_CONSTRAIN(SORT(BH2:BH27,--REGEXREPLACE(BH2:BH27,"W(.*)", "$1"),0),COUNTA(BH2:BH27)-SUM(--REGEXMATCH(BH2:BH27,"^W"))+1,1))

We're sorting by the W in the descending order and constraining it to the first result only.

Related Solutions

Google Sheets – Finding and Displaying Matching Values Based on Two Criteria

Not sure there is a way to display a matching value base on two criteria - wouldn't be the first time I was wrong though.

In the meantime, you could create three queries and query each week individually, then you could do a vlookup to get the data into your table. I created a Google Spreadsheet. You should be able to edit this, etc.

If you want to do more automation, for example, have the report run every Monday morning and have it sent to your email, you can use my blog post and scroll down to the Using Triggers and Classes to Automate your Google Analytics Spreadsheet Delivery section.

Best of luck!

Google Sheets – Create Data Validation Function to Print Values of a Specified Column Across All Pages

If the names of sheets were known, you'd be able to do something like

={filter(Sheet1!C2:C, len(Sheet1!C2:C)); filter(Sheet2!C2:C, len(Sheet2!C2:C)); ... }

to join all of those together.

But since new Sheets will be added, and you don't know what names they will have, you need a script. I can think of two approaches: (a) the script will collect the data from C-columns itself; (b) the script will update the formula (of the above form) that will do the rest. The second one seems easier to code so I'll go with that.

function updateFormula(e) {
  if (e.changeType === 'INSERT_GRID' || e.changeType === 'REMOVE_GRID') {
    var sheets = e.source.getSheets();
    var formulaBits = [];
    for (var i = 0; i < sheets.length; i++) {
      if (sheets[i].getSheetName() !== '🌴') {
        var quotedName = "'"+sheets[i].getSheetName()+"'";
        formulaBits.push('iferror(filter('+quotedName+'!C2:C, len('+quotedName+'!C2:C)))');
      }
    }
    var formula = '={'+formulaBits.join('; ')+'}';
    e.source.getSheetByName('🌴').getRange('C1').setFormula(formula);
  }
}

To deploy:

Open Tools > Script Editor and put the above code there. Click Save.
Go to Resources > "This project's triggers" and create a trigger with parameters "from spreadsheet", "on change".

You may want to add a sheet yourself to make the script run the first time; after that it will update the formula in cell C1 of sheet 🌴 whenever a new sheet is added or a sheet is removed.

In case of renamed sheets, the software takes care of renaming itself, adjusting the formula automatically.

Best Answer

Related Solutions

Google Sheets – Finding and Displaying Matching Values Based on Two Criteria

Google Sheets – Create Data Validation Function to Print Values of a Specified Column Across All Pages

Related Topic