return latest date for a order number that pops up frequently

[rikkie54321]

So the problem i'm facing is that a I have order 001 (for example) that has been worked on for a span of 2 or more months. This means the order 001 pops up 2 or more times with the same quantity in month 1,2 and so on. The quantity eventually gets booked off at the last month its been worked on (when its finished obvly).

Normally i would just use the average to calculate the quantity, but I also need it to return the last month as well, because that is the month it will get booked off.

The file that is added will probably tell you more about the problem and the result I'm looking for. The hardest part is that i needs to show the last month that the order has been worked on!



If there are any other questions feel free to ask them and I hope it is clear what I'm looking for.


Thanks in advance.


Kind regards,
Rikkie54321

[63falcondude]

Try this in K2:

=LOOKUP(2,1/($A$2:$A$20=$J2),B$2:B$20)

Drag to the right and down.

[AliGW]

Welcome to the forum.

The biggest problem you are going to face here is that the data in the right-hand column is text, not dates. Are you OK for this to be changed so that calculations can be made?

[63falcondude]

If you want a formula for column J as well, you can use this in J2:

=INDEX(A$2:A$20,MATCH(0,COUNTIF(J$1:J1,A$2:A$20),0)) Ctrl Shift Enter

Drag it down column K.

[rikkie54321]

Hi Falcon,


Thanks for your quick reply first of all. This seems to work in both files.

I don't want to have to put in the unique order numbers however, is there a solution to automate this process as well? Or atleast put in the unique order numbers with a formula. The data in the workbook will be retrieved from another file via a formula as well. All I want to do is put in the data in the other workbook.

Hope you understand what i mean.

Hope to hear from you and thanks again.

[rikkie54321]

You already posted it, haha. Ok let me try that. I'll come back to you!

[rikkie54321]

It seems, that when the latest date is on the bottom it works fine but when the latest date is on top it goes wrong. so when you turn around some of the months in the file it shows november instead of december for instance

[63falcondude]

Right. This was addressed in post #3.

[rikkie54321]

Is there something I can do, so this won't be the case? change the months into "10-2019" instead of "2019 sep" for instance?

[63falcondude]

Change the dates in column C to actual dates (you can then format these to show however you'd like).
You can use this formula in D2 to do so:

=(RIGHT(C2,3)&" 1, "&LEFT(C2,4))+0
Drag down column D then copy/paste values into column C.

Then, once you have actual dates in column C, you can use these:

L2 =AGGREGATE(14,6,(A$2:A$20=$J2)*(C$2:C$20),1)

K2 =LOOKUP(2,1/((A$2:A$20=J2)*(C$2:C$20=L2)),B$2:B$20)

[AliGW]

Right. This was addressed in post #3.

Yes, it was, and it was completely ignored, it seems, by the OP.

[rikkie54321]

Could you send me the workbook, doesn't seem to work for me. The month shows a #num! error This should look at the D cells right? if I do that i comes up with weird numbers.


See workbook

[rikkie54321]

Yes, it was, and it was completely ignored, it seems, by the OP.

I'm truely sorry, guess i misunderstood.

[AliGW]

It was pretty crucial.

Anyway, you have your answer now.

[63falcondude]

Those aren't the formulas that I shared in post #10.

See attachment.

[63falcondude]

The formula that I used in column D was just a quick way to get the dates into column C.

I copied the formulas in column D and then pasted values into column C. Then removed what was in column D.

Now that the dates are actual dates, you can format them to show however you'd like.

[rikkie54321]

Still facing a problem in my original file, however I won't be able to respond untill monday. To be continued.

[rikkie54321]

Hello guys,

The problem seemed to be in the date as I already expected. By making an additional formula the problem was resolved. The formula you provided me with seems to be working fine now.

A million thanks, the help was much appreciated!

Kind regards,
Rikkie54321

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[rikkie54321]

There is one more problem occuring with the INDEX formula. The formula is working fine, however if I use this for 10,000 cells approxamitely it takes hours to calculate it seems. Could that be right? and is there a solution for this problem?

[AliGW]

You probably should have mentioned the size of your dataset before we started!

Are you using full column references (e.g. A:A) in your formula?

[rikkie54321]

I'm sorry, I was not aware this might be problematic.


As for the column references, the formula looks like this: {=INDEX($A$2:$A$15000;MATCH(0;COUNTIF(S$1:S1991;$A$2;$A$15000);0))} so yeah, its using references.. I'm not sure this can be changed though.

[rikkie54321]

Alright, I've tried to do this with approximately 2000 cells at a time, this still takes 30 - 45 mins at a time. So, although the formula does what I need the only problem is that it takes ages unfortunately.

If there is a solution for this, I'm all ears.

Edit: I've been googling on this matter and found another thread on the mrexcel, i'm just not sure how to convert this into my formula, perhaps you can help?

(Hope I'm allowed to share links from other sites?) : https://www.mrexcel.com/forum/excel-...calculate.html

[JeteMc]

Perhaps replacing the array entered formula, in column J, with the following non-array formula will help:

Please Login or Register  to view this content.

Let us know if you have any questions.