Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
I am working on a spreadsheet which has many different fields. The first
column is Last Name and the second column is first name. My objective is to delete the rows with duplicate names and only keep one of the rows. I tried using the following formula but it was only reading the first column which is last name. How do I change it to read the first and second column. For instance, Smith John Smith John Shot, Steve (Keep only one John Smith and delete the other.) My Second project which is a little different, is to take this completed file (the one that only has John Smith and Steve Shot left and remove the duplicates) and compare with another spreadsheet. I was going to add the spreadsheet fields to this final spreadsheet and then I will need to remove all duplicates. What formula do I need to apply so that I can use the filter? Then, I have two projects....one I need to delete the "duplicate" rows and the other I need to |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
Hi,
In cell C1, enter the following formula =A1&B1 and copy down. In cell D1, use the following formula if(countif($A$1:A1,A1)1,"a",1). Now select column D and navigate to Edit Go To Special. Click on the Formulas radio button and check the box for Text (Please remember to keep the other boxes unchecked). Now all the "a"'s in column D will be highlighted. You can simply delete all the rows now. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Kimmie" wrote in message ... I am working on a spreadsheet which has many different fields. The first column is Last Name and the second column is first name. My objective is to delete the rows with duplicate names and only keep one of the rows. I tried using the following formula but it was only reading the first column which is last name. How do I change it to read the first and second column. For instance, Smith John Smith John Shot, Steve (Keep only one John Smith and delete the other.) My Second project which is a little different, is to take this completed file (the one that only has John Smith and Steve Shot left and remove the duplicates) and compare with another spreadsheet. I was going to add the spreadsheet fields to this final spreadsheet and then I will need to remove all duplicates. What formula do I need to apply so that I can use the filter? Then, I have two projects....one I need to delete the "duplicate" rows and the other I need to |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
Just learned something new with two of the formulas that you provided....now
how do I delete all rows that are duplicate? Smith, John Smith, John Smith, John Smith, Robert Smith, Smith (Delete all Johns using a similiar spreadsheet layout that you provided with putting last name and first name together.) I cannot thank you enough...I just learned about this helpful site. Total lifesaver! "Kimmie" wrote: I am working on a spreadsheet which has many different fields. The first column is Last Name and the second column is first name. My objective is to delete the rows with duplicate names and only keep one of the rows. I tried using the following formula but it was only reading the first column which is last name. How do I change it to read the first and second column. For instance, Smith John Smith John Shot, Steve (Keep only one John Smith and delete the other.) My Second project which is a little different, is to take this completed file (the one that only has John Smith and Steve Shot left and remove the duplicates) and compare with another spreadsheet. I was going to add the spreadsheet fields to this final spreadsheet and then I will need to remove all duplicates. What formula do I need to apply so that I can use the filter? Then, I have two projects....one I need to delete the "duplicate" rows and the other I need to |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
Appreciate your feedback
-- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Kimmie" wrote in message ... Just learned something new with two of the formulas that you provided....now how do I delete all rows that are duplicate? Smith, John Smith, John Smith, John Smith, Robert Smith, Smith (Delete all Johns using a similiar spreadsheet layout that you provided with putting last name and first name together.) I cannot thank you enough...I just learned about this helpful site. Total lifesaver! "Kimmie" wrote: I am working on a spreadsheet which has many different fields. The first column is Last Name and the second column is first name. My objective is to delete the rows with duplicate names and only keep one of the rows. I tried using the following formula but it was only reading the first column which is last name. How do I change it to read the first and second column. For instance, Smith John Smith John Shot, Steve (Keep only one John Smith and delete the other.) My Second project which is a little different, is to take this completed file (the one that only has John Smith and Steve Shot left and remove the duplicates) and compare with another spreadsheet. I was going to add the spreadsheet fields to this final spreadsheet and then I will need to remove all duplicates. What formula do I need to apply so that I can use the filter? Then, I have two projects....one I need to delete the "duplicate" rows and the other I need to |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
I did not see a response to this question yet. "Kimmie" wrote: Just learned something new with two of the formulas that you provided....now how do I delete all rows that are duplicate? Smith, John Smith, John Smith, John Smith, Robert Smith, Smith (Delete all Johns using a similiar spreadsheet layout that you provided with putting last name and first name together.) I cannot thank you enough...I just learned about this helpful site. Total lifesaver! "Kimmie" wrote: I am working on a spreadsheet which has many different fields. The first column is Last Name and the second column is first name. My objective is to delete the rows with duplicate names and only keep one of the rows. I tried using the following formula but it was only reading the first column which is last name. How do I change it to read the first and second column. For instance, Smith John Smith John Shot, Steve (Keep only one John Smith and delete the other.) My Second project which is a little different, is to take this completed file (the one that only has John Smith and Steve Shot left and remove the duplicates) and compare with another spreadsheet. I was going to add the spreadsheet fields to this final spreadsheet and then I will need to remove all duplicates. What formula do I need to apply so that I can use the filter? Then, I have two projects....one I need to delete the "duplicate" rows and the other I need to |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
Use datafilteradvanced filter, select unique records, then copy to another
location That will give you a list without all the duplicates -- Regards, Peo Sjoblom "Kimmie" wrote in message ... I did not see a response to this question yet. "Kimmie" wrote: Just learned something new with two of the formulas that you provided....now how do I delete all rows that are duplicate? Smith, John Smith, John Smith, John Smith, Robert Smith, Smith (Delete all Johns using a similiar spreadsheet layout that you provided with putting last name and first name together.) I cannot thank you enough...I just learned about this helpful site. Total lifesaver! "Kimmie" wrote: I am working on a spreadsheet which has many different fields. The first column is Last Name and the second column is first name. My objective is to delete the rows with duplicate names and only keep one of the rows. I tried using the following formula but it was only reading the first column which is last name. How do I change it to read the first and second column. For instance, Smith John Smith John Shot, Steve (Keep only one John Smith and delete the other.) My Second project which is a little different, is to take this completed file (the one that only has John Smith and Steve Shot left and remove the duplicates) and compare with another spreadsheet. I was going to add the spreadsheet fields to this final spreadsheet and then I will need to remove all duplicates. What formula do I need to apply so that I can use the filter? Then, I have two projects....one I need to delete the "duplicate" rows and the other I need to |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
If I have the following listed, how do I remove all of the John Smiths? I
want any field showing up 2 or more times removed completely. Smith, John Smith, John Smith, John Doe, Linda Schmidt, Steve Moore, Dave Moore, Dave I would like the final report only to show Linda and Steve but remove all John Smiths and all Dave Moore because they are appearing more than once. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove duplicated data repeated....
Assume you have a header in A4 and your names start in A5
in row 2 in F2 put =COUNTIF($A$5:$A$500,A5)=1 adapt the range to fit and replace all cell references with your actual ones Select the table including the headers do datafilteradvanced filter, select copy to another location, preferably above or below the table itself. In the criteria box use $F$1:$F$2 once again use the actual references you have in your workbook, leave F1 blank and then click OK which will give you Names Doe, Linda Schmidt, Steve where Names is the fictive header I used -- Regards, Peo Sjoblom "Kimmie" wrote in message ... If I have the following listed, how do I remove all of the John Smiths? I want any field showing up 2 or more times removed completely. Smith, John Smith, John Smith, John Doe, Linda Schmidt, Steve Moore, Dave Moore, Dave I would like the final report only to show Linda and Steve but remove all John Smiths and all Dave Moore because they are appearing more than once. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to remove duplicated items from a list? | Excel Worksheet Functions | |||
How to remove duplicated data repeated in a column and keep just o | Excel Worksheet Functions | |||
No repeated/duplicated record | Excel Discussion (Misc queries) | |||
how do I remove repeated data in excel | Excel Worksheet Functions | |||
Remove ALL duplicated records, leaving behind NONE | Excel Discussion (Misc queries) |