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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi Krissy

Please try

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

Hope this helps
PGC
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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