Importing .txt file and removing duplicates....

[jack8400]

Hi all,

I have a .txt file containing around 20,000 email addresses. The problem is that there are a number of email addresses repeated more than once, so I want to import the file into excel and remove the dulplicates. I tried importing it but it doesn't gets imported in a single column and when I use 'Remove Duplicates' in the Data tab Excel either says no duplicates found or multiple columns selected when I select the imported data.

Please let me know if anyone has a solution for this. Thanks in advance.

 

[VBA Geek]

You would need to show what your txt file looks like to get help

 

[jack8400]

It just has lots of email addresses with no headers, that's it.

You would need to show what your txt file looks like to get help

 

[This_Guy]

Hey jack,

It just has lots of email addresses with no headers, that's it.

You would be surprised how unhelpful that comment is... Therefore, you will be mostly on your own for this one. I have included some guidance for you below.

Your first problem is that the email addresses aren't being imported into a single column. I'm not sure why that would be unless you are delimiting the text by something when you import the file? If you do, then I would recommend not doing that.

Otherwise, the text file may contain tab characters, and that's why your addresses are being imported into different columns. If that's the case, then go ahead and import your data and then refer to this article as it will be able to help you:
Quickly Fill Blank Cells in a Table [Reader Tip] | Chandoo.org - Learn Microsoft Excel Online

After getting all of your addresses into column A, then go to cell B1 and type in the following formula: =TRIM(CLEAN(A1)). Copy that formula all the way down to the bottom of your dataset in Column A. Copy and paste values.

Then, after the data has been standardized, you can run your Remove Duplicates based on the values in column B. Delete Column A and you're done...

I would imagine that you're going to need to google and resolve any issues that you encounter on your own, as we can't help you any further without some sample text/examples to work with.

Good Luck

 

[hiker95]

jack8400,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel.com | Excel Resources | Excel Seminars | Excel Products

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.