Creating Random number generator but ignoring numbers given

[jromanexcel]

Hi
I have the following formula in A1
=VLOOKUP(Sheet1!$F$1,Sheet1!$B$2:$C$45,2,FALSE)
I want to add to the formula to check if the answer I get is the same as in E2,F2
If the answer of A1 is the same I need it to automatically change until the value is not the same as in E2,F2

Please help, pulling out my hair

 

[Eric W]

Welcome to the forum.

Based on your description, I can only assume that you have multiple matches in column B for F1, and that if the first match is in E2 or F2, return the next match, etc. If so, try:

Book1
	A	B	C	D	E	F
1	e					1
2		1	c		c	d
3		2	c
4		3	c
5		4	c
6		5	c
7		1	d
8		2	d
9		3	d
10		4	d
11		5	d
12		1	e
13		2	e
14		3	e
15		4	e
16		5	e
Sheet1

Cell Formulas
Range	Formula
A1	=IFERROR(INDEX($C$2:$C$45,AGGREGATE(15,6,(ROW($C$2:$C$45)-ROW($C$2)+1)/($B$2:$B$45=F1)/(COUNTIF($E$2:$F$2,$C$2:$C$45)=0),1)),"")

 

[jromanexcel]

Hi Eric

I tried the formula and doesn't seem to give me the result I want

I have two sheets

Sheet 1
has 3 Columns A,B,C with rows 2-45, A1,B1,C1 contains headings only

Column A (row 2-45) has formula =RAND()
Column B (row 2-45) has formula =RANK($A2,$A$2:$A$45)+COUNTIF(B$1:B1,B1)-1
Column C No formula only contains numbers 1-45
E1 and F1 contains the numbers 1 and 2 respectively

Sheet 2
C1 contains formula =VLOOKUP(sheet1!$E$1,sheet1!$B$2:$C$45,2,FALSE)
C2 contains formula =VLOOKUP(Sheet1!$F$1,sheet1!$B$2:$C$45,2,FALSE)
M1 AND N1 contains a predetermined value(fixed)

I now want C1 and C2 to look at M1 and N1 and if the value of C1 or C2 matches that of M1 or M2 it needs to automatically reproduce another random value until neither of C1 or C2 matches any of the fixed values in M1 or M2

 

[Eric W]

Try:

Book1
	C
1	24
2	7
Sheet2

Cell Formulas
Range	Formula
C1	=IF(COUNTIF(M1:N1,VLOOKUP(Sheet1!$E$1,Sheet1!$B$2:$C$45,2,FALSE)),INDEX(Sheet1!$C$2:$C$25,AGGREGATE(15,6,(ROW(Sheet1!$C$2:$C$45)-ROW(Sheet1!$C$2)+1)/(COUNTIF(M1:N1,Sheet1!$C$2:$C$25)=0),RANDBETWEEN(1,ROWS(Sheet1!$C$2:$C$25)-COLUMNS(M1:N1)))),VLOOKUP(Sheet1!$E$1,Sheet1!$B$2:$C$45,2,FALSE))
C2	=IF(COUNTIF(M1:N1,VLOOKUP(Sheet1!$F$1,Sheet1!$B$2:$C$45,2,FALSE)),INDEX(Sheet1!$C$2:$C$25,AGGREGATE(15,6,(ROW(Sheet1!$C$2:$C$45)-ROW(Sheet1!$C$2)+1)/(COUNTIF(M1:N1,Sheet1!$C$2:$C$25)=0),RANDBETWEEN(1,ROWS($C$3:$C$25)-COLUMNS(M1:N1)))),VLOOKUP(Sheet1!$F$1,Sheet1!$B$2:$C$45,2,FALSE))

I assume when you say you want a random value, you want a value from Sheet1!$C$2:$C$45. There is a small chance that C2 could get the same random value as C1. I could upgrade the C2 formula (lengthen it) to prevent that, or just hit F9 to recalculate. What exactly are you trying to achieve with this set up? There might be an easier way.

 

[jromanexcel]

Thanks Eric seems to be working ok, did pick up once or twice that C1 gives thes same number as N1,but i'm sure I can just tweek it.
I'm trying to create a random number generator (I know that there are plenty online), but it has to exclude certain numbers that i indicate or include certain numbers in the process of giving random numbers.

 

[Eric W]

It really shouldn't do that, so I looked at my formula again, and realized I had $C$2:$C$25 instead of $C$2:$C$45 in several places. See if you need to fix those, and if so, you shouldn't get the duplicate again.

 

[jromanexcel]

Thanks Eric, it worked you a genius

 

[jromanexcel]

Hi Eric it does what you said it will do it duplicate the numbers in C1 and C2. I have also changed it to D1 and D2 as when I alter the formula the C2 in sheet 1 and C2 in sheet 2 messes me around. So I have used D1 and D2 to copy and paste your formulas in. The rest works 100%

 

[Eric W]

To avoid getting duplicate values in D1 and D2, probably the easiest way is to put this formula in O1: =D1
Then in the D2 formula, change the M1:N1 to M1:O1 in 2 places. If you have something already in O1, then it's a bit tougher to look in multiple places for used values.