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".
Jump to PostThis 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.
Jump to PostCan you provide a mock workbook with no private or sensitive data?
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.
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.
Can you provide a mock workbook with no private or sensitive data?
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.
I should have also noted that the code needs to be entered in A2, rather than A1, as A1 is the heading row.
@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)))
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.