Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicate entries
I have to copy data from an excel sheet on a website to my excel sheet. The
company that I get the data from doesnt separate the DATE from the name of the data e.g. [ICSC-UBS (FEB 3)] is in one cell. When I paste the data from the website my sheet still has info that I pasted from the previous month. I need to be able to identify any duplicate entries but the date at the end keeps it from being an exact duplicate because the date has changed. The formula that Im using right now is =IF(COUNTIF($A$2:$A$150,A115)1,"Duplicate","") this will give me the word Duplicate in the rows where there is an exact match but I still need to watch carefully to be able to keep the old data weeded out. Ive tried variations with RIGHT, LEFT , and , LEN but havent had any success. I need to be able to ignore the last 6 char (FEB 3) in the cell that contains the [ICSC-UBS (FEB 3)] in order for there to be an exact match. Does anybody have any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicate entries
Assuming something like [ICSC-UBS always precedes the (Feb 3)] create a
helper column that extracts that left portion of the text string and dedupe based on that. =LEFT(A1,10) should do it. Post back if you have questions. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "mmcap" wrote: I have to copy data from an excel sheet on a website to my excel sheet. The company that I get the data from doesnt separate the DATE from the name of the data e.g. [ICSC-UBS (FEB 3)] is in one cell. When I paste the data from the website my sheet still has info that I pasted from the previous month. I need to be able to identify any duplicate entries but the date at the end keeps it from being an exact duplicate because the date has changed. The formula that Im using right now is =IF(COUNTIF($A$2:$A$150,A115)1,"Duplicate","") this will give me the word Duplicate in the rows where there is an exact match but I still need to watch carefully to be able to keep the old data weeded out. Ive tried variations with RIGHT, LEFT , and , LEN but havent had any success. I need to be able to ignore the last 6 char (FEB 3) in the cell that contains the [ICSC-UBS (FEB 3)] in order for there to be an exact match. Does anybody have any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicate entries
Else, you can use Ctrl + H (to find and replace)
1. Select the rows over where you want to remove FEB 3. 2. Press Ctrl + H. 3. On the first box enter (FEB 3) and on the second box don't enter anything. And press Replace All button. So you will get rid of (FEB 3). Hope this helps Thanks, Shail On Feb 5, 9:28 pm, mmcap wrote: I have to copy data from an excel sheet on a website to my excel sheet. The company that I get the data from doesn't separate the DATE from the name of the data e.g. [ICSC-UBS (FEB 3)] is in one cell. When I paste the data from the website my sheet still has info that I pasted from the previous month. I need to be able to identify any duplicate entries but the date at the end keeps it from being an exact duplicate because the date has changed. The formula that I'm using right now is =IF(COUNTIF($A$2:$A$150,A115)1,"Duplicate","") this will give me the word Duplicate in the rows where there is an exact match but I still need to watch carefully to be able to keep the old data weeded out. I've tried variations with RIGHT, LEFT , and , LEN but haven't had any success. I need to be able to ignore the last 6 char "(FEB 3)" in the cell that contains the [ICSC-UBS (FEB 3)] in order for there to be an exact match. Does anybody have any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicate entries
I definitely like being able to do it with a formula since it does it
automatically but if I understand the =LEFT(A1,10) correctly it will return the first 10 chars starting from the left side. The only problem with that is right now there about 250 headings for the data not just ICSC-USB. They range in length from 10 to 30+ chars not including the date. Thats why I was trying to just plug something in the Duplicate formula that would ignore the (FEB 3). "Dave F" wrote: Assuming something like [ICSC-UBS always precedes the (Feb 3)] create a helper column that extracts that left portion of the text string and dedupe based on that. =LEFT(A1,10) should do it. Post back if you have questions. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "mmcap" wrote: I have to copy data from an excel sheet on a website to my excel sheet. The company that I get the data from doesnt separate the DATE from the name of the data e.g. [ICSC-UBS (FEB 3)] is in one cell. When I paste the data from the website my sheet still has info that I pasted from the previous month. I need to be able to identify any duplicate entries but the date at the end keeps it from being an exact duplicate because the date has changed. The formula that Im using right now is =IF(COUNTIF($A$2:$A$150,A115)1,"Duplicate","") this will give me the word Duplicate in the rows where there is an exact match but I still need to watch carefully to be able to keep the old data weeded out. Ive tried variations with RIGHT, LEFT , and , LEN but havent had any success. I need to be able to ignore the last 6 char (FEB 3) in the cell that contains the [ICSC-UBS (FEB 3)] in order for there to be an exact match. Does anybody have any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicate entries
I didnt know about cntl+H, thats great info to have but unfortunately I
need to keep the dates for future reference. Thanks for the effort! "shail" wrote: Else, you can use Ctrl + H (to find and replace) 1. Select the rows over where you want to remove FEB 3. 2. Press Ctrl + H. 3. On the first box enter (FEB 3) and on the second box don't enter anything. And press Replace All button. So you will get rid of (FEB 3). Hope this helps Thanks, Shail On Feb 5, 9:28 pm, mmcap wrote: I have to copy data from an excel sheet on a website to my excel sheet. The company that I get the data from doesn't separate the DATE from the name of the data e.g. [ICSC-UBS (FEB 3)] is in one cell. When I paste the data from the website my sheet still has info that I pasted from the previous month. I need to be able to identify any duplicate entries but the date at the end keeps it from being an exact duplicate because the date has changed. The formula that I'm using right now is =IF(COUNTIF($A$2:$A$150,A115)1,"Duplicate","") this will give me the word Duplicate in the rows where there is an exact match but I still need to watch carefully to be able to keep the old data weeded out. I've tried variations with RIGHT, LEFT , and , LEN but haven't had any success. I need to be able to ignore the last 6 char "(FEB 3)" in the cell that contains the [ICSC-UBS (FEB 3)] in order for there to be an exact match. Does anybody have any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding duplicate entries
Ive been trying different things but I dont understand why this formula
doesnt work. =IF(COUNTIF($A$2:$A$150,LEFT(A1,LEN(A1)-10)),"Duplicate","") "Dave F" wrote: Assuming something like [ICSC-UBS always precedes the (Feb 3)] create a helper column that extracts that left portion of the text string and dedupe based on that. =LEFT(A1,10) should do it. Post back if you have questions. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "mmcap" wrote: I have to copy data from an excel sheet on a website to my excel sheet. The company that I get the data from doesnt separate the DATE from the name of the data e.g. [ICSC-UBS (FEB 3)] is in one cell. When I paste the data from the website my sheet still has info that I pasted from the previous month. I need to be able to identify any duplicate entries but the date at the end keeps it from being an exact duplicate because the date has changed. The formula that Im using right now is =IF(COUNTIF($A$2:$A$150,A115)1,"Duplicate","") this will give me the word Duplicate in the rows where there is an exact match but I still need to watch carefully to be able to keep the old data weeded out. Ive tried variations with RIGHT, LEFT , and , LEN but havent had any success. I need to be able to ignore the last 6 char (FEB 3) in the cell that contains the [ICSC-UBS (FEB 3)] in order for there to be an exact match. Does anybody have any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Duplicate Entries | Excel Discussion (Misc queries) | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Need help with finding duplicate entries | Excel Worksheet Functions | |||
Finding duplicate cell entries in a column of data | Excel Discussion (Misc queries) | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions |