Thanks:  0
Likes:  0

1. 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. Errr... anyone??

3. 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. 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. 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

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

## User Tag List

#### Posting Permissions

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