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?
 

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,884
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
 

Forum statistics

Threads
1,141,740
Messages
5,708,235
Members
421,553
Latest member
Geeyj

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
Top