| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Deleting Duplicate Data in a Column.
Hi,
I am trying to delete duplicate data in a column. All of the data is in column A and has over 5000 rows. What is unique about the data, is duplication of it is not the same, please see the example below. ABQ - Albuquerque, NM, USA - Albuquerque International Airport ABQ - Albuquerque, NM, USA Albuquerque International Airport ABR - Aberdeen, SD, USA - Aberdeen Regional Airport ABR - Aberdeen, SD, USA Aberdeen Regional Airport ABS - Abu Simbel, Egypt - Abu Simbel ABS - Abu Simbel, Egypt Abu Simbel ABX - Albury, New South Wales, Australia - Albury ABX - Albury, New South Wales, Australia Albury Is there a formula or macro that I can use to remove the duplicate information where I can have a single line of data and also remove the blank rows that had the duplicate data? Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Cocktails for all! |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Deleting Duplicate Data in a Column.
Hi Sandy,
I Used Data/Filter/Advanced Filter: I selected the range which is $A$1:$A$5549. I'm not sure what the Criteria range should be, or if I need to checkmark the box for Unique records only. Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: You could use Advanced Filter to copy a unique list which would remove all the duplicates -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi, I am trying to delete duplicate data in a column. All of the data is in column A and has over 5000 rows. What is unique about the data, is duplication of it is not the same, please see the example below. ABQ - Albuquerque, NM, USA - Albuquerque International Airport ABQ - Albuquerque, NM, USA Albuquerque International Airport ABR - Aberdeen, SD, USA - Aberdeen Regional Airport ABR - Aberdeen, SD, USA Aberdeen Regional Airport ABS - Abu Simbel, Egypt - Abu Simbel ABS - Abu Simbel, Egypt Abu Simbel ABX - Albury, New South Wales, Australia - Albury ABX - Albury, New South Wales, Australia Albury Is there a formula or macro that I can use to remove the duplicate information where I can have a single line of data and also remove the blank rows that had the duplicate data? Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Cocktails for all! |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Deleting Duplicate Data in a Column.
My apologies, I failed to notce that your *duplicates* were not identical.
If the difference is always the second hyphen then in a *helper* column to the right of the data, (insert a Temporary column if necessary), enter: (I would recommend that you try this on a COPY of yourdata first) =TRIM(SUBSTITUTE(A2,"-","",2)) where A2 is the first cell of data then add th label above the formulas. Now double left-click on the fill handle of the above forumula which will copy the formula down to the end of the data With the formulas still highlighted Copy Paste Special and paste back over the same range that the formulas are in. Now delete your original data and then do the Advanced filter selecting *Copy to another location* and *Unique records only* Leave the *Criteria range* empty and select the first cell or where the original data was as the *Copy to* range. When you are confident, do it on your real data. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi Sandy, I Used Data/Filter/Advanced Filter: I selected the range which is $A$1:$A$5549. I'm not sure what the Criteria range should be, or if I need to checkmark the box for Unique records only. Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: You could use Advanced Filter to copy a unique list which would remove all the duplicates -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi, I am trying to delete duplicate data in a column. All of the data is in column A and has over 5000 rows. What is unique about the data, is duplication of it is not the same, please see the example below. ABQ - Albuquerque, NM, USA - Albuquerque International Airport ABQ - Albuquerque, NM, USA Albuquerque International Airport ABR - Aberdeen, SD, USA - Aberdeen Regional Airport ABR - Aberdeen, SD, USA Aberdeen Regional Airport ABS - Abu Simbel, Egypt - Abu Simbel ABS - Abu Simbel, Egypt Abu Simbel ABX - Albury, New South Wales, Australia - Albury ABX - Albury, New South Wales, Australia Albury Is there a formula or macro that I can use to remove the duplicate information where I can have a single line of data and also remove the blank rows that had the duplicate data? Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Cocktails for all! |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Deleting Duplicate Data in a Column.
I got everything to work perfectly. Thanks for your help.
Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: My apologies, I failed to notce that your *duplicates* were not identical. If the difference is always the second hyphen then in a *helper* column to the right of the data, (insert a Temporary column if necessary), enter: (I would recommend that you try this on a COPY of yourdata first) =TRIM(SUBSTITUTE(A2,"-","",2)) where A2 is the first cell of data then add th label above the formulas. Now double left-click on the fill handle of the above forumula which will copy the formula down to the end of the data With the formulas still highlighted Copy Paste Special and paste back over the same range that the formulas are in. Now delete your original data and then do the Advanced filter selecting *Copy to another location* and *Unique records only* Leave the *Criteria range* empty and select the first cell or where the original data was as the *Copy to* range. When you are confident, do it on your real data. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi Sandy, I Used Data/Filter/Advanced Filter: I selected the range which is $A$1:$A$5549. I'm not sure what the Criteria range should be, or if I need to checkmark the box for Unique records only. Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: You could use Advanced Filter to copy a unique list which would remove all the duplicates -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi, I am trying to delete duplicate data in a column. All of the data is in column A and has over 5000 rows. What is unique about the data, is duplication of it is not the same, please see the example below. ABQ - Albuquerque, NM, USA - Albuquerque International Airport ABQ - Albuquerque, NM, USA Albuquerque International Airport ABR - Aberdeen, SD, USA - Aberdeen Regional Airport ABR - Aberdeen, SD, USA Aberdeen Regional Airport ABS - Abu Simbel, Egypt - Abu Simbel ABS - Abu Simbel, Egypt Abu Simbel ABX - Albury, New South Wales, Australia - Albury ABX - Albury, New South Wales, Australia Albury Is there a formula or macro that I can use to remove the duplicate information where I can have a single line of data and also remove the blank rows that had the duplicate data? Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Cocktails for all! |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Deleting Duplicate Data in a Column.
Glad that you got it all worked out and my apologies once more for not
reading your OP properly -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... I got everything to work perfectly. Thanks for your help. Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: My apologies, I failed to notce that your *duplicates* were not identical. If the difference is always the second hyphen then in a *helper* column to the right of the data, (insert a Temporary column if necessary), enter: (I would recommend that you try this on a COPY of yourdata first) =TRIM(SUBSTITUTE(A2,"-","",2)) where A2 is the first cell of data then add th label above the formulas. Now double left-click on the fill handle of the above forumula which will copy the formula down to the end of the data With the formulas still highlighted Copy Paste Special and paste back over the same range that the formulas are in. Now delete your original data and then do the Advanced filter selecting *Copy to another location* and *Unique records only* Leave the *Criteria range* empty and select the first cell or where the original data was as the *Copy to* range. When you are confident, do it on your real data. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi Sandy, I Used Data/Filter/Advanced Filter: I selected the range which is $A$1:$A$5549. I'm not sure what the Criteria range should be, or if I need to checkmark the box for Unique records only. Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: You could use Advanced Filter to copy a unique list which would remove all the duplicates -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi, I am trying to delete duplicate data in a column. All of the data is in column A and has over 5000 rows. What is unique about the data, is duplication of it is not the same, please see the example below. ABQ - Albuquerque, NM, USA - Albuquerque International Airport ABQ - Albuquerque, NM, USA Albuquerque International Airport ABR - Aberdeen, SD, USA - Aberdeen Regional Airport ABR - Aberdeen, SD, USA Aberdeen Regional Airport ABS - Abu Simbel, Egypt - Abu Simbel ABS - Abu Simbel, Egypt Abu Simbel ABX - Albury, New South Wales, Australia - Albury ABX - Albury, New South Wales, Australia Albury Is there a formula or macro that I can use to remove the duplicate information where I can have a single line of data and also remove the blank rows that had the duplicate data? Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Cocktails for all! |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Deleting Duplicate Data in a Column.
Hi,
Is there a way to automatically extract duplicate? The duplicate datas are in my working sheet. And would like to have another worksheet to list the unique data. This worksheet will automatically extract duplicate & update the list whenever I enter a new data in the working sheet. Please advice how I can do it. Thank you Rgds Juliet "Sandy Mann" wrote: Glad that you got it all worked out and my apologies once more for not reading your OP properly -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... I got everything to work perfectly. Thanks for your help. Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: My apologies, I failed to notce that your *duplicates* were not identical. If the difference is always the second hyphen then in a *helper* column to the right of the data, (insert a Temporary column if necessary), enter: (I would recommend that you try this on a COPY of yourdata first) =TRIM(SUBSTITUTE(A2,"-","",2)) where A2 is the first cell of data then add th label above the formulas. Now double left-click on the fill handle of the above forumula which will copy the formula down to the end of the data With the formulas still highlighted Copy Paste Special and paste back over the same range that the formulas are in. Now delete your original data and then do the Advanced filter selecting *Copy to another location* and *Unique records only* Leave the *Criteria range* empty and select the first cell or where the original data was as the *Copy to* range. When you are confident, do it on your real data. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi Sandy, I Used Data/Filter/Advanced Filter: I selected the range which is $A$1:$A$5549. I'm not sure what the Criteria range should be, or if I need to checkmark the box for Unique records only. Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: You could use Advanced Filter to copy a unique list which would remove all the duplicates -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi, I am trying to delete duplicate data in a column. All of the data is in column A and has over 5000 rows. What is unique about the data, is duplication of it is not the same, please see the example below. ABQ - Albuquerque, NM, USA - Albuquerque International Airport ABQ - Albuquerque, NM, USA Albuquerque International Airport ABR - Aberdeen, SD, USA - Aberdeen Regional Airport ABR - Aberdeen, SD, USA Aberdeen Regional Airport ABS - Abu Simbel, Egypt - Abu Simbel ABS - Abu Simbel, Egypt Abu Simbel ABX - Albury, New South Wales, Australia - Albury ABX - Albury, New South Wales, Australia Albury Is there a formula or macro that I can use to remove the duplicate information where I can have a single line of data and also remove the blank rows that had the duplicate data? Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Cocktails for all! |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Deleting Duplicate Data in a Column.
I think that you really should have started a new thread with more detail of
how your data is laid out and what exactly you want extracted. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Juliet" wrote in message ... Hi, Is there a way to automatically extract duplicate? The duplicate datas are in my working sheet. And would like to have another worksheet to list the unique data. This worksheet will automatically extract duplicate & update the list whenever I enter a new data in the working sheet. Please advice how I can do it. Thank you Rgds Juliet "Sandy Mann" wrote: Glad that you got it all worked out and my apologies once more for not reading your OP properly -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... I got everything to work perfectly. Thanks for your help. Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: My apologies, I failed to notce that your *duplicates* were not identical. If the difference is always the second hyphen then in a *helper* column to the right of the data, (insert a Temporary column if necessary), enter: (I would recommend that you try this on a COPY of yourdata first) =TRIM(SUBSTITUTE(A2,"-","",2)) where A2 is the first cell of data then add th label above the formulas. Now double left-click on the fill handle of the above forumula which will copy the formula down to the end of the data With the formulas still highlighted Copy Paste Special and paste back over the same range that the formulas are in. Now delete your original data and then do the Advanced filter selecting *Copy to another location* and *Unique records only* Leave the *Criteria range* empty and select the first cell or where the original data was as the *Copy to* range. When you are confident, do it on your real data. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi Sandy, I Used Data/Filter/Advanced Filter: I selected the range which is $A$1:$A$5549. I'm not sure what the Criteria range should be, or if I need to checkmark the box for Unique records only. Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Drinks for all! "Sandy Mann" wrote: You could use Advanced Filter to copy a unique list which would remove all the duplicates -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Greg" wrote in message ... Hi, I am trying to delete duplicate data in a column. All of the data is in column A and has over 5000 rows. What is unique about the data, is duplication of it is not the same, please see the example below. ABQ - Albuquerque, NM, USA - Albuquerque International Airport ABQ - Albuquerque, NM, USA Albuquerque International Airport ABR - Aberdeen, SD, USA - Aberdeen Regional Airport ABR - Aberdeen, SD, USA Aberdeen Regional Airport ABS - Abu Simbel, Egypt - Abu Simbel ABS - Abu Simbel, Egypt Abu Simbel ABX - Albury, New South Wales, Australia - Albury ABX - Albury, New South Wales, Australia Albury Is there a formula or macro that I can use to remove the duplicate information where I can have a single line of data and also remove the blank rows that had the duplicate data? Thanks, Greg -- I am always thankful for the help everyone gives me. Virtual Cocktails for all! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting Duplicate Data in Two Worksheets | Excel Discussion (Misc queries) | |||
| Deleting Duplicate Cells in one Column. | Excel Discussion (Misc queries) | |||
| deleting duplicate data | Excel Discussion (Misc queries) | |||
| Deleting Duplicate Numbers In A Column: How ? | New Users to Excel | |||
| subtotal of identical data and deleting its duplicate | Excel Discussion (Misc queries) | |||
Linear Mode
