Forum Discussion

millerseth528's avatar
millerseth528
Copper Contributor
Oct 25, 2024
Solved

=FILTER and =MAX(FILTER issue

I'm Filtering information from this Table but I only want the latest data to show. Why does the =MAX(FILTER formula not populate all the needed data? Could some one please assist, I need all the data...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Oct 30, 2024

    millerseth528 

    I can't seem to get =GROUPBY to work, what I read about it is that it's not available to me OK but this doesn't tell us what exact version of Excel you run (2021, 2024 or 365). Don't forget next times please

     

    In the meantime:

    in E3:

    =LET(
  unique_house, SORT( UNIQUE( Table1[House] ) ),
  max_date,     MAXIFS( Table1[Date], Table1[House], unique_house ),
  CHOOSE( {1,2}, unique_house, max_date )
)
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 25, 2024

millerseth528 

 

=FILTER( Table1, Table1[Date] = MAX( Table1[Date] ) )

 

millerseth528's avatar
millerseth528
Copper Contributor
Oct 28, 2024
Awesome thank you it worked. One more question though if you don't mind helping please. You would be a lifesaver. What if I have 10 different house names all with multiple entries and dates. How would I populate those?
Like say "A" House's last entry was 11/10/2024 but "B" House's last entry was 11/15/2025 and "C" House's last entry was 12/21/2024? How would I populate those?
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 28, 2024

    millerseth528 

     

    If you run 365 and already have it, easy with GROUPBY:

     

    =GROUPBY( Table1[House], Table1[Date], MAX,, 0 )

    or (with Headers):

    =GROUPBY( Table1[[#All],[House]], Table1[[#All],[Date]], MAX, 3, 0 )

     

     

    • millerseth528's avatar
      millerseth528
      Copper Contributor
      Oct 29, 2024

      Lorenzo 

      I can't seem to get =GROUPBY to work, what I read about it is that it's not available to me

      but yes, you get the idea of what I'm wanting to do. is there another way?

      I apologize in advance for how simple this probably is for some people like yourself but I'm a new user, still learning. 

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Oct 30, 2024

        millerseth528 

        I can't seem to get =GROUPBY to work, what I read about it is that it's not available to me OK but this doesn't tell us what exact version of Excel you run (2021, 2024 or 365). Don't forget next times please

         

        In the meantime:

        in E3:

        =LET(
  unique_house, SORT( UNIQUE( Table1[House] ) ),
  max_date,     MAXIFS( Table1[Date], Table1[House], unique_house ),
  CHOOSE( {1,2}, unique_house, max_date )
)

Resources