In Excel: List items, count and sort by descending order, combine with validate menu for search peri-CodePudding

I have this formula:

=LET(a;FRUITS!A2:INDEX(FRUITS!B:B;LOOKUP(2;1/(FRUITS!A:A<>"");ROW(FRUITS!B:B)));
     aa;INDEX(a;;1);
     ab;INDEX(a;;2);
     u;UNIQUE(INDEX(a;;2));
     c;COUNTIF(ab;u);
     d;COUNTIFS(ab;u;
                aa;">="&TODAY()
                      -VLOOKUP(SUBSTITUTE(D2;" ";"");
        {"24HOURS"\0;"2DAYS"\1;"3DAYS"\4;"7DAYS"\7;"2WEEKS"\14;"1MONTH"\30;"3MONTHS"\90;"6MONTHS"\180;"1YEAR"\365;"2YEARS"\730;"3YEARS"\1095;"TOTAL"\999999};
         2;0));
SORT(CHOOSE({1\2\3};u;c;d);{2\1\1};{-1\1\1}))

This is in one sheet where the formula is:

This is the other sheet containing the table with the raw data:

It is not counting by period in my real work file. I don't know why. Probably something to do with date formats? Now when I made this dummy file, I just changed names, and now it is working. The names in the real file are of people instead of fruits, like: "Doe, John", "Jane, Mary" etc. Could this be the problem and not the date format?

Also, I would like to have only 2 columns: one with the names of the unique items, and the 2nd with count "By Period". If I want the total count, I will just choose from the validation menu. The name's column on the left should be sorted by descending order according to the count by period.

Here is a file:

CodePudding user response：

This is what I understand, please clarify if my understanding is correct. In cell: G2 enter the following formula:

=LET(period, SWITCH(E2, "24HRS", 1, "7-DAYS", 7), refDate, E1,
 set, FILTER(TB_Fruits, (TB_Fruits[Date]>= refDate) * 
   (TB_Fruits[Date]<= refDate period)),
 fruits, INDEX(set,,2),uxFruits, UNIQUE(fruits),
 match, XMATCH(fruits, uxFruits), freq, DROP(FREQUENCY(match, UNIQUE(match)),-1),
 SORT(HSTACK(uxFruits, freq),2,-1)
)

and here is the output:

The screenshot doesn't show all the input data in TB_Fruits. The formula is looking for future dates based on refDate, if you want the opposite, i.e. looking into the past, see the note at the end of this answer.

In cell E1 I have a reference date (starting date) and on cell E2 a drop-down list with possible values: 24HRS, 7-DAYS for example.

Notice that you cannot use COUNTIFS if you have an array (it only works with ranges). I use instead FREQUENCY combined with XMATCH (removing the last bins [greater than the last value] at the end via DROP). It was hard for me to understand your formula, so I took a different approach based on my understanding.

If you need to add more periods you can add additional SWITCH elements. If you need a logical condition or expression (not a value) to define the period, then you can use IFSinstead.

If you still don't have DROP or HSTACK function available in your Excel version, you can use the following approach:

=LET(period, SWITCH(E2, "24HRS", 1, "7-DAYS", 7), refDate, E1,
 set, FILTER(TB_Fruits, (TB_Fruits[Date]>= refDate) *
   (TB_Fruits[Date]<= refDate period)),
 fruits, INDEX(set,,2),uxFruits, UNIQUE(fruits),
 match, XMATCH(fruits, uxFruits), freq, FREQUENCY(match, UNIQUE(match)),
 SORT(CHOOSE({1,2},uxFruits, FILTER(freq, freq<>0)),2,-1)
)

Note: The above formulas are looking at the future based on refDte, if you want to look into the past just change the filter conditions as follows:

(TB_Fruits[Date]<= refDate) *
       (TB_Fruits[Date]>= refDate-period)

CodePudding user response：

Keeping the logic from the initial formula, but switching to table references to simplify the formula and dropping the total count per name, this works:

=LET(a,HubLog[[Date]:[Fruit]],
     date,INDEX(a,,1),
     name,INDEX(a,,2),
     uniquename,UNIQUE(name),
     datecount,COUNTIFS(name,uniquename,
                date,">="&TODAY()
                      -VLOOKUP(SUBSTITUTE(D2," ",""),
                               {"24HOURS",0;
                                "2DAYS",1;
                                "3DAYS",4;
                                "7DAYS",7;
                                "2WEEKS",14;
                                "1MONTH",30;                               
                                "3MONTHS",90;
                                "6MONTHS",180;
                                "1YEAR",365;
                                "2YEARS",730;
                                "3YEARS",1095;
                                "TOTAL",999999},
                                2,0)),
     SORT(CHOOSE({1,2},uniquename,datecount),{2,1},{-1,1}))