Hi. I’ve got this...

90312966 MRS LEE JONES 21 FORE
STREET REDRUTH
90312968 MISS RACHEL GOLDING FLAT 3 OLD
PLACE ALMA PLACE
90312979 Miss Lily Pascoe The
Cottage the lizard
90312984 mr david carr pitch
02 mullion holiday
park
90312984 MR DAVID CARR PITCH 02 MULLION HOLIDAY
PARK RUAN MINOR
90312984 MR DAVID CARR PITCH 02 MULLION HOLIDAY
PARK RUAN MINOR
90312991 MISS Nerys Hughes 7 POLDARK
VIEW CARHARRACK
90312991 MISS Nerys Hughes 7 POLDARK
VIEW CARHARRACK
90312992 MR ADRIAN TREWERN FLAT VOGUE
YARD GEW TERRACE

.... and there are further address columns to the right. Also, the
same number+person+address might appear more than twice. I want a
macro that will look at the number in col A and delete all the rows
above it with the same number, so that I am left with only the last
row for each person.

Ribbon: Data (tab) | Data Tools (group) | Remove Duplicates

-or...
http://office.microsoft.com/en-us/ex...001034626.aspx

-or...
http://tinyurl.com/2f9govk

-or-
http://tinyurl.com/XLCompanion

--
Jim Cone
Portland, Oregon USA

..
..
..

"robzrob" <(E-Mail Removed)>
wrote in message
news:a71891a4-3bf0-4c12-836a-(E-Mail Removed)...

Hi. I’ve got this...
90312966 MRS LEE JONES 21 FORE
STREET REDRUTH
90312968 MISS RACHEL GOLDING FLAT 3 OLD
PLACE ALMA PLACE
90312979 Miss Lily Pascoe The
Cottage the lizard
90312984 mr david carr pitch
02 mullion holiday
park
90312984 MR DAVID CARR PITCH 02 MULLION HOLIDAY
PARK RUAN MINOR
90312984 MR DAVID CARR PITCH 02 MULLION HOLIDAY
PARK RUAN MINOR
90312991 MISS Nerys Hughes 7 POLDARK
VIEW CARHARRACK
90312991 MISS Nerys Hughes 7 POLDARK
VIEW CARHARRACK
90312992 MR ADRIAN TREWERN FLAT VOGUE
YARD GEW TERRACE

.... and there are further address columns to the right. Also, the
same number+person+address might appear more than twice. I want a
macro that will look at the number in col A and delete all the rows
above it with the same number, so that I am left with only the last
row for each person.

A different way to do this would be -

1. Sort the data in excel in Ascending order using Data-Sort menu in excel.
2. Once you are done, enter the following formula in the cell adjacent to the data .i.e. if your data is on columns A,B & C then use column D and enter this formula in it.
=IF(A1=A2,1,0). This would indicate the duplicate rows with the value '1' in it.
3. Apply a filter, using Data - Filter and filter all rows with value '1' in column D.
4. Delete this data and you would be left with the unique data you need.

Lemme know if this works, its a 4 step process but you dont have to mess around with a macro!

Regards,
James

Submitted via EggHeadCafe
Composing WCF applications
http://www.eggheadcafe.com/tutorials...lications.aspx

On Nov 10, 2:17*pm, James PrabhatKiran <jamesprab...@gmail.com> wrote:
> A different way to do this would be -
>
> 1. Sort the data in excel in Ascending order using Data-Sort menu in excel.
> 2. Once you are done, enter the following formula in the cell adjacent tothe data .i.e. if your data is on columns A,B & C then use column D and enter this formula in it.
> =IF(A1=A2,1,0). This would indicate the duplicate rows with the value'1' in it.
> 3. Apply a filter, using Data - Filter and filter all rows with value '1'in column D.
> 4. Delete this data and you would be left with the unique data you need.
>
> Lemme know if this works, its a 4 step process but you dont have to mess around with a macro!
>
> Regards,
> James
>
> Submitted via EggHeadCafe
> Composing WCF applicationshttp://www.eggheadcafe.com/tutorials/aspnet/b428fb65-08b4-45c8-97cd-4...

