? asked in Computers & InternetSoftware · 8 years ago

How to generate 1 to 10 in Excel without repeating?

I have 10 cells in Excel that I wanted randomly number between 1 & 10.

Therefore each cell will feature a number from 1 to 10 inclusive.

Ideally looking NOT to use macro or any other cells.

4 Answers

  • 8 years ago
    Favorite Answer

    Here is one, not too elegant, method to do as you wish using a single formula. However, you will have to adjust your options to enable the use of circular references. This formula assumes the non-repeating numbers 1-10 will be returned in cells A1:A10.

    For Excel 2003, and prior:

    Go to Tools > Options and select the Calculation tab. Check the 'Iteration' checkbox. Click 'OK'.

    For Excel 2007/2010:

    Go to File > Options and select the Formulas menu item.

    Place a check mark in 'Enable iterative calculation' checkbox. Click 'OK'.

    In cell A1, enter the formula:

    =IF(COUNTIF($A$1:$A$10,A1)>1, RANDBETWEEN(1,10),A1)

    Fill the formula down through cell A10.

    Note: This formula will not recalculate when the Calculate event occurs, i.e. pressing F9. To create a new list of numbers in A1:A10, simply select cell A1 and drag the formula down through A10 again. Like I said, not too elegant. However, it does avoid VBA and other cells.

  • Greg G
    Lv 7
    8 years ago

    Do you care if the numbers repeat? If not, you can simply use RANDBETWEEN:

    =RANDBETWEEN(1, 10)

    copy that down from A1 to A10.

    If you want each number to be unique, use this in A2:

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

    Confirm with CTRL + SHIFT + ENTER, not just enter since this is an array formula.

    Copy down to A11.

  • sak
    Lv 4
    4 years ago

    #a million became into humorous, in spite of the undeniable fact that it did no longer truly stick to the subject count. i presumed #10 confirmed extra lyricism and that i enjoyed "incomplete like a play without narration". there have been some truly good similes in that one. #10 gets my vote.

  • 8 years ago

    If you want random, then you have to allow for repetition, otherwise, the last cell will be determined and not random.

Still have questions? Get your answers by asking now.