Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Union/Intersect

  1. #1
    Guest

    Default

    Hi,
    I have a function that returns an array of unique items in a given range

    eg unique = FindUnique(A1:A200)

    Which is fine and dandy.. the problem is, I want an array of the non-unique items. Since Excel has union, intersect funcs.

    1. Is there a function that does subtract??

    ie something like .. non-unique = Application.Subtract(A1:A200, FindUnique(A1:A200)??

    Got the function from the web, and I have no programming experience to modify it.

    2. How do you populate items from an array into cells, got this little code here.. seems a bit slow

    for i = 0 to UBound(myArray)
    Range("A1").Offset(i, 0).Value = myArray(i)
    next i

    Thanks!

  2. #2
    Guest

    Default

    Errr... anyone??

  3. #3
    Guest

    Default

    OK.. let me try and rephrase the question..

    I have a table
    Name Account
    AAA 123
    BBB 345
    CCC 678
    DDD 910
    AAA 131
    BBB 415

    is there a function in excel that returns a range

    AAA 123
    AAA 134
    BBB 345
    BBB 415

    Am exploring this option as to detect duplicate applicantion forms.

    Thanks

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not following...where are the duplicates, and what do you want to do/not do with the duplicates? Maybe another example would be helpful.

    -Russell

  5. #5
    Guest

    Default

    Oh sorry... I will try to rephrase that again..

    In the example.. We have two duplicate names.. namely AAA and BBB.

    What I need is a function (I suspect its a VBA job) that find non-unique names, and then joins the non-unique name& corresponding account number so that it will return a range that looks something like the answer in the example

    I think it has something to do with Application.Union(...)

    Thanks

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would just basically sort your data. How about this:

    To the right of the last column, put the following formula and copy it down (for this example, we'll say that your data is in Cols A and B, rows 1 to 6, and this formula is in Column C - specifically, cell C1):

    =COUNTIF($A$1:$A$6,A1)

    (again copy this down).

    Now select the entire range (including column C) and go to Data-Sort, and sort by Column C descending, then by column A ascending. All of your non-unique entries will have values in Column C greater than 1.

    I think VBA is overkill for this, but let me know if that's what you really want and I'll see what I can come up with.

    Hope this helps,

    Russell

  7. #7
    Guest

    Default

    Well, I have to do this for about 100 sheets per day.. scan the sheets and generate all the duplicate names in a new sheet.. would appreciate some VBA/macros that can do this.. The steps you described is what I am doing right now.. just wondering if there's a better way to do it

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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