Remove duplicate entries from one column and output it into another column using formulas

This is relatively easy up to this part:

I want the formula to apply to the entire column i.e for the 1048576 rows.

That means you would potentially need 1048576 formulas to get the results.

1048576 of the formulas needed to do this would bring the file to it's knees!

Will you really have over 1 million rows of data?

Well the second sheet will have lesser number of rows than the first sheet since it will be a column with the duplicates removed. And each entry in the column of the first sheet will be repeated a minimum of 2 and a maximum of 15 times. On an average 5 times at least. But even then, I will need to extend the formula quite a bit in the second sheet. At least 2000 times.

Hi alrikvincent and welcome to the forum,

If you learn how to do Advanced Filters, there is a check box to only filter to Unique values. No Macros AND no formulas. You simply need to use a feature built into Excel..

See
http://office.microsoft.com/en-us/ex...010073943.aspx or
http://www.contextures.com/xladvfilter01.html

or Bing "Excel Advanced Filter Unique"

Try this and see if it's acceptable performance-wise.

Create a dynamic range so the formulas are a bit more efficient and not having to look through a bunch of empty cells.

Assume your data is on Sheet1 in column A starting at cell A2 downwards with no empty cells within the data range.

Goto the Formulas tab>Define Name
Name: MyRange (or whatever you want to call it)
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzz",Sheet1!$A:$A))

OK

Then, on Sheet2...

Enter this label in cell A1: Count Of Uniques

Enter this array formula** in cell B1. This will return the count of unique records in MyRange.

=SUM(IF(FREQUENCY(MATCH(MyRange,MyRange,0),ROW(MyRange)-MIN(ROW(MyRange))+1),1))

Now, let's get the list of unique entries from MyRange.

Enter this formula in cell A3:

=INDEX(MyRange,1)

Enter this array formula** in A4:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(MyRange,MATCH(0,COUNTIF(A$3:A3,MyRange),0))))

Copy down to a number of cells that is at least equal to the number in cell B1.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi,

Try the attached that has two Dynamic Named Ranges, an Event Macro and a Module Macro that does Advanced Filter on Sheet2.

This will solve your problem but uses Macros..... See if you can deal with an answer that works.

Originally Posted by alrikvincent

Dear Tony,

I tried doing what you told me. It doesn't seem to work.

Because the data in your file is NUMERIC but the sample data you posted is TEXT.

I wrote the formula to work on TEXT based on the sample data in your original post.

What type of data will you have? Is it text? Numeric? Could be both? Something else?

My method works on either text or numbers....

Have you given it a try yet?

Originally Posted by MarvinP

My method works on either text or numbers....

Have you given it a try yet?

Are you asking me?

If so, no, I haven't downloaded your sample file.

Originally Posted by alrikvincent

I am really sorry about the mix up. The data is numeric.

OK, that makes things a bit easier and just needs some minor tweaks.

Define the dynamic range as:

Name: MyRange
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(1E100,Sheet1!$A:$A))

On Sheet2 change the formula in B1 to:

=IFERROR(SUM(--(FREQUENCY(MyRange,MyRange)>0)),0)

Enter this array formula in A3:

=IF(ROWS(A$3:A3)>B$1,"",INDEX(MyRange,MATCH(0,COUNTIF(A$2:A2,MyRange),0)))

Note that the formula refers to cell A2. A2 must not contain a number that is also in MyRange.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to number of cells that is at least equal to the number in cell B1.

Here's your file with the updated formulas implemented:

Remove Duplicate Entries(1).xlsx

If using formuls brings the excel file to its knees like what Tony says, then I guess I will have no choice lol.

It depends on how much data you have and how many unique entries there are.

Try it and see if the performance is acceptable. If not, then you can go with the VBA method.

Good deal. Thanks for the feedback!

Please start your own thread.

It's against forum rules to post your question in someone else's thread.