Union/Intersect

G

Guest

Guest
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top