? asked in Computers & InternetSoftware · 7 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

  • 7 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.

    • Login to reply the answers
  • Greg G
    Lv 7
    7 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.

    • Login to reply the answers
  • sak
    Lv 4
    3 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.

    • Login to reply the answers
  • 7 years ago

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

    • Login to reply the answers
Still have questions? Get your answers by asking now.