Forum Discussion

RobMac14's avatar
RobMac14
Copper Contributor
Oct 08, 2021
Solved

Extracting the latest data

Hi   I need to construct a data set from a data dump out of an database that will be loaded onto a webportal.  At the moment, I have to do it manually, which is time consuming and I'm hoping that t...
excel
mathetes's avatar
mathetes
Gold Contributor
Oct 08, 2021

RobMac14 

 

If you have the latest version of Excel, you could use UNIQUE to get a list of unique clients, then FILTER to get the latest. Here's a video that explains those functions. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Since you went to the trouble to create a sample file to create an image, I or somebody else could demonstrate the steps if you'd just attach the actual Excel file you created.

  • RobMac14's avatar
    RobMac14
    Copper Contributor
    Oct 08, 2021

    mathetes 

    Hi Mathetes, really appreciate you taking the time to respond. I'll take a look at the video you've suggested.  But also as you've suggested, I've attached the file I created.

     

    Thanks again.  Rob

      • mtarler's avatar
        mtarler
        Silver Contributor
        Oct 08, 2021

        so mathetes already solved the problem in a cleaner way than I did but I will attach my solution.  I didn't rearrange the columns, which caused part of the complexity and solved it in a single formula, which is more of the complexity:

        =LET(input,A2:C7,uset,UNIQUE(INDEX(input,,1)&"@@@"&INDEX(input,,3)),clients,LEFT(uset,SEARCH("@@@",uset)-1),catagories,MID(uset,SEARCH("@@@",uset)+3,99),udate,MAXIFS(INDEX(input,,2),INDEX(input,,1),clients,INDEX(input,,3),catagories),CHOOSE({1,2,3},clients,udate,catagories))

        that said, I could suggest a slight improvement on mathetes solution by using this single array formula in column L (Cert Date):

        =MAXIFS(C2:C7,A2:A7,INDEX(J2#,,1),B2:B7,INDEX(J2#,,2))

         

Resources