Exclude lines if ref and grade match in another sheets

Question

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 https://docs.google.com/spreadsheets/d/1quXkNuSYPF7MCs7_PGzjHKOLDf0TSvPqlEumNCWc0lc/edit?usp=sharing

February '22

March '22

Expected result (comparison between Feb and March)

Thanks for helping

Can you share a worksheet example? - are you using formulas or google apps script? - one idea I have is to add a new column and filter that column by values that does NOT have the value "A" on the B column... — Marco Aurelio Fernandez Reyes, Mar 28 '22 at 16:04
I did a google sheet here https://docs.google.com/spreadsheets/d/1quXkNuSYPF7MCs7_PGzjHKOLDf0TSvPqlEumNCWc0lc/edit?usp=sharing — marie, Mar 28 '22 at 16:32

player0 · Accepted Answer · 2022-03-28T18:25:27.693

4

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))

edited Mar 28 '22 at 18:25

answered Mar 28 '22 at 16:34

player0

124,011
12
67
124

thanks for your help, it's not working , you can edit the sheet if you wanna try – marie Mar 28 '22 at 17:17
2

@marie answer and your sheet updated – player0 Mar 28 '22 at 18:18
2

@marie being fair, I think player0's answer is better :) – Marco Aurelio Fernandez Reyes Mar 28 '22 at 18:24

score 3 · Answer 2 · answered Mar 28 '22 at 17:54

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)

Great ! Shall I open a new question? I forgot to ask to remove the same occurrences... — marie, Mar 28 '22 at 18:18
@marie probably, but, before that, search first and if you don't get any results, then, post a new question :) - tip: you can remove duplicates by selecting "data > data cleaning > remove duplicates" — Marco Aurelio Fernandez Reyes, Mar 28 '22 at 18:21

Exclude lines if ref and grade match in another sheets

2 Answers2

Linked