Home > Mobile >  Exclude lines if ref and grade match in another sheets
Exclude lines if ref and grade match in another sheets

Time:03-29

It is the first time I am using Google sheet and I need to sort tables from two differents sheets. I want to keep only the ref that don't have a grade of A

here is a link in google sheet February '22

March '22

March'22

Expected result (comparison between Feb and March)

Comparison

Thanks for helping

CodePudding user response:

try:

=QUERY({'February''22'!A:B; 'March''22'!A2:B}; 
 "where Col2 <> 'A' and Col2 is not null"; 1)

or if you want only uniques:

=UNIQUE(QUERY({'February''22'!A:B; 'March''22'!A2:B}; 
 "where Col2 <> 'A' and Col2 is not null"; 1))

enter image description here

CodePudding user response:

Alternative:

  • In a new sheet, use "QUERY" for get all the data from the columns you desired to manipulate - see example # 1.
  • Create a new column "called FILTER" - in the "C" column which will have this formula - see example # 2.
  • Add an filter on this new sheet and filter the "C" column for filter those values with value 1.
  • Optional: you can hide the "FILTER" (which is the "C" column) by clicking the "C" column header > choose "hide column".

You can see the results in the "Feuille 4" sheet in your google sheet sample.

Example # 1:

Get all data from the (February'22) at the "A" column - in the ranges: A2:A7

=QUERY('February''22'!A2:A7)

Example # 2:

Add value 0 when the value in the cells of the column "B" is equals to "A" - otherwise: 1

=IF(B2 = "A";0;1)
  • Related