Making a list of items, then showing the date the item was last ordered

This is kind of a corollary to this post but I thought it warranted its own thread.

Is there any way a list could be generated of all the items we have ordered from our wholesaler (i.e. duplicates removed and only unique values remaining) and then in the next column along a vlookup or similar could return the LAST time the item was ordered?

If it helps to look at this problem from a "why is that useful" perspective, it would allow me to see if there are products we have probably run out of because we haven't ordered them for ages.

MANY THANKS!

Hi Wayne

I attach sample.xlsx a sample (much reduced!) spreadsheet. I've tried playing around with your examples but I think I must have explained the particular situation poorly as I can't get it to work or perhaps - get my head around how I should use the formula! :-)

Basically what I'm after is the LAST (most recent) date a particular product is ordered.

many thanks

try this
sample (1)(1).xlsm

Odd - Thank you Kalak - I can see the button but the macro seems to be corrupted because when I open the file I get a message about damage, and upon clicking the button I'm told there is no code to run??

hmm...
v. strange
i have just re-downloaded the file directly from my Post 4 above, clicked the button and it ran just as intended.

however, here's the VBA code behind it, which you might like to place in a VBA module in your sample.xlsx file , which would of course need to be macro-enabled first (becoming sample.xlsm) and then run the code manually
if it works for you, then you can easily assign it to the button anytime you like

Please Login or Register  to view this content.

it uses the dictionary object which works with most (since maybe 1997) versions of windows, but not on macs where you'd need some modifications.

Originally Posted by kalak

it uses the dictionary object which works with most (since maybe 1997) versions of windows, but not on macs where you'd need some modifications.

I think that was the problem :-) I tried it from a Mac. I'm now running Windows. It now seems to work nicely, thank you! Now I can just do a sort by cell colour?

MANY THANKS!

Now I can just do a sort by cell colour?

Maybe.

But better I think to do it directly as part of the code and then put in colours if you want these.

Do you want the relevant rows (as colored in red) at the bottom? at the top? in what order?, or perhaps (as you suggested in your opening post) all others deleted entirely?

Pl see attached file formula in Sheet2.