Check column if same number exists

kmjones

New Member
Joined
Jun 15, 2005
Messages
44
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?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,855
Hi Krissy

Please try

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

Hope this helps
PGC
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

this thread is about unique random numbers in one column
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
 

macajm

New Member
Joined
Oct 21, 2005
Messages
32
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,471
Members
409,884
Latest member
Msinmath
Top