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


--
Dawns are only the illusion of the beauty of the world. When the world opens
eyes, reality takes back its rights.

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

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

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


--
Dawns are only the illusion of the beauty of the world. When the world opens
eyes, reality takes back its rights.


"Ed Ferrero" wrote:

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