Thx. It worked.

On Nov 8, 12:21*pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
> Ribbon: *Data (tab) | Data Tools (group) | Remove Duplicates
>
> -or...http://office.microsoft.com/en-us/ex...licate-rows-fr...
>
> -or...http://tinyurl.com/2f9govk
>
> -or-http://tinyurl.com/XLCompanion
>
> --
> Jim Cone
> Portland, Oregon *USA
>
> .
> .
> .
>
> "robzrob" <robz...@hotmail.com>
> wrote in messagenews:a71891a4-3bf0-4c12-836a-(E-Mail Removed)...
>
> Hi. *I’ve got this...
> 90312966 * * * MRS LEE JONES * * * * * * * * 21 FORE
> STREET * * * * * * * * * * * * * * * * REDRUTH
> 90312968 * * * MISS RACHEL GOLDING FLAT 3 OLD
> PLACE * * * * * * * * * * * * * * * * ALMA PLACE
> 90312979 * * * Miss Lily Pascoe * * * * * * * * The
> Cottage * * * * * * * * * * * * * * * * the lizard
> 90312984 * * * mr david carr * * * * * * * * pitch
> 02 * * * * * * * * * * * * * * * * * * * * * * * * mullion holiday
> park
> 90312984 * * * MR DAVID CARR * * * * * * * * PITCH 02 MULLION HOLIDAY
> PARK RUAN MINOR
> 90312984 * * * MR DAVID CARR * * * * * * * * PITCH 02 MULLION HOLIDAY
> PARK RUAN MINOR
> 90312991 * * * MISS Nerys Hughes 7 POLDARK
> VIEW * * * * * * * * * * * * * * * * CARHARRACK
> 90312991 * * * MISS Nerys Hughes 7 POLDARK
> VIEW * * * * * * * * * * * * * * * * CARHARRACK
> 90312992 * * * MR ADRIAN TREWERN FLAT VOGUE
> YARD * * * * * * * * * * * * * * * * GEW TERRACE
>
> ... and there are further address columns to the right. *Also, the
> same number+person+address might appear more than twice. *I want a
> macro that will look at the number in col A and delete all the rows
> above it with the same number, so that I am left with only the last
> row for each person.

Thx. 'Remove duplicates' worked.

On Nov 28, 12:28*pm, robzrob <robz...@hotmail.com> wrote:
> On Nov 8, 12:21*pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
>
>
>
>
>
> > Ribbon: *Data (tab) | Data Tools (group) | Remove Duplicates
>
> > -or...http://office.microsoft.com/en-us/ex...licate-rows-fr...
>
> > -or...http://tinyurl.com/2f9govk
>
> > -or-http://tinyurl.com/XLCompanion
>
> > --
> > Jim Cone
> > Portland, Oregon *USA
>
> > .
> > .
> > .
>
> > "robzrob" <robz...@hotmail.com>
> > wrote in messagenews:a71891a4-3bf0-4c12-836a-(E-Mail Removed)...
>
> > Hi. *I’ve got this...
> > 90312966 * * * MRS LEE JONES * * * * * * * * 21 FORE
> > STREET * * * * * * * * * * * * * * * * REDRUTH
> > 90312968 * * * MISS RACHEL GOLDING FLAT 3 OLD
> > PLACE * * * * * * * * * * * * * * * * ALMA PLACE
> > 90312979 * * * Miss Lily Pascoe * * * * * * * * The
> > Cottage * * * * * * * * * * * * * * * *the lizard
> > 90312984 * * * mr david carr * * * * * * * * pitch
> > 02 * * * * * * * * * * * * * * * * * * * * * * * * mullion holiday
> > park
> > 90312984 * * * MR DAVID CARR * * * * * * * * PITCH 02 MULLION HOLIDAY
> > PARK RUAN MINOR
> > 90312984 * * * MR DAVID CARR * * * * * * * * PITCH 02 MULLION HOLIDAY
> > PARK RUAN MINOR
> > 90312991 * * * MISS Nerys Hughes 7 POLDARK
> > VIEW * * * * * * * * * * * * * * * * CARHARRACK
> > 90312991 * * * MISS Nerys Hughes 7 POLDARK
> > VIEW * * * * * * * * * * * * * * * * CARHARRACK
> > 90312992 * * * MR ADRIAN TREWERN FLAT VOGUE
> > YARD * * * * * * * * * * * * * * * * GEW TERRACE
>
> > ... and there are further address columns to the right. *Also, the
> > same number+person+address might appear more than twice. *I want a
> > macro that will look at the number in col A and delete all the rows
> > above it with the same number, so that I am left with only the last
> > row for each person.
>
> Thx. *'Remove duplicates' worked.- Hide quoted text -
>
> - Show quoted text -Hmm - no it didn't - it removed the duplicates, but left me with the first row not the last

Re: XL Companion...
"Thx. 'Remove duplicates' worked.- Hmm - no it didn't - it removed the duplicates, but left me with the first row not the last "

Pressing the Shift key when clicking OK reverses the removal order - it leaves the last "duplicate" row.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

On Nov 28, 2:45*pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
> Re: *XL Companion...
> *"Thx. 'Remove duplicates' worked.- Hmm - no it didn't - it removed theduplicates, but left me with the first row not the last *"
>
> Pressing the Shift key when clicking OK reverses the removal order - it leaves the last "duplicate" row.
> --
> Jim Cone
> Portland, Oregon *USAhttp://www.mediafire.com/PrimitiveSoftware

That's not working - I'm still left with the first.

As to the use of the Shift key...
I thought you were referring to the XL Companion add-in of mine that I included in the list of possible solutions.
'--
Try sorting your data in descending order and then using the built-in xl2007 Remove Duplicates utility.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware


..
..
..

"robzrob" <(E-Mail Removed)>
wrote in message
news:e06891b5-8a5e-4365-a1c3-(E-Mail Removed)...
On Nov 28, 2:45 pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
> Re: XL Companion...
> "Thx. 'Remove duplicates' worked.- Hmm - no it didn't - it removed the duplicates, but left me with the first row not the last "
>
> Pressing the Shift key when clicking OK reverses the removal order - it leaves the last "duplicate" row.
> --
> Jim Cone
> Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware

That's not working - I'm still left with the first.

On Nov 30, 10:44*pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
> As to the use of the Shift key...
> I thought you were referring to the XL Companion add-in of mine that I included in the list of possible solutions.
> '--
> Try sorting your data in descending order and then using the built-in xl2007 Remove Duplicates utility.
> --
> Jim Cone
> Portland, Oregon *USAhttp://www.mediafire.com/PrimitiveSoftware
>
> .
> .
> .
>
> "robzrob" <robz...@hotmail.com>
> wrote in messagenews:e06891b5-8a5e-4365-a1c3-(E-Mail Removed)...
> On Nov 28, 2:45 pm, "Jim Cone" <james.cone...@comcast.netXXX> wrote:
>
> > Re: XL Companion...
> > "Thx. 'Remove duplicates' worked.- Hmm - no it didn't - it removed the duplicates, but left me with the first row not the last "
>
> > Pressing the Shift key when clicking OK reverses the removal order - itleaves the last "duplicate" row.
> > --
> > Jim Cone
> > Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
>
> That's not working - I'm still left with the first.

"Try sorting your data in descending order and then using the built-in
xl2007 Remove Duplicates utility." Tried that - even though they're
sorted DEscending, it still takes out the latest lines and I'm left
with the first!! It's a b****r, innit?