Please help! - Possibly need a Macro?
 

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.

Please help! - Possibly need a Macro?
 

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.

Please help! - Possibly need a Macro?
 

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.

Please help! - Possibly need a Macro?
 

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.

Please help! - Possibly need a Macro?
 

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.

Please help! - Possibly need a Macro?
 

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

Please help! - Possibly need a Macro?
 

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