+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

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

    I been struggling to do this for sometime now.

    I have two sheets.

    I have one column in the first sheet which has duplicate entries. I want to keep this as it is.

    I want to create a new column in the second sheet, which is a copy of the column in the first sheet, but without the duplicate entries.

    I want to do all this using formulas and not macros, because I want the second sheet to update, as and when new entries are made in the first sheet.

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

    To further illustrate:

    Sheet 1 has column A which has the following entries

    Apple
    Orange
    Apple
    Grape
    Tomato
    Orange

    Sheet 2 should have the column A without the duplicates in column A of Sheet 1, which is:

    Apple
    Orange
    Grape
    Tomato

    Thank you very much in advance, for taking the time to help me out.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove duplicate entries from one column and output it into another column using formu

    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?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Remove duplicate entries from one column and output it into another column using formu

    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.
    Last edited by alrikvincent; 07-20-2013 at 10:23 PM.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: Remove duplicate entries from one column and output it into another column using formu

    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"
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove duplicate entries from one column and output it into another column using formu

    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.

  6. #6
    Registered User
    Join Date
    11-27-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Remove duplicate entries from one column and output it into another column using formu

    Dear Tony,

    I tried doing what you told me. It doesn't seem to work. I think I must be doing something wrong though I followed your instructions to the dot. In the second sheet, the unique count seems to be wrong. I have attached the worksheet showing what I did.

    Regards, Alrik


    Dear Marvin

    That way of finding the unique values is not useful in my case because, I want the unique values to be found automatically, without running a macro. So when I update the original series, the second series has to be updated as well.
    Attached Files Attached Files

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: Remove duplicate entries from one column and output it into another column using formu

    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.
    Attached Files Attached Files

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove duplicate entries from one column and output it into another column using formu

    Quote Originally Posted by alrikvincent View Post
    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?

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: Remove duplicate entries from one column and output it into another column using formu

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

    Have you given it a try yet?

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove duplicate entries from one column and output it into another column using formu

    Quote Originally Posted by MarvinP View Post
    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.

  11. #11
    Registered User
    Join Date
    11-27-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Remove duplicate entries from one column and output it into another column using formu

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

    Yes the macro does work. But I was looking for a formula way of solving it because I dont want the user to click anything before the sheet2 updates. I know I can make a macro to run whenever the sheet1 is updated. But that has its own disadvantages. If using formuls brings the excel file to its knees like what Tony says, then I guess I will have no choice lol.

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove duplicate entries from one column and output it into another column using formu

    Quote Originally Posted by alrikvincent View Post
    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.

  13. #13
    Registered User
    Join Date
    11-27-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Remove duplicate entries from one column and output it into another column using formu

    Hi Guys

    I used the filter function to achieve this in the end. Though the macros I have in workbook is some what less effective, I was able to work around it.

    This how I did it in the end:

    I had entries with duplicates in the row A1 of Sheet1

    I referenced these entries into A1 of Sheet2 So A2 of Sheet2 corresponded to A2 of Sheet1 and so on.

    Then used the Advanced Filted and produced only the uniques in sheet 2.

    This is similar to Tony's method but without the macros.

    Thanks for the help guys.

  14. #14
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove duplicate entries from one column and output it into another column using formu

    Good deal. Thanks for the feedback!

  15. #15
    Registered User
    Join Date
    07-09-2014
    Location
    Tabriz
    MS-Off Ver
    2010
    Posts
    5

    Re: Remove duplicate entries from one column and output it into another column using formu

    Hi dear Tony
    I did what u said and result was very good. But when I filter some cloume on source to change calculations, that filterd values didnot removed on formula u created. may u help me on this ...

  16. #16
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove duplicate entries from one column and output it into another column using formu

    Please start your own thread.

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Remove rows with duplicate values in one column based on value of another column
    By jolleyje in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-15-2013, 06:20 AM
  2. Replies: 2
    Last Post: 01-31-2013, 07:04 PM
  3. Replies: 0
    Last Post: 06-14-2012, 12:38 PM
  4. Remove cells from column 1 from a entries in column 2
    By Tim8w in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2011, 04:03 PM
  5. How to remove duplicate entries in column?
    By registrations@discountcosmeticsguide.com in forum Excel General
    Replies: 1
    Last Post: 06-24-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1