Delete duplicate rows

R

robzrob

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

Jim Cone

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

-or...
http://office.microsoft.com/en-us/e...te-rows-from-a-list-in-excel-HA001034626.aspx

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

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

--
Jim Cone
Portland, Oregon USA

..
..
..

"robzrob" <[email protected]>
wrote in message

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

James PrabhatKiran

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/tutorial...-47ee1a1eaf41/composing-wcf-applications.aspx
 
R

robzrob

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...
Click to expand...

Thx. It worked.
 
R

robzrob

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

-or...http://office.microsoft.com/en-us/excel-help/delete-duplicate-rows-fr...

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

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

--
Jim Cone
Portland, Oregon  USA

.
.
.

"robzrob" <[email protected]>
wrote in message
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.
Click to expand...

Thx. 'Remove duplicates' worked.
 
R

robzrob

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 :(
Click to expand...
 
J

Jim Cone

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

robzrob

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.
Click to expand...

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

Jim Cone

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" <[email protected]>
wrote in message
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.
Click to expand...

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

robzrob

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" <[email protected]>
wrote in message


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

"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? :)
 
J

Jim Cone

Five days without computer privileges, isn't that a long time for the institution to be in lock down? <g>
'---

This is what I get using xl2007...

DATA...
A 1
B 2
C 3
A 4
B 5
C 6

With Duplicates Removed...
A 1
B 2
C 3

'--------

DATA SORTED DESCENDING...
C 6
B 5
A 4
C 3
B 2
A 1

With Duplicates Removed...
C 6
B 5
A 4
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/XLCompanion

..
..
..

"robzrob" <[email protected]>
wrote in message
 
Top