Please help! - Possibly need a Macro?

V

Vertoobli

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.
 
R

Roger Govier

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, Data>Filter>Autofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.
 
V

Vertoobli

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, Data>Filter>Autofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.
Click to expand...

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

Vertoobli

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, Data>Filter>Autofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.
Click to expand...

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.
 
R

Roger Govier

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
 
R

Roger Govier

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.
 
Top