Hi, looking for soem help, I have a spreadsheet that generates randon
numbers, I want to check against a lsit of numebrs to see if the random
number generated have already been used. I can identify the number in a list,
however I am looking for a formula to highlight that the number has already
been used.

I was thinking of putting a 1 in the column beside the number in the list as
it appears so that I know it has been used before. Obviously if I jsut use a
formula that will update the next time a new number is generated.

I can do this very easily with a macro however for this problem I cannot use
a macro I need to use formula. Really looking for formula that pastes a value
into a cell if such a thing exists ?

One way,

Say your previously used numbers are in column B and your proposed new
number is in A1. Try this formula

=IF(COUNTIF(B1:B100,A1),"Already used","")

Mike

"AMH" wrote:

Hi, looking for soem help, I have a spreadsheet that generates randon
numbers, I want to check against a lsit of numebrs to see if the random
number generated have already been used. I can identify the number in a list,
however I am looking for a formula to highlight that the number has already
been used.

I was thinking of putting a 1 in the column beside the number in the list as
it appears so that I know it has been used before. Obviously if I jsut use a
formula that will update the next time a new number is generated.

I can do this very easily with a macro however for this problem I cannot use
a macro I need to use formula. Really looking for formula that pastes a value
into a cell if such a thing exists ?

Hi Mike,

This is sort of where I was, this will tell me if the number is contained
within the list or not, however if it is not in the list it will not add it
to the list of already used number, or mark it within a list.

I'll try an example because this is difficult to explain
Random number is between 1 and 10
My potential solution was to the list of 1 to 10 and then in an adjacent
column add a 1 if the number had been selected i.e.

List Selected
1 1
2
3
4 1
5
6
7
8 1
9
10

Therefore is numbers 1, 4 or 8 were generated then the sheet woud tell you
the number had already been selected, however if 2, 3, 5, 6, 7, 9 or 10 were
generated then these would be a new number, but how do I mark them with a 1,
if I use a formula in the selected field it will change next time the sheet
opens and a new number is generated.

An alternative way woudl be to add to the list of selected numebrs as they
are generated, i.e. as per above

List
1
4
8

Then excel generates 6 (which has not been used before) thus 6 needs to be
added to the list before the sheet is shut down.

Again I stress I can do this with a macro, however I cant use code for this
solution and thus need to try with formula

Any thoughts ?

"Mike H" wrote:

One way,
8
Say your previously used numbers are in column B and your proposed new
number is in A1. Try this formula

=IF(COUNTIF(B1:B100,A1),"Already used","")

Mike

"AMH" wrote:

Hi, looking for soem help, I have a spreadsheet that generates randon
numbers, I want to check against a lsit of numebrs to see if the random
number generated have already been used. I can identify the number in a list,
however I am looking for a formula to highlight that the number has already
been used.

I was thinking of putting a 1 in the column beside the number in the list as
it appears so that I know it has been used before. Obviously if I jsut use a
formula that will update the next time a new number is generated.

I can do this very easily with a macro however for this problem I cannot use
a macro I need to use formula. Really looking for formula that pastes a value
into a cell if such a thing exists ?