AVERAGE and GET RID Off ZEROs

Boulakgnobot 

See attached.

HELP-FILTER-FILL-01.xlsx

I get lost in your nested IFs too.

So I added a table, and use XLOOKUP in columnD.

rachelThanks !

Here I want to FILL (D:D) with COLUMNS (A:A) and (C:C)

IF (A:A="Move up to a higher class" + C:C="Successful" OR "Fail")) OR (A:A="Repeats in case of failure" + C:C="Successful") = "PassB25"

IF ("Repeats in case of failure" + "Fail") OR (Exceptional repetition" + ("Successful" OR "Fail")) = "Repeat25"

IF ("Excluded in case of failure" + ("Successful" OR "Fail")) = "Exclude24"

IF ("Abandon" + "Abandon" = "Exclude24"

IF ("Move" + "Move" = "Move24"

I TRY with this below FORMULA but I still don't understand WHY it doesn't WORK ?

=IF(B3="";"";IF(OR(IF(AND(B3="3A";A3="Move up to a higher class";IF(OR(C3="Successful";C3="Fail")));IF(AND(B3="3A";A3="Repeats in case of failure";C3="Successful")));"PassB";"")

IMAGE below :

FILE link below :

https://www.cjoint.com/c/NGvtWN3UC1G

Boulakgnobot 

Updated file.

HELP-24-FILTER-DIFFERENT-CLASSES-DIFFERENT-DISCIPLINES---RACHEL---HELP-03-suite02---GET-RID-OFF-ZEROs-.xlsx

I added IF() around XLOOKUP to replace "0" with "".

rachel  Thanks !

Here, I have 3 dependent Drop-Downs (Q1), (Q2) and (M2)

The PROBLEM is that when it comes for "LEVEL {"Class_5e";"Class_6e"} (Q2) there is NO "ITV" Notes for those 2 LEVELS in 'DATA DETAILS'! Sheet so it gives me back ZEROs

SOLUTION : I want to GET RID Off z ZEROs in (I) Column

IMAGE below :

LINK below :

https://www.cjoint.com/c/NGobFKw7MnC

Boulakgnobot 

Can you try this OneDrive link:

HELP-24-FILTER-DIFFERENT-CLASSES-DIFFERENT-DISCIPLINES-22-Juin-2024---RACHEL---HELP-03-suite01---GET-RID-OFF-ZEROs.xlsx

I think something is wrong with uploading file in this site.

I tried to download some of the files I uploaded last week, didn't work either.

Thanks !

I have problem to DOWNLOAD this FILE. It gives me html file

Boulakgnobot 

Adding more filtering for "IEN" should fix it:

See attached.

rachel  Thanks !

Now HOW can I get rid off the zeros and the extras ?

IMAGE below :

FILE link below :

https://www.cjoint.com/c/NGlkZa7ttBp

Boulakgnobot 

Yes. Helpers are required for sorting.

I noticed you already have "RANK" in AVERAGE STUDENT 3e. So for 5 best students, I filter out students with RANK <=5; for 10 best students, I filter out students with RANK <= 10. (see attached).

rachel  Thanks !

Here, I want to FILTER depending on (L1) Criteria, the NUMBER of BEST STUDENTS (2;5 or 10 highest AVERAGE) from each class in 'AVERAGE STUDENT 3e'! sheet

(F); (0); (X) depending on (I1)

Maybe with "Helpers"

FILE link below :

https://www.cjoint.com/c/NGea4Uc8z7G

Boulakgnobot 

I see. then we need to add an IF inside SORT:

rachel  Thanks !

Here, there's some mistake with SORTING

It's OK for (Q1) "2nd Semester" because I wanted to SORT it by (L) "ANNUAL"

BUT there's a PROBLEM when it comes for (Q1) "1st Semester" because I want it to be SORT also BUT by (J) "S01"

FILE link below :

https://www.cjoint.com/c/NGdbfEgmBie

Boulakgnobot 

On 1). I think it is better to separate Average and Rank:

On 2), I added helper columns for sorting:

See attached.

rachel  Thanks !

1- NOW, I want my "1st Semester" RANKs to be in (M) same COLUMN as for "2nd Semester"

EXAMPLE :
IF = "1st Semester", Average STILL in (J) COLUMN but Rank GOES to (M) COLUMN Like for "2nd Semester"

Here, is it possible to Separate 2 Adjacents Columns at once ?

PICTURE below :

FILE link below :

https://www.cjoint.com/c/NGbp35tps6W

2- HOW can I, at the same time, SORT this LIST depending on (L);(D) then (C) Columns that mean the "ANNUAL" Average first then Last Name and First Name ?

Boulakgnobot 

I think you need to put CHOOSECOLS outside INDIRECT (see attached):

rachel  Thanks !

NOW, I want to change my "2nd Semester" to be FILTERED from 'AVERAGE STUDENT 3e! (O:T) with NON ADJACENTS by CHOOSING (O;Q;S;T) COLUMNS

EXAMPLE :
IF = "2nd Semester" so (J5:O85) = 'AVERAGE STUDENT 3e! (O3:T85) but WITHOUT (P) and ®
Maybe by USING CHOOSECOLS()

FILE link below :

https://www.cjoint.com/c/NGbb0QHdkPT

Boulakgnobot 

Yes, I believe you can. (Attached the excel).

I just convert G2:J7 as a table and names it "Classes". and I put =INDIRECT("Classes["&B2&"]") as data validation list:

rachel  Thanks !

Can I use the same principle to fill my drop down ?

Here I WANT "BLOC" (B3) DROP-DOWN LIST to DEPEND from "LEVEL" (B2) DROP-DOWN LIST

EXAMPLE :
- IF "LEVEL" = "Class_3"
       "BLOC" = "3B";"3B";"3C";"3D";"3E";"3F"
- IF "LEVEL" = "Class_4"
       "BLOC" = "4B";"4B";"4C";"4D";"4E";"4F"
- IF "LEVEL" = "Class_5"
       "BLOC" = "5B";"5B";"5C";"5D";"5E";"5F"
- IF "LEVEL" = "Class_6"
       "BLOC" = "6B";"6B";"6C";"6D";"6E";"6F"

FILE below :

https://www.cjoint.com/c/NFDdpxdfw7u

Boulakgnobot 

See attached.

I put the address of relevant ranges for 1st Semester, 2nd Semester in below table:

Then I use INDIRECT to get data from those ranges. 

rachel  Thanks !

Here in YELLOW SHEETS, I want to FILTER DATAs from 'AVERAGE STUDENT 3e! (B:E) + 'DATA DETAILS 3e! (G:G;K:K;O:O;S:S) + 'AVERAGE STUDENT 3e! (F:G) for "_3eA"'s "1st Semester"

So that : It DEPENDS from (N1) and (K2) DROP DOWN LISTS
EXAMPLE :
=IF(AND((N1="1st Semester");(K2="_3eA"));IF(AND((N1="2nd Semester");(K2="_3eA"));FILTER(
=IF(AND((N1="1st Semester");(K2="_3eB"));IF(AND((N1="2nd Semester");(K2="_3eB"));FILTER(
=IF(AND((N1="1st Semester");(K2="_3eC"));IF(AND((N1="2nd Semester");(K2="_3eC"));FILTER(

and REMOVE ERRORS and ZEROS

Image Illustration below

FILE Link below

https://www.cjoint.com/c/NFBna5Sql31

Boulakgnobot 

Hi,

I added a "Sheet1" that consolidates all three lists to handle this:

rachel  Thanks !

1- for "N°" (K), "First Name" (L), "Last Name" (M), "Registration" (N), "Secure" (O) and "Xpiration" (P)
a- I want to FIND the students who are recruited SEVERAL TIMES (DUPLICATES) outside; it's to say from other 2 sheets (RICHARDSON LIST'!$A$4:$F$23), (NARUTO LIST'!$A$4:$F$23) and (MICHELLE LIST'!$A$4:$F$23)

2- for "N° in DC" (Q), "First Name in DC" (R) and "Last Name in DC" (S)
a- FIND their RIGHT "N°" from ('DATA CENTER'!$A$4:$A$274), "First Name" from ('DATA CENTER'!$B$4:$B$274) and "Last Name" from ('DATA CENTER'!$C$4:$C$274)

3- for "which LISTS" (T) and "Coach" (U)
a- FIND which LISTS CONTAIN those DUPLICATES.
- Example: Here it's external duplicates so they come from DIFFERENT sheets lists; it's to say "RICHARDSON LIST" (sheets NAME or (RICHARDSON LIST'!B1), (NARUTO LIST'!B1) and (MICHELLE LIST'!B1))
b- FIND who are these COACHES from (RICHARDSON LIST'!$I$4:$I$23), (NARUTO LIST'!$I$4:$I$23) and (MICHELLE LIST'!$I$4:$I$23) who make DUPLICATES.

IMAGE below:

FILE link below:

https://www.cjoint.com/c/NAwp7jdNgDO

Boulakgnobot 

Hi, 

I use COUNTIFS to find duplicates. and then XLOOKUP to find FirstName in DC and LastName in DC.

rachel  Thanks !

1- for "N°" (K), "First Name" (L), "Last Name" (M), "Registration" (N), "Secure" (O) and "Xpiration" (P)
a- I want to FIND the students who are recruited SEVERAL TIMES (DUPLICATES) inside a same LIST ($A$4:$F$23) (left TABLE)

2- for "N° in DC" (Q), "First Name in DC" (R) and "Last Name in DC" (S)
a- FIND their RIGHT "N°" from ('DATA CENTER'!$A$4:$A$274), "First Name" from ('DATA CENTER'!$B$4:$B$274) and "Last Name" from ('DATA CENTER'!$C$4:$C$274)

3- for "which LISTS" (T) and "Coach" (U)
a- FIND which LIST bring those DUPLICATES.
- Example: Here it's internal duplicates so they come from this sheet list; it's to say "RICHARDSON LIST" (sheet NAME or (B1))
b- FIND who are these COACHES from ($I$4:$I$23) who make DUPLICATES.

IMAGE below:

FILE link below:

https://www.cjoint.com/c/NAvtGxTcGqo

Boulakgnobot 

Hi,

Thanks for further clarification!

Yes, I realised FIND function has limitations for cases such as  "ADAMO" "RIQ".

But I think below criterion actually make things easier:

a- ALL NAMES that don't APPEAR in "DATA CENTER"'s

b- But those names "Registration" Code (D), "Secure" Code (E) and "Xpiration" Code (F) MUST MATCH EXACTLY "DATA CENTER"'s "Registration" Code ('DATA CENTER'!$G$4:$G$275), "Secure" ('DATA CENTER'!$H$4:$H$275) and "Xpiration" Code ('DATA CENTER'!$I$4:$I$275) first

It is better to use XLOOKUP to search for First Name in DC and Last Name in DC by ColumnD + ColumnE + ColumnF first, and then check whether or not First Name and Last Name match the "First Name in DC" and "Last Name in DC".