software

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=quot;Fred Bloggsquot;)),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, Datagt;Filtergt;Autofilter and use the dropdown on the helper column
to Select 0
Mark the range of Visible rows and Delete.--
Regards

Roger Govierquot;Vertoobliquot; gt; wrote in message
...
gt; Hi,
gt; Today at work, I was presented with an Excel sheet, which denotes
gt; users for the company I work with, which is needed for something
gt; rather urgently.
gt; This Sheet contains over 120 000 rows of data.
gt; One thing that is present in the data, that is very important, are
gt; dates, which denote when an amendment to a users details have been
gt; made, and therefore, some users have multiple entries.
gt; What I would like help with is this, I would like to be able to
gt; automatically delete all but the last update of each user. ie :
gt;
gt; 10/06/99 ; Fred Bloggs
gt; 31/02/01 ; Fred Bloggs
gt; 02/03/02 ; Fred Bloggs
gt;
gt; I would like to be able to automatically erase the first two entries,
gt; leaving only the last. Unfortunately, while I am familiar with
gt; Formulas and charts and such, this falls outside of my level of
gt; knowledge. Would anyone please be able to help?
gt; Regards,
gt;
gt; Vert.
On Sun, 5 Mar 2006 19:36:44 -0000, quot;Roger Govierquot;
gt; wrote:

gt;Assuming the dates are in column A, and the names are in column B, then
gt;create a helper column with the following array entered formula
gt;
gt;{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=quot;Fred Bloggsquot;)),1,0)}
gt;
gt;To enter an array formula, commit with Ctrl Shift Enter. Do not type the
gt;curly braces { } yourself, Excel will enter them when you commit, or
gt;amend, using Ctrl Shift Enter.
gt;
gt;Copy down the helper column.
gt;Then, Datagt;Filtergt;Autofilter and use the dropdown on the helper column
gt;to Select 0
gt;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, quot;Roger Govierquot;
gt; wrote:

gt;Hi
gt;Assuming the dates are in column A, and the names are in column B, then
gt;create a helper column with the following array entered formula
gt;
gt;{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=quot;Fred Bloggsquot;)),1,0)}
gt;
gt;To enter an array formula, commit with Ctrl Shift Enter. Do not type the
gt;curly braces { } yourself, Excel will enter them when you commit, or
gt;amend, using Ctrl Shift Enter.
gt;
gt;Copy down the helper column.
gt;Then, Datagt;Filtergt;Autofilter and use the dropdown on the helper column
gt;to Select 0
gt;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 quot;Fred Bloggsquot;
{=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 Govierquot;Vertoobliquot; gt; wrote in message
...
gt; On Sun, 5 Mar 2006 19:36:44 -0000, quot;Roger Govierquot;
gt; gt; wrote:
gt;
gt;gt;Hi
gt;gt;Assuming the dates are in column A, and the names are in column B,
gt;gt;then
gt;gt;create a helper column with the following array entered formula
gt;gt;
gt;gt;{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=quot;Fred Bloggsquot;)),1,0)}
gt;gt;
gt;gt;To enter an array formula, commit with Ctrl Shift Enter. Do not type
gt;gt;the
gt;gt;curly braces { } yourself, Excel will enter them when you commit, or
gt;gt;amend, using Ctrl Shift Enter.
gt;gt;
gt;gt;Copy down the helper column.
gt;gt;Then, Datagt;Filtergt;Autofilter and use the dropdown on the helper column
gt;gt;to Select 0
gt;gt;Mark the range of Visible rows and Delete.
gt;
gt; I tried this and got a #Value error coming up, I think I may have
gt; explained it badly, but there are a number of different users, with
gt; different names, so would I need to insert a wild card of some sort
gt; for the user names (not all of them are called Fred Bloggs!)
gt; Regards,
gt; Vert.
Thanks again, Thats so obvious, I should be ashamed of myself!

On Mon, 6 Mar 2006 22:36:37 -0000, quot;Roger Govierquot;
gt; wrote:

gt;Hi Vert
gt;
gt;Substitute the cell reference B1 for quot;Fred Bloggsquot;
gt;{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=B1))1,0)}
gt;Make sure you use Ctrl Shift Enter when you edit the formulaHi

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 Govierquot;Vertoobliquot; gt; wrote in message
...
gt; Thanks again, Thats so obvious, I should be ashamed of myself!
gt;
gt; On Mon, 6 Mar 2006 22:36:37 -0000, quot;Roger Govierquot;
gt; gt; wrote:
gt;
gt;gt;Hi Vert
gt;gt;
gt;gt;Substitute the cell reference B1 for quot;Fred Bloggsquot;
gt;gt;{=IF(A1=MAX(($A$1:$A$120000)*--($B$1:$B$120000=B1))1,0)}
gt;gt;Make sure you use Ctrl Shift Enter when you edit the formula
gt;