Finding duplicate entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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
 
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. That’s why I
was trying to just plug something in the “Duplicate†formula that would
ignore the (FEB 3).

Dave F said:
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 said:
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?
Click to expand...
Click to expand...
 
I didn’t know about cntl+H, that’s great info to have but unfortunately I
need to keep the dates for future reference. Thanks for the effort!

shail said:
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

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?
Click to expand...
Click to expand...
 
I’ve been trying different things but I don’t understand why this formula
doesn’t work.
=IF(COUNTIF($A$2:$A$150,LEFT(A1,LEN(A1)-10)),"Duplicate","")


Dave F said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top