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
Oct 08, 2021
RobMac14 Here you go. I've added some text boxes to explain what I've done, but as expected a combination of UNIQUE and FILTER (along with MAX) did the trick.
Data extract_Unique and Filtered.xlsx12 KB

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))

Data extract.xlsx11 KB

RobMac14

Copper Contributor

Oct 10, 2021

mtarler Thanks for taking the time to respond. That's really helpful. Best wishes Rob

Forum Discussion

Extracting the latest data

Resources