Oct 08 2021 06:13 AM
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 there are formulae within Excel that can help me speed this up.
In picture below is a sample data set on the left and what I need to extract is the latest entry for a client, their role in column C and present it a a new table/dataset.
The latest data is highlighted in gold and that's what I need extracting. I'm aware that I could use the MAX function to select the latest date, but that only produces the date and not the name and category.
If anyone can offer some assistance on this I'd be grateful.
Oct 08 2021 07:24 AM
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.
Oct 08 2021 08:03 AM
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
Oct 08 2021 10:14 AM
Solution@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.
Oct 08 2021 11:06 AM
Oct 08 2021 11:08 AM
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))
Oct 08 2021 11:31 AM
Great options. I never cease to be amazed at how many different ways Excel's many functions can be brought to bear on a single problem.
As an aside, your use of LET is impressive, here. (Although I did think that LET was used primarily to make formulas shorter!) [smiley face]
Oct 08 2021 12:55 PM
Oct 08 2021 04:27 PM - edited Oct 08 2021 04:29 PM
Fair enough. I definitely overstated it with "primarily." Readability is another very important purpose served by LET, by naming things before you use them, especially results of formulas or functions that otherwise would be repeated... I love the results when I've used it, and did appreciate how you incorporated it so effectively to create a "one formula solution"!
Oct 10 2021 01:21 AM
Oct 08 2021 10:14 AM
Solution@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.