removing duplicate words

**mandi** · 07-16-2008, 04:10 AM

Hello everybody,
I have the following problem: I have two lists of email addresses in one txt file, separated with semicolon. The task is to remove those of the addresses from the second list which figure in the first list. It needs not to be an excel solution. Thanks in advance.

Artur

**kostas** · 07-16-2008, 05:03 AM

Without having a sample file I can give the following suggestion:

Open the txt file with excel and it will probably put all the data in Column A.

Use Data->Text to Columns , select Delimited and then select Semicolon. Click Finish and you should get your data seperated in columns A (one email) and B (second email).

Then just type the following code in C1

Please Login or Register  to view this content.

Select C1, Go to Data->Filter->Autofilter

click the dropdown box and click on DUPLICATE

select all the rows and delete them.

Go to Data->Filter and click on Autofilter (to remove the tick) and you should be left with all the non-duplicated entries.

Regards,

K

**SamuelT** · 07-16-2008, 05:27 AM

Hi,

Following on from kosta's suggestion - if the duplicate email addresses aren't next to each other, you can use the following formula:

Please Login or Register  to view this content.

If you put this in C1, it will display only the unique email addresses in your range. Obviously change the range and cell references to fit your document.

HTH,

SamuelT

**mandi** · 07-16-2008, 05:46 AM

Kostas thank you very much. I'm not sure I get your solution, so let me restructure the problem.
I have two txt files (lets call them A.txt and B.txt) containing email addresses, and each of them is situated in a new line (one address per line, no semicolons this time). I have to somehow compare those files and delete those addresses in B.txt which are listed in A.txt. How to do it in excel? I imported A.txt and B.txt as two separete columns, used if (a1=b1;"Duplicate","") and then what? Those files have different length. An automate is needed that would search B.txt (or column B) across all lines for those of email addresses that are in any line of A.txt (or column A). Anyone? Thanks in advance.

**Simon Lloyd** · 07-16-2008, 06:01 AM

Why not upload a sample workbook?

**SamuelT** · 07-16-2008, 06:05 AM

Hello again,

OK - try the following. This formula will display only addresses that are unique in column B. Anything that also appears in column A is blank. Filter and copy as previously suggested.

Please Login or Register  to view this content.

SamuelT

**kostas** · 07-16-2008, 06:40 AM

Originally Posted by mandi

Kostas thank you very much. I'm not sure I get your solution, so let me restructure the problem.
I have two txt files (lets call them A.txt and B.txt) containing email addresses, and each of them is situated in a new line (one address per line, no semicolons this time). I have to somehow compare those files and delete those addresses in B.txt which are listed in A.txt. How to do it in excel? I imported A.txt and B.txt as two separete columns, used if (a1=b1;"Duplicate","") and then what? Those files have different length. An automate is needed that would search B.txt (or column B) across all lines for those of email addresses that are in any line of A.txt (or column A). Anyone? Thanks in advance.

Hi Mandi,

I am sorry my solution was not clear enough and didn't help you. In your first post you mentioned having two lists of emails in one txt file, seperated by a semicolon. That implied that you had the same length of emails in both lists. In that case my formula would have produced some solution.

Since you have two seperate text files with unequal length of email lists then you should use SamuelT's suggestion as mine won't work in that case.

Regards,

K.

P.S You might want to alter the $A$32 bit of SamuelT's formula to cover the full length of you A column.

**mandi** · 07-16-2008, 06:53 AM

Great thanks everybody.
I don't know I am stupid or something, because when apply SamuelT's formula:

=IF(COUNTIF($A$1:$A$32,B2)>0,"",B2 (I used B1 instead of B2)

my results look messed up (with 0s, etc).

I attach the xls file (note: I have Polish excel, so "if"="jeżeli" and "countif"="licz.jeżeli").

Should you have problems with opening xls file, I attach the mentioned source txt files as well.

Thanks in advance.

**SamuelT** · 07-16-2008, 07:14 AM

Hi,

Just one problem I can see with your documents. You're referencing starts ar B21 rather than B1. Change...

Please Login or Register  to view this content.

...to...

Please Login or Register  to view this content.

I've checked a few random addresses and it appears to be working fine. You're getting zeroes at the end because there's nothing in the B column for Excel to search for.

SamuelT