+ Reply to Thread
Results 1 to 11 of 11

Remove duplicates from one dimensional array

  1. 06-28-2010, 10:04 AM #1
    asha3010
    asha3010 is offline
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Remove duplicates from one dimensional array

    Greetings,

    Attached is a code which compares a list with a database to identify those values which are not present in the database. The values are carried in a single dimensional array rUnique. However, some of the values within the array maybe repeated. I would like to remove any duplicates from the array.

    I did see a couple of posts on the subject but was not able to adapt for my use. Would be grateful for any assistance.

    Let me know if I need to post a sample file.

    Many thanks
    Asha 


    Please Login or Register  to view this content.
    Last edited by asha3010; 06-29-2010 at 05:09 AM.
    Reply With QuoteRegister To Reply
  2. 06-28-2010, 10:20 AM #2
    Bob Phillips
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Remove duplicates from one dimensional array 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 06-28-2010, 10:22 AM #3
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Remove duplicates from one dimensional array 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 06-28-2010, 12:44 PM #4
    asha3010
    asha3010 is offline
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Remove duplicates from one dimensional array

    Thanks for both your replies. Unfortunately I get the following errors. Any ideas why this is happening?

    Bob: I get a Run-time error 5 : Invalid procedure call or argument with the IsError line highlighted.

    snb    : I get a Run-time error 424 : Object required.

    Thanks
    Asha
    Last edited by asha3010; 06-28-2010 at 12:48 PM.
    Reply With QuoteRegister To Reply
  5. 06-28-2010, 01:19 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Remove duplicates from one dimensional array

    I'm not clear on your intent, but maybe this:
    Please Login or Register  to view this content.
    Note that a reference is required to Microsoft Scripting Runtime.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  6. 06-28-2010, 03:19 PM #6
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Remove duplicates from one dimensional array 

    Please Login or Register  to view this content.
    PS. Why doesn't format (color, etc) work between code-tags ??
    Reply With QuoteRegister To Reply
  7. 06-28-2010, 03:24 PM #7
    asha3010
    asha3010 is offline
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Remove duplicates from one dimensional array

    Hi shg,

    Thanks for your response.

    I have attached my sample file. The macro I currently have compares the values in Col G of sheetTest with Col G of sheetDatabase and checks if any of the names in sheetTest exists in sheetDatabase. The nonduplicate values are returned in an array, which in this case would be, ASH, GSH, ASH. Since ASH repeats itself in the array, I would like to remove any repetitive values from the array. Therefore, the final result should be ASH, GSH.

    When I used the UDF unique that you provided, I get a result of 4, which I believe is the count of the unique list within sheetTest itself.

    Thanks again
    Asha
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 06-28-2010, 03:27 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Remove duplicates from one dimensional array

    Sorry for being slow on the uptake. You want the values in test col G that don't appear in Database col G ...
    Reply With QuoteRegister To Reply
  9. 06-28-2010, 03:40 PM #9
    asha3010
    asha3010 is offline
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Remove duplicates from one dimensional array

    Don't be - I probably was not clear enough.

    Yes, I want a unique list of values in Col G of Test that do not appear in Col G of Database.

    Thanks for your continued support.
    Asha
    Reply With QuoteRegister To Reply
  10. 06-28-2010, 03:56 PM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Remove duplicates from one dimensional array

    New Sub x:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 06-29-2010, 05:09 AM #11
    asha3010
    asha3010 is offline
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Remove duplicates from one dimensional array

    Hi shg,

    Many thanks for your code. That works.

    Hi snb,

    Thanks for your reply. How do I access the values that the code generates?

    Regards
    Asha
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1