duplicates - user defined ranges - need help!

I'm not sure why you have a double loop, x in Rng1, y in Rng2. Let's say you have 40 cells in each range; then for cell 1 in range 1, you compare it to each of 40 cells in range 2; for cell 2 in range 1, you compare it to each of 40 cells in range 2; and so on. In other words, 40 x 40 = 1600 comparisons. If you only need to know if a duplicate exists, you could just use COUNTIF. As it's a two way comparison, you'd need to go through each cell in range 1 (using COUNTIF against range 2) and then each cell in range 2 (using COUNTIF against range 1). That would be 80 tests rather than 1600. As can be seen in the table below, this difference increases exponentially.

As you process each cell in each range, you know where it is, in that you can refer to x.row and y.row ... and use that as the position in the "related" column.

Maybe doesn't answer the question but it might make the process a little quicker.

It's also common practice when working with ranges, especially large ranges, to read them into arrays and loop through the arrays to make comparisons, etc. The position in the array will map to the position in the range.

	A	B	C	D
1	Number	Doubled	Squared	Comparison
2	10	20	100	20.00%
3	20	40	400	10.00%
4	30	60	900	6.67%
5	40	80	1600	5.00%
6	50	100	2500	4.00%
7	60	120	3600	3.33%
8	70	140	4900	2.86%
9	80	160	6400	2.50%
10	90	180	8100	2.22%
11	100	200	10000	2.00%
12	110	220	12100	1.82%
13	120	240	14400	1.67%
14	130	260	16900	1.54%
15	140	280	19600	1.43%
16	150	300	22500	1.33%
17	160	320	25600	1.25%
18	170	340	28900	1.18%
19	180	360	32400	1.11%
20	190	380	36100	1.05%
21	200	400	40000	1.00%
22	210	420	44100	0.95%
23	220	440	48400	0.91%
24	230	460	52900	0.87%
25	240	480	57600	0.83%
26	250	500	62500	0.80%
27	260	520	67600	0.77%
28	270	540	72900	0.74%
29	280	560	78400	0.71%
30	290	580	84100	0.69%
31	300	600	90000	0.67%

I would have to recode the whole thing from what you are saying.

The reason why there are two loops is because the first "x" loop cycles through the 1st set of data one at a time. each individual x is compared to the entire y population by using the second loop.

My goal is to make sure x doesn't appear anywhere in y. The two sets of data being compared are not necessarily the same size or order. To give you an idea of what data I am using, I have two patient lists. One list is from the current year. I need to make sure every patient on the current year list does not appear on the prior year list.

I cant imagine countif will work across worksheets and workbooks without a lot of error checking.

I think what i really need to figure out to make this work is how to get the x,y coordinates of what x in the selected range i am at. I can then point to it and change it without using offset (which only returns the cells value, not its coordinates). I will look into using arrays if you could point me in the right direction.

The following routines are relatively simple demonstrations of using COUNTIF to test for duplicates. The testing has been done with two ranges on the same worksheet. The first maps the output using the address of the duplicated cell. The second creates two lists in columns A and B. The second is probably safer if the two ranges could overlap.

For the demonstration, I have used two different sized ranges, one containing cells in a column, the other containing cells in a row. I'm guessing that, under normal circumstances, you would be comparing columns ... and the columns could be the same column but in different sheets or even workbooks.

The demonstration is not meant to demonstrate that capability although it should work in just the same way as comparing local ranges.

Routine 1

Please Login or Register  to view this content.

Routine 2

Please Login or Register  to view this content.

Regards, TMS