0

I'm trying to generate a random number for each field in column "A" of my spreadsheet, and I have been trying to experiment with the following formula:

=LARGE(ROW($1:$1000)*NOT(COUNTIF($A$1:A4, ROW($1:$1000))), RANDBETWEEN(1,1000-ROW(A4)))

This is giving me the numerical values that I want, but is affecting both Columns "B" and "C" as well. What am I missing??? I just want it to affect column "A".

2
Contributors
8
Replies
19
Views
4 Years
Discussion Span
Last Post by Stuugie
1

This formula gives me 0s but only in column A. I can't see why you'd have problems with columns B and C unless the cells in those columns are referencing column A cells.

0

I'm getting the random values I need in column "A", but when I enter a value in (let's just say ) row 2, Column "B", and I hit the tab key...it automatically generates random numbers again for all of column "A". the same thing happens if I was to enter something in to row 3, Column "C". If I skip over to any fields under column "D" and tab out, nothing happens.

0

I've tried to do that, but it keeps telling me that my Excel spreadsheet can't be uploaded... "The filetype you are attempting to upload is not allowed" is all it tells me. Sorry, but I don't know of another way to attach it.

0

I should have also noted that the code needs to be entered in A2, rather than A1, as A1 is the heading row.

0

@STUUGIE: My apologies, I sent you in the wrong direction. I copied the code from the 4th cell in column "A" which is why you are getting the value of 0 in every cell. Here is the corrected version that should provide some active answers:

=LARGE(ROW($1:$1000)*NOT(COUNTIF($A$1:A1, ROW($1:$1000))), RANDBETWEEN(1,1000-ROW(A1)))

0

I'm pretty sure you can upload it if it is zipped. If you are still having problems and I haven't solved this for you, I would like to suggest a very good site for Excel and other MS Office problems:
Click Here. As it is I'll see what I can do for you.

0

Hi PDB1982, I don't think I can replicate what you need without a workkbook. Please check out the code cage and we'll be able to help you when you upload a workbook.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.