SOLVED

Extracting the latest data

%3CLINGO-SUB%20id%3D%22lingo-sub-2826979%22%20slang%3D%22en-US%22%3EExtracting%20the%20latest%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2826979%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20construct%20a%20data%20set%20from%20a%20data%20dump%20out%20of%20an%20database%20that%20will%20be%20loaded%20onto%20a%20webportal.%26nbsp%3B%20At%20the%20moment%2C%20I%20have%20to%20do%20it%20manually%2C%20which%20is%20time%20consuming%20and%20I'm%20hoping%20that%20there%20are%20formulae%20within%20Excel%20that%20can%20help%20me%20speed%20this%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20picture%20below%20is%20a%20sample%20data%20set%20on%20the%20left%20and%20what%20I%20need%20to%20extract%20is%20the%20latest%20entry%20for%20a%20client%2C%20their%20role%20in%20column%20C%20and%20present%20it%20a%20a%20new%20table%2Fdataset.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RobMac14_0-1633693386468.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316182iA1AB08436A303F83%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RobMac14_0-1633693386468.png%22%20alt%3D%22RobMac14_0-1633693386468.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20latest%20data%20is%20highlighted%20in%20gold%20and%20that's%20what%20I%20need%20extracting.%26nbsp%3B%20I'm%20aware%20that%20I%20could%20use%20the%20MAX%20function%20to%20select%20the%20latest%20date%2C%20but%20that%20only%20produces%20the%20date%20and%20not%20the%20name%20and%20category.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20offer%20some%20assistance%20on%20this%20I'd%20be%20grateful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2826979%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2827252%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20the%20latest%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2827252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F877077%22%20target%3D%22_blank%22%3E%40RobMac14%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20the%20latest%20version%20of%20Excel%2C%20you%20could%20use%20UNIQUE%20to%20get%20a%20list%20of%20unique%20clients%2C%20then%20FILTER%20to%20get%20the%20latest.%20Here's%20a%20video%20that%20explains%20those%20functions.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20you%20went%20to%20the%20trouble%20to%20create%20a%20sample%20file%20to%20create%20an%20image%2C%20I%20or%20somebody%20else%20could%20demonstrate%20the%20steps%20if%20you'd%20just%20attach%20the%20actual%20Excel%20file%20you%20created.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2827415%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20the%20latest%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2827415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Mathetes%2C%20really%20appreciate%20you%20taking%20the%20time%20to%20respond.%20I'll%20take%20a%20look%20at%20the%20video%20you've%20suggested.%26nbsp%3B%20But%20also%20as%20you've%20suggested%2C%20I've%20attached%20the%20file%20I%20created.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%26nbsp%3B%20Rob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2827940%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20the%20latest%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2827940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F877077%22%20target%3D%22_blank%22%3E%40RobMac14%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BHere%20you%20go.%20I've%20added%20some%20text%20boxes%20to%20explain%20what%20I've%20done%2C%20but%20as%20expected%20a%20combination%20of%20UNIQUE%20and%20FILTER%20(along%20with%20MAX)%20did%20the%20trick.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828206%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20the%20latest%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828206%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%20That's%20fantastic%20and%20thank%20you%20very%20much.%20I'll%20be%20able%20to%20replicate%20that%20for%20a%20much%20larger%20data%20dump%20and%20it'll%20save%20me%20a%20lot%20of%20time.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again%20for%20your%20time%20and%20enjoy%20your%20day.%3CBR%20%2F%3E%3CBR%20%2F%3EBest%20wishes%20Rob%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828212%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20the%20latest%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828212%22%20slang%3D%22en-US%22%3E%3CP%3Eso%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Balready%20solved%20the%20problem%20in%20a%20cleaner%20way%20than%20I%20did%20but%20I%20will%20attach%20my%20solution.%26nbsp%3B%20I%20didn't%20rearrange%20the%20columns%2C%20which%20caused%20part%20of%20the%20complexity%20and%20solved%20it%20in%20a%20single%20formula%2C%20which%20is%20more%20of%20the%20complexity%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(input%2CA2%3AC7%2Cuset%2CUNIQUE(INDEX(input%2C%2C1)%26amp%3B%22%40%40%40%22%26amp%3BINDEX(input%2C%2C3))%2Cclients%2CLEFT(uset%2CSEARCH(%22%40%40%40%22%2Cuset)-1)%2Ccatagories%2CMID(uset%2CSEARCH(%22%40%40%40%22%2Cuset)%2B3%2C99)%2Cudate%2CMAXIFS(INDEX(input%2C%2C2)%2CINDEX(input%2C%2C1)%2Cclients%2CINDEX(input%2C%2C3)%2Ccatagories)%2CCHOOSE(%7B1%2C2%2C3%7D%2Cclients%2Cudate%2Ccatagories))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ethat%20said%2C%20I%20could%20suggest%20a%20slight%20improvement%20on%20mathetes%20solution%20by%20using%20this%20single%20array%20formula%20in%20column%20L%20(Cert%20Date)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DMAXIFS(C2%3AC7%2CA2%3AA7%2CINDEX(J2%23%2C%2C1)%2CB2%3AB7%2CINDEX(J2%23%2C%2C2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

RobMac14_0-1633693386468.png

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.

9 Replies

@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.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...

@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

best response confirmed by RobMac14 (Occasional Contributor)
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.

@mathetes That's fantastic and thank you very much. I'll be able to replicate that for a much larger data dump and it'll save me a lot of time.

Thanks again for your time and enjoy your day.

Best wishes Rob

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

 

@mtarler 

 

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]

I disagree a bit on that being the primary or most important use of LET but let me tell you that my formula was made significantly shorter using LET than the equation necessary if you substituted all the variables into their part (not to mention it probably wouldn't even work that way).

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"!

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