Drop Down List from Column with comma separate data Google Sheets

Question

i'm new with google sheets, i'm trying to create a Dynamic Drop down list from a column, the drop down list will show the items (without duplicates) from a column, this column have data separate by commas.

Example

Drop down list is in A1 Cell

Sheet have this structure:

Drop-Down-List (A1 Cell)

So when the user click on the A1 Cell will show the games list (no duplicate) when the user select a game, the list will be filtered with all the users with the same game in common.

so in this case if the user click on A1 drop-down-list will see:

Apex Castlevania Overwatch Street Fighter V Wow

and if the user select wow the sheet will be filter and will show Mike and jean rows

the idea is user can fill their data in a single row and made easier to find a common partner for their favorites games

thanks in advance :C

Cooper Cooper · Accepted Answer · 2019-08-04T17:19:21

Try this:

function dropDownList() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet178');
  var rg=sh.getRange(3, 3,sh.getLastRow(),1);
  var vA=rg.getValues();
  var fA=[];//full list
  var uA=[];//unique list
  for(var i=0;i<vA.length;i++) {
    var t=vA[i][0].toString().split(',');
    for(var j=0;j<t.length;j++) {
      fA.push(t[j]);
    }
  }
  for(var i=0;i<fA.length;i++) {
    if(uA.indexOf(fA[i])==-1) {
      if(fA[i]){ 
        uA.push(fA[i]); 
      }
    }
  }
  var vrg=sh.getRange('A1').clear({validationsOnly:true});
  var dv=SpreadsheetApp.newDataValidation().requireValueInList(uA.sort(), true).build();
  vrg.setDataValidation(dv);
}

Drop Down List from Column with comma separate data Google Sheets

1 Answers