Last Invoice Timing

%3CLINGO-SUB%20id%3D%22lingo-sub-2028559%22%20slang%3D%22en-US%22%3ELast%20Invoice%20Timing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028559%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22chattext-body%22%3EI%20have%20N%20number%20of%20employee%20each%20employee%20work%2013%20days%20for%2015days%20I%20need%20each%20employee's%20last%20invoice%20timing%20for%20each%20day%20also%20each%20employee%20will%20be%20having%20at%20least%2040%20to%20150%20invoices%20each%20day%2C%20let%20me%20know%20if%20you%20need%20any%20files%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22chat-footer%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2028559%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2029261%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20Invoice%20Timing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2029261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F916890%22%20target%3D%22_blank%22%3E%40Shanmukh0705%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20some%20of%20the%20true%20Excel%20MVPs%20are%20able%20to%20read%20minds%3B%20my%20own%20attempts%20at%20telepathy%2C%20to%20read%20your%20mind%20and%20better%20understand%20your%20need%20have%20failed%20miserably.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20yes%2C%20a%20file%20or%20two%20would%20help.%20Seriously%2C%20your%20description%20gives%20an%20idea%20of%20what%20you%20want%20to%20do%2C%20but%20not%20what%20your%20raw%20material%20looks%20like%2C%20how%20you've%20arranged%20or%20arrayed%20your%20data.%20And%20the%20%22how%20to%22%20of%20extracting%20what%20you%20want%20to%20extract%20depends%20very%20much%20on%20being%20able%20to%20work%20with%20the%20actual%20file.%20If%20it%20contains%20confidential%20information%2C%20please%20create%20a%20reasonable%20facsimile%20that%20does%20not%20reveal%20proprietary%20or%20private%20info.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2029458%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20Invoice%20Timing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2029458%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%3Bthank%20you%20for%20the%20response%20please%20find%20the%20below%20attachment%20for%20the%20required%20file%2C%20in%20sheet%201%20there%20are%20invoice%20details%20for%20each%20employee%20and%20on%20sheet%202%20there%20is%20the%20list%20of%20employees%20where%20I%20need%20the%20last%20invoice%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20sheet%20will%20help%20to%20resolve%20me%20question%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2030201%22%20slang%3D%22en-US%22%3ERe%3A%20Last%20Invoice%20Timing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2030201%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F916890%22%20target%3D%22_blank%22%3E%40Shanmukh0705%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20converted%20your%20raw%20data%20to%20a%20Table%20and%20shortened%20the%20names%20of%20the%20sheets%2C%20just%20because%20it%20makes%20the%20formula%20easier%20to%20read%20and%20create.%20Having%20the%20data%20as%20a%20Table%20also%20enables%20you%20to%20enlarge%20the%20database%20and%20the%20formula%20adapts%20to%20the%20new%20rows%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20does%20use%20the%20newly%20released%20function%20FILTER%2C%20so%20you%20will%20need%20to%20have%20the%20most%20up-to-date%20version%20of%20Excel%20in%20order%20for%20it%20to%20work.%20Here's%20the%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMAX(FILTER(Table1%5BTime%5D%2C(Table1%5Badduser%5D%3DReport!A2)*(Table1%5BDate%5D%3DReport!B2)%2C%22NotFound%22))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have N number of employee each employee work 13 days for 15days I need each employee's last invoice timing for each day also each employee will be having at least 40 to 150 invoices each day, let me know if you need any files 

6 Replies

@Shanmukh0705 

 

Maybe some of the true Excel MVPs are able to read minds; my own attempts at telepathy--to read your mind and better understand your need--have failed miserably.

 

So, yes, a file or two would help. Seriously, your description gives an idea of what you want to do, but not what your raw material looks like, how you've arranged or arrayed your data. And the "how to" of extracting what you want to extract depends very much on being able to work with the actual file. If it contains confidential information, please create a reasonable facsimile that does not reveal proprietary or private info.

@mathetes thank you for the response please find the below attachment for the required file, in sheet 1 there are invoice details for each employee and on sheet 2 there is the list of employees where I need the last invoice time.

 

Hope this sheet will help to resolve me question

@Shanmukh0705 

 

I converted your raw data to a Table and shortened the names of the sheets, just because it makes the formula easier to read and create. Having the data as a Table also enables you to enlarge the database and the formula adapts to the new rows automatically.

 

This formula does use the newly released function FILTER, so you will need to have the most up-to-date version of Excel in order for it to work. Here's the formula

=MAX(FILTER(Table1[Time],(Table1[adduser]=Report!A2)*(Table1[Date]=Report!B2),"NotFound"))

 

@mathetes Thank you, So I need to use this formula in any sheet to get the values am I correct

?

@Shanmukh0705 

 

You wrote:    So I need to use this formula in any sheet to get the values am I correct?

 

To which I can only answer with a qualified "Yes." That formula, or, more to the point, one like it with updated references, will work where the conditions are essentially the same, but you shouldn't expect to just copy and paste from that sample to another sheet.

 

Here are two links that can help you understand the FILTER function, which is the heart of what I gave you. The first is an excellent YouTube video which features a Microsoft employee (who occasionally makes an appearance on these pages) introducing the function at a meeting: https://www.youtube.com/watch?v=9I9DtFOVPIg

 

The other is a website that gives helpful explanations with examples that you can study at your leisure:

https://exceljet.net/excel-functions/excel-filter-function

 

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

@Shanmukh0705 

I didn't catch what is the logic of selection name/date in second sheet, but if the purpose is to show max time for each name and each date when PivotTable could work

image.png

Layout and design could be adjusted as needed.