Query Multiple Columns with using "Where" and "AND"

Question

I have multiple sheets in data google sheet, let's both named is sheet 1 and sheet 2. In sheet 1, I have three columns as below:

And in sheet 2, I have three columns in below:

If I want to filter sheet 1 based column ID Primary, example the ID Primary is 54f94c2f. If i paste the ID Primary to column ID Primary (sheet 2), then on sheet 2 it will output the column as follows:

I have created formula for column 2 of sheet 2 (B2) and it is worked. The formula is:

=QUERY('Sheet1'!A:C; "SELECT C WHERE A = '"&A2&"'"; 0)

And the only for column 3 of sheet 3 (C2) is not worked. The formula is:

=QUERY('Sheet1'!A:C; "SELECT C WHERE A = '"&A2&"' AND C <> '"&B2&"'"; 0)

The question is how the formula on Cell C2, column 3 of sheet 3 fix it?

rockinfreakshow · Accepted Answer · 2023-09-18 16:53:14Z

0

Try this to populate all unique dates(for a ID Primary):

=torow(unique(filter(Sheet1!C:C,Sheet1!A:A=E2)),1)

Change the above formula ranges to fit your sheet design accordingly
use array_constrain() & restrict to just 2 dates if that's how you need it or so!

answered Sep 18, 2023 at 16:53

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akhmad Fahrudhin Over a year ago

Sorry i still not understand Sir, how the formula on column "Production Date 2"?

rockinfreakshow Over a year ago

the formula in Cell_F2(as in screenshot) populates both the dates automatically in F2 and G2

Akhmad Fahrudhin Over a year ago

Thankyou sir, if i have any questions, i will ask you again

Cooper · Accepted Answer · 2023-09-18 18:35:14Z

This could post all of the id's in sheet in sheet 2 sorted alphabetically along with first date in column2 and last date in column 3. Or all of the dates

function myfunck() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1");
  const osh = ss.getSheetByName("Sheet2")
  const vs = sh.getRange(2,1,sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  const ob = {pA:[]};
  vs.forEach((r,i)=>{
    if(!ob.hasOwnProperty(r[0])) {
      ob[r[0]] = [r[2]];
      ob.pA.push(r[0]);
    } else {
      ob[r[0]].push(r[2])
    }
    if(ob[r[0]].length > 1) {
      ob[r[0]].sort((a,b) => {new Date(a[2]).valueOf() - new Date(b[2]).valueOf()});//sort dates
    }
  })
  let o = ob.pA.map((p,i) => {return [p,ob[p][0],ob[p][ob[p].length - 1]]}).sort();/sort ids alpha
  o.unshift(["ID Primary","Production Date 1","Production Date 2"]);
  osh.clearContents();
  osh.getRange(1,1,o.length,o[0].length).setValues(o);
}

Collectives™ on Stack Overflow

Query Multiple Columns with using "Where" and "AND"

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related