Hi,
Today at work, I was presented with an Excel sheet, which denotes
users for the company I work with, which is needed for something
rather urgently.
This Sheet contains over 120 000 rows of data. :(
One thing that is present in the data, that is very important, are
dates, which denote when an amendment to a users details have been
made, and therefore, some users have multiple entries.
What I would like help with is this, I would like to be able to
automatically delete all but the last update of each user. ie :

10/06/99 ; Fred Bloggs
31/02/01 ; Fred Bloggs
02/03/02 ; Fred Bloggs

I would like to be able to automatically erase the first two entries,
leaving only the last. Unfortunately, while I am familiar with
Formulas and charts and such, this falls outside of my level of
knowledge. Would anyone please be able to help?
Regards,

Vert.

Hi
Assuming the dates are in column A, and the names are in column B, then
create a helper column with the following array entered formula

{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000="Fred Bloggs")),1,0)}

To enter an array formula, commit with Ctrl+Shift+Enter. Do not type the
curly braces { } yourself, Excel will enter them when you commit, or
amend, using Ctrl+Shift+Enter.

Copy down the helper column.
Then, DataFilterAutofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.


--
Regards

Roger Govier


"Vertoobli" wrote in message
...
Hi,
Today at work, I was presented with an Excel sheet, which denotes
users for the company I work with, which is needed for something
rather urgently.
This Sheet contains over 120 000 rows of data. :(
One thing that is present in the data, that is very important, are
dates, which denote when an amendment to a users details have been
made, and therefore, some users have multiple entries.
What I would like help with is this, I would like to be able to
automatically delete all but the last update of each user. ie :

10/06/99 ; Fred Bloggs
31/02/01 ; Fred Bloggs
02/03/02 ; Fred Bloggs

I would like to be able to automatically erase the first two entries,
leaving only the last. Unfortunately, while I am familiar with
Formulas and charts and such, this falls outside of my level of
knowledge. Would anyone please be able to help?
Regards,

Vert.

On Sun, 5 Mar 2006 19:36:44 -0000, "Roger Govier"
wrote:

Assuming the dates are in column A, and the names are in column B, then
create a helper column with the following array entered formula

{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000="Fred Bloggs")),1,0)}

To enter an array formula, commit with Ctrl+Shift+Enter. Do not type the
curly braces { } yourself, Excel will enter them when you commit, or
amend, using Ctrl+Shift+Enter.

Copy down the helper column.
Then, DataFilterAutofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.

Thankyou very much. I'll make a copy of the file and test this out.
Regards,
V.

On Sun, 5 Mar 2006 19:36:44 -0000, "Roger Govier"
wrote:

Hi
Assuming the dates are in column A, and the names are in column B, then
create a helper column with the following array entered formula

{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000="Fred Bloggs")),1,0)}

To enter an array formula, commit with Ctrl+Shift+Enter. Do not type the
curly braces { } yourself, Excel will enter them when you commit, or
amend, using Ctrl+Shift+Enter.

Copy down the helper column.
Then, DataFilterAutofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.

I tried this and got a #Value error coming up, I think I may have
explained it badly, but there are a number of different users, with
different names, so would I need to insert a wild card of some sort
for the user names (not all of them are called Fred Bloggs!)
Regards,
Vert.

Hi Vert

Substitute the cell reference B1 for "Fred Bloggs"
{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=B1))1,0)}
Make sure you use Ctrl+Shift+Enter when you edit the formula
--
Regards

Roger Govier


"Vertoobli" wrote in message
...
On Sun, 5 Mar 2006 19:36:44 -0000, "Roger Govier"
wrote:

Hi
Assuming the dates are in column A, and the names are in column B,
then
create a helper column with the following array entered formula

{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000="Fred Bloggs")),1,0)}

To enter an array formula, commit with Ctrl+Shift+Enter. Do not type
the
curly braces { } yourself, Excel will enter them when you commit, or
amend, using Ctrl+Shift+Enter.

Copy down the helper column.
Then, DataFilterAutofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.

I tried this and got a #Value error coming up, I think I may have
explained it badly, but there are a number of different users, with
different names, so would I need to insert a wild card of some sort
for the user names (not all of them are called Fred Bloggs!)
Regards,
Vert.

Thanks again, Thats so obvious, I should be ashamed of myself!

On Mon, 6 Mar 2006 22:36:37 -0000, "Roger Govier"
wrote:

Hi Vert

Substitute the cell reference B1 for "Fred Bloggs"
{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=B1))1,0)}
Make sure you use Ctrl+Shift+Enter when you edit the formula

Hi

No need to be ashamed. Sometimes when I'm so close to the problem I fail
to see the obvious.
Thanks for the feedback and glad you are sorted.

--
Regards

Roger Govier


"Vertoobli" wrote in message
...
Thanks again, Thats so obvious, I should be ashamed of myself!

On Mon, 6 Mar 2006 22:36:37 -0000, "Roger Govier"
wrote:

Hi Vert

Substitute the cell reference B1 for "Fred Bloggs"
{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=B1))1,0)}
Make sure you use Ctrl+Shift+Enter when you edit the formula