Sort by the more recent in different sheets-CodePudding

Yesterday I asked your help (

February

March

Comparison

I expect to have in the comparison table that result:

ref     grade
1234    A
5678    A
3452    C
9012    A
6789    D
6711    C
9999    A
8822    A

In the Comparison sheet I would like to have the unique references with its last grade... Shall I add a date column in my sheets?

If in February 9999 was D, I want that in Comparison sheet appears just the March grade A... ( When I'll have April, it should be April...)

I just want to keep the more recent grade on a ref

Here is a google sheet sample if you can help :)

CodePudding user response：

See the sheet named "Erik Help," which I've added to your sample spreadsheet. The formula there in A1 is as follows:

=ArrayFormula({"ref"\"grade";SORT(VLOOKUP(UNIQUE(QUERY({January!A2:A; February!A2:A; March!A2:A}; "WHERE Col1 Is Not Null"));SORT({QUERY({January!A2:B; February!A2:B; March!A2:B}; "WHERE Col2 Is Not Null")\SEQUENCE(COUNTA(QUERY({January!A2:A; February!A2:A; March!A2:A}; "WHERE Col1 Is Not Null")))};3;0);{1\2};FALSE))})

Keep in mind that while you know that your list is in order, Sheets needs a way to know (and to reverse) that order to find the "most recent" grade.

First, headers are created by the formula:

"ref"\"grade";

Then, you need a list of all unique IDs only:

UNIQUE(QUERY({January!A2:A; February!A2:A; March!A2:A}; "WHERE Col1 Is Not Null"))

Then we need a listing of all ID-grade pairings within which to search for those unique IDs:

QUERY({January!A2:B; February!A2:B; March!A2:B}; "WHERE Col2 Is Not Null")

...but these pairings need to be paired with some way to number them in order...

SEQUENCE(COUNTA(QUERY({January!A2:A; February!A2:A; March!A2:A}; "WHERE Col1 Is Not Null")))

...and then that order needs to be reversed:

SORT( ;3;0)

(where the SEQUENCE column is column #3).

Now, VLOOKUP can find the UNIQUE ID list within this reversed table, and return the ID-grade pairing it first finds...

{1\2}

... which will be the most recent, since the table was in reverse order.

I will add that I would not have set the spreadsheet up this way. You are making a common mistake, in that you are creating separate sheets/reports that are each acting as a fragment of a whole database, rather than creating one sheet to act as the database (e.g., containing rows with student ID, grade and month) and then using one or more additional sheets to reference that one database and simply organize that data into reports after the fact.

Additional

I added four sheets/tabs and colored them pink. One is a note explaining the system. Here is that note again, for the reference of others:

I mentioned above that there is a better way to go about this whole process. The three front pink sheets/tabs (Grades, Conversion, Search) are all that is really necessary for a more streamlined spreadsheet. You don't need a separate sheet for each month and yet another for the most recent grades.

You may not even need the Conversion sheet, if you are going to use French months only in your real spreadsheet. But the Search!C1 formula would need to be rewritten (to eliminate the conversion of English to French month names).