Need help with a search function - vlookup or ???

[Sun]

Hi everybody,

sorry if I don't post in the right thread, I just don't really know where to
ask for help >_< (and sorry as well for my sometimes very bad english)

I'm currently working on Excel to update some material expiry dates, and I'm
looking for a solution that could avoid me hours of using the mouse's scroll.
To start with, I've download a spreadsheet from our ERP and the resulting
information is presented like below:

XXX555655 (material number)
(mandatory field from erp)
(price) (currency) (nb item) (packaging) (set-up
date) (expiry date)
PR11 100.17 EUR 1 EA 03.04.2006 01.04.2007
PR11 97.25 EUR 1 EA 02.04.2007 21.05.2007
PR11 100.17 EUR 1 EA 22.05.2007 31.12.2009
PR11 97.25 EUR 1 EA 01.01.2010 31.12.9999

I've got almost 1200 lines like that, and if I want to use the AutoFilter,
I first need to go throught everything to put the material number instead of
the "PR11" field. But it's really time consuming and useless as this way I
could check my expiry dates at the same time. what I'd like to know is if
there's a way to ask Excel to find me the very last date for X product,
looking through all the lines for a single product.
e.g, for that particular code XXX555655, I want to get as a result
"31.12.9999".

so I wonder if there's a formula that I can use, maybe before using a filter
to sort everything? I tried with vlookup, but I'm not really good at it, so
if anyone could help me with this, it would be brilliant.

Hope I'm clear enough in my query, thanks a million for your help!!

 

[Ed Ferrero]

Hi Sun,

Your English is fine.

Let me see if I understand the question.


If your data looks like this

| A | B | C | D | E | F |
1|
2| XXX5565
3| PR11 100.1 EUR xxx xxx
4| PR11 100.1 EUR xxx xxx
5| PR11 100.1 EUR xxx xxx
6|
7| XXX3234
8| PR11 100.1 EUR xxx xxx
9| PR11 100.1 EUR xxx xxx
10| PR11 100.1 EUR xxx xxx

In cell A2 enter formula =IF(B2<>"PR11",B2,A1)
Copy down.
Copy and paste as values, then use AutoFilter or a pivot table to find
maximum date for each material number.

Or, perhaps you can identify material number like this
=IF(LEFT(B2,3)="XXX",B2,A1)

HTH

Ed Ferrero
www.edferrero.com

 

[Sun]

Well....I tried with the first formula...and that's perfect!

Thanks a million for your help Ed, you really made me saving time!