I got a list with only duplicated values and an associated date. I want to get a result column with only one value out of the duplicates, but with the associated date. It's the earliest date I want.

Please look at example below (European dates):

I think I'm overthinking this as I have tried most formulas I know, but I can't figure it out.

CodePudding user response：

Try

=query(A:B,"select A,min(B) where A is not null group by A",1)

or

=query(A:B;"select A,min(B) where A is not null group by A";1)

CodePudding user response：

Earliest Dates

function earliestDateForEachValue() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rg = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn());
  const vs = rg.getValues();
  let obj = {pA:[]};
  vs.forEach((r,i) => {
    if(!obj.hasOwnProperty(r[0])) {
      obj[r[0]] = [];
      obj[r[0]].push(new Date(r[1]).valueOf());
      obj.pA.push(r[0]);
    } else {
      obj[r[0]].push(new Date(r[1]).valueOf())
    }
  });
  let o = [];
  obj.pA.forEach(p => {
    obj[p].sort((a,b) => {
      return a - b;
    });
    o.push([p,Utilities.formatDate(new Date(obj[p][0]),Session.getScriptTimeZone(),"dd.MM.yyyy")])
  });
  Logger.log(JSON.stringify(o));
}

Execution log
11:44:47 AM Notice  Execution started
11:44:47 AM Info    [["Value1","01.01.2022"],["Value2","02.01.2022"],["Value3","03.01.2022"]]
11:44:48 AM Notice  Execution completed

Data: