|
Delete duplicate rows
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. |
Delete duplicate rows
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" 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. |
Ribbon: Data (tab) | Data Tools (group) | Remove Duplicates-or...
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 |
Ribbon: Data (tab) | Data Tools (group) | Remove Duplicates-or...
On Nov 10, 2:17*pm, James PrabhatKiran 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 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 applicationshttp://www.eggheadcafe.com/tutorials/aspnet/b428fb65-08b4-45c8-97cd-4... Thx. It worked. |
Delete duplicate rows
On Nov 8, 12:21*pm, "Jim Cone" 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" wrote in ... 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. |
Delete duplicate rows
On Nov 28, 12:28*pm, robzrob wrote:
On Nov 8, 12:21*pm, "Jim Cone" 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" wrote in ... 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 :( |
Delete duplicate rows
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 |
Delete duplicate rows
On Nov 28, 2:45*pm, "Jim Cone" wrote:
*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. |
Delete duplicate rows
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" wrote in message ... On Nov 28, 2:45 pm, "Jim Cone" wrote: 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. |
Delete duplicate rows
On Nov 30, 10:44*pm, "Jim Cone" 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" wrote in ... On Nov 28, 2:45 pm, "Jim Cone" wrote: 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. "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? :) |
Delete duplicate rows
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" wrote in message ... On Nov 30, 10:44 pm, "Jim Cone" 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" wrote in ... On Nov 28, 2:45 pm, "Jim Cone" wrote: 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. "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? :) |
| All times are GMT +1. The time now is 09:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com