Check column if same number exists

kmjones

New Member
I am trying to verify that all of the random numbers i have generated in column 1 are different than the random numbers i have genereated in my column 2 on my worksheet. Is there a way to check this besides going through each value?

Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi Krissy

Code:
``=SUMPRODUCT(COUNTIF(A1:A10,B1:B10))=0``

Hope this helps
PGC

Hi,

Given that once you know this, your next question is likely to be '& how can I find out which ones they are?", a link to working with lists:

http://www.cpearson.com/excel/duplicat.htm

but with 2 lists in, eg, a1:b3:

SUM(--(A1:A3=TRANSPOSE(B1:B3)))

...entered with control + shift + enter, not just enter.

Hi,

what do you want to do when there are duplicates ?
if the answer is "try again", you might consider to generate uniques at once
see
http://www.mrexcel.com/board2/viewtopic.php?p=1112202

copying values from the column to the next one would be a solution for you
you won't need a check because it's 100% sure uniques

example
if you need 20 random numbers you could generate a column with 40 numbers and paste the second half to the next column

kind regards,
Erik

Assuming you have your random data pairs in columns A & B
Then in cell C1 type =1-ISNA(VLOOKUP(B1,A:A,0,FALSE))*1
and then copy this down the column for as many rows as you have pairs of random numbers in columns A and B
Then in say D1 put =SUM(C:C)=0
This will show true if all data is unique, or false if any value in column B matches any value in column A

Replies
7
Views
354
Replies
4
Views
296
Replies
5
Views
235
Replies
9
Views
151
Replies
10
Views
334

1,219,792
Messages
6,150,288
Members
450,949
Latest member
faizanmalik10

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.

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

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