Excel Magic Trick 942: Lookup Last Shipment Date For Customer, Sorted & Not Sorted Date Column
HTML-код
- Опубликовано: 11 сен 2024
- Download Excel Start File: people.highlin...
Download workbook: people.highline...
Lookup Last Shipment Date For Customer:
1. Sorted Date Column using LOOKUP function and array operation
2. Not Sorted Date Column using LOOKUP function and complex array operation with two criteria: "Product Match" and "Last Date"
"When we enter product, then I should give the last shipment rate and the last shipment date."
That is beautiful, IncongruentBalance!!
I guess the reason I always stay away from D Functions is because they are hard to copy down a column because of the criteria argument setup requirement.
I love hanging out with our online Excel Team because I would have never "seen": the DMAX solution if it were not for you, IncongruentBalance !!
Thanks. Great and clearr explanation.
Glad this helps!
I am glad that you like it!
Thank you. This video was very helpful and it was exactly what I was looking for.
Not really. What formula would you use with DMAX?
You are a genius and a saint.
Yes, but it is invloved:
Alt + Shift + F10 (keyboard to open any "Smart Tag")
Then the letter "o"
Altogether it is:
Alt + Shift + F10, o
Really appreciated your work.
thank you, sir...
very usefull for my job...
Thank you so much Mike, you really made my life easy. much appreciated.
You are welcome, Mahad! I am glad that the resource I post help make your life easier. Thanks for the support with your comments, Thumbs ups and your Sub : )
Cool trick, thanks Mike. Simply amazing
Excel GENIUS!! Thank you so much!!!!
You are welcome so much! Thanks for your support : )
This is God level @Mike.. Thank you so much
You are welcome!!!
God bless you!
=DMAX($A$1:$D$11,G1,F1:F2) and =SUMPRODUCT($D$2:$D$11,--($B$2:$B$11=F$2),--($C$2:$C$11=G2))
of course the only issue here is that you have to change the format of the sheet a little to use DMAX, but the same formula works for both approaches.
Plzzz tell me how I can have a nagetive value if it arrives in colum D ? I wants to show just like debit of a customer it will always arrives in nagetive so what should I have to do if I wants to show that plzzz guide ... With bundle of thanks in advance
Excellent video, thank you.
You are welcome, Nuno!!!! Thanks for your support with your comments, thumbs up and Sub : )
first i love your videos . do you have an index of tips which includes the tip no and the short description. I'm trying to build a spreadsheet with a lot of different things you can do and that would be helpful .
Hey, send me link after you post to Mr Excel Message Board, and I will take a look later...
Thanks.. it solved my purpose..
Thank you sir 👍👍👍
You are welcome, Riyas!!!!
Thank you! very helpful! =)
Glad this helps!
Mike was there any particular reason you didn't use DMAX?
You are the man! Made me money! 2
Glad it helps, Jordan! Thanks for the support with your comment, Thumbs Up and Sub : )
At 3:15 you said: "I gonna point to fill without formatting".
Is there a keyboard shortcut for "Fill without formatting" after dragging the fillhandle?
Thanks and greetings from the netherlands
{G1:f2} = {Product,Shippment date;ABC;=DMAX...}
G2 still had the data val, so changing the target product is easy enough
Thank you, very helful information. If the list is constantly growing is it possible to have this sybtable dynamic? I have a similar table (list of drawings) and in my case abc would be in three columns (a in one column, b in other and c in yet another). Should & be used to join the columns together when using a formula? And the last question is is it possible to achieve the same result using an advanced filter with formula? Thank you
very helpful.
Glad it is helpful! Thanks for your support with your comment, Thumbs Up and your Sub, swapnil mule!!!!
What should I have to do if there is any nagetive value in colum D ? This formula does not show nagetive value if it arrives in colum D
Hi Mike,
I had a project where I had to return the earliest date of unsorted data. Would I use the SMALL function - or could I ever use the LOOKUP function in this case?
What about to get earliest date ? i am having issue due to 0 while using MIN function instead of MAX
I do not. I am sorry.
one error found in above formula, that in above example, if the same date is mentioned for xyz is 11/5/2012, then the latest date will pick 222 for abc. please advise.
Great Video, Can you help me with this - Update Product Prices without Losing the Old Price Records, I am using Vlookup to Pull Price, But if I am updating the existing price then it changes all old records.
kindly help me with this I am really stuck at this
what if same date have two different value??, i tried it but failed please help me
Dear sir I want find the last entry of multiple times in single day your formula work to find the entry of date it doesn't work for me I want to find last entry of time for example the employee 🆔 1234 who enter in 6 June 15:00,15:30,17:00 and 17:15
I want to find 17:15 for the 🆔 1234
how do you do it with 2 criteria? The AND function doesn't seem to work
In second argument something like:
((Range=Condition)*(Range=Condition)) you can use other comparative operators such as >, =,
My data is horizontally how I can sir solve it. First row for date 2 nd row is sell data. I need the last date. Same table but horizontally.
I can not do it
How if rate has 3 lookup columns?
Nice video, Can some one help me? I have a row with dates and below it I have an empty row where I can tipe a "C" on any number of cells wanted or needed to get the dates above it, but I only get the first C, so what I am trying to do is actually ignore every "C" that I have wrote that correspond to past dates from today and only give me the closer one or next one from today.
For example: lets say that today is July 1 2018, so I have row 1 with a series of dates like A1=June 30 2018, B1= July 1 2018, C1=July 2 2018, D1=July 3 2018, E1=July 4 2018, etc and in row2 I have typed C on A2, C2 and E2 so with HLOOKUP it returns June 30 2018, but I dont want that since that date is now in the past, I want to get the next and more close date after the present day so it should be C2 that is July 2 2018, and so on since the today formula will update as the days pass.
This is to get the next checking date on a project cronogram so all the dates are defined but the result as for each task should be automatically updating to stay relevant for the scehdule, so past dates just dont make any sence to be the results showed on the gantt table diagram, and yes I know it is easy as just to be deleating the "C" that correspond to past dates, but then what is the point of excel then?
I CANT GET THE 3:12 TO 3:23 PART MY RESULT IS NOT THE SAME
try to insert the same max date for xyx and it will give you wrong results
working 70+ hours this week. No time. Try:
mrexcel [dot] com/forum
can you give me same for minimum
Unable to find 942 excercise
Here you go: people.highline.edu/mgirvin/RUclipsExcelIsFun/EMT942.xlsx