Still not workin,,,,
Seems this array doesn't like dates, I changed the date format as you
suggested and still no luck.
So I changed the dates to a random numeric value and hey presto it worked
fine!
Thanks again for all your help Jacob

"Jacob Skaria" wrote:

1. From sheet2 (blank sheet) try advanced filter. Yes you need to select
unique records
In list range type Sheet1!A1:A379
In copy to type/select cell A1

3. Fine let us do this test with array formula.
With data as below. enter the below. If it still returns 0 you are having
problems with the date format...Try entering the dates manually (just for
this test) using Ctrl+semicolon and then edit

=MAX(IF($A$2:$A$10=A2,$B$2:$B$10))

ColA ColB ColC
Rego# RegoDate =formula
123-456 29/09/09
123-456 29/09/08
123-456 29/09/07
789-123 29/09/09
789-123 29/09/08

4. $ is not there for the first range reference so that when you copy that
to right it changes the column...upto BM...I have tried this with sample
data....

If this post helps click Yes
---------------
Jacob Skaria


"Sklyn" wrote:

The dates are in date format but not a standard one..
"2009-Sep-29" provided you enter it as 29/09/09
its a custom format of yyyy-mmm-dd;@

1) ok, step one i guess u mean access Advancedfilter, as Auto filter doesn't
work with no data selected.
At this point am i meant to also check 'Unique records only'?

2) Step 1 has already copied the header from column A,
No problem copying headers across..

3) To Array enter I press Ctrl+Shift+Enter instead of just Enter, correct?
When I do this it gives me a 0 value in most cells...

4) Are there any $ missing at the start of that formula?
When I do this it gives me a #N/A in every cell

Sorry to be a pain, but I'm pretty sure I'm following the instructions
correctly

"Jacob Skaria" wrote:

OK. So your data spans across columns A1 to BM379. If you are sure the dates
are in *** excel date format *** and not in text format; let us try the below

1. Suppose you have data in Sheet1. From Sheet2 access the menu
DataFilterAutofilter and 'Copy to another location' .
In list range type Sheet1!A1:A379
In copy to type/select cell A1

2. For copying headers use the below formula in Sheet2 B1
=Sheet1!B1 and copy the formula to the columns to the right upto BM


3. In cell B2 of Sheet2 apply below formula and copy that down to B379

=MAX(IF(Sheet1!$A$2:$A$379=A2,Sheet1!$B$2:$B$379))
PS: Please note that this is array entered..

4. In cell C2 of sheet2 apply the below formula and copy across to BM2
=INDEX(Sheet1!C$2:C$379,MATCH(1,(Sheet1!$A$2:$A$37 9=$A2)*(Sheet1!$B$2:$B$379=$B2),0))
PS: Please note that this is array entered..


If this post helps click Yes
---------------
Jacob Skaria


"Sklyn" wrote:

Thanks for the quick response!
It's not working for me :( I have the filtered unique entries list but I
can't seem to get the array to work. I am using excel 2003, and have rows to
379 and columns across to BM. I substituted 'G' in your formula to BO (as
this is where I put the filtered list) and '100' to 379, is that correct?

I require all the information from each column I just want to hide the rows
wich repeat in column A.

Your help is very much appreciated.. :)
Thanks

"Jacob Skaria" wrote:

1. Select the range in Col A including the header. You need to have headers
for the column
2. From menu DataFilterAdvanced FilterCopy to another location
3. In copy to I have selected G1 and check 'Unique records only'
4. Click OK will give you the unique list of reg number in ColG.
6. In H2 apply the below formula and copy down as required

=MAX(IF($A$2:$A$100=G2,$B$2:$B$100))

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"


If this post helps click Yes
---------------
Jacob Skaria


"Sklyn" wrote:

Hi, I have multiple entries in column A with different information accross
the row (including dates). i want to filter for only the most recent ones..
e.g.
Rego# RegoDate
123-456 29/09/09
123-456 29/09/08
123-456 29/09/07
789-123 29/09/09
789-123 29/09/08

I only want to see the most recent entry of each Rego#