Forum Discussion

phuiyee's avatar
phuiyee
Copper Contributor
Jan 16, 2026

Random Contractor Selection List

Hi, I would like to set up an automated random contractor list for tender purpose - is this possible?

For example, I have Contractor 1 - Contractor 10, I would like to choose 2 contractors for each tender/quotation request randomly. Currently, it is done manually which is time consuming and could not ensure impartiality in selection - if I can setup the contractor rota to generate random selection, it would be wonderful. Please can anyone advise and help with the correct way to setup my excel sheet? Many thanks

Formulas and Functions

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 17, 2026

    I have attempted to use a pseudo-random number generator that I had on file from Lori Miller.  The advantage of this is that the values are not volatile so it is possible to add contracts without reassigning existing tender invitations.

    My worksheet formula is

    = INVITEDλ(contractorLst, COUNTA(Contracts))

where

INVITEDλ
= LAMBDA(contractors,n,
    LET(
      rand,         WRAPROWS(PseudoRandλ(2*n, seed), 2),
      m,            COUNTA(contractors),
      selection1,   1 + INT( m    * CHOOSECOLS(rand, 1)),
      selection2,   1 + INT((m-1) * CHOOSECOLS(rand, 2)),
      selections,   HSTACK(selection1, selection2 + (selection2>=selection1)),
      invited,      INDEX(contractorLst, selections),
      invited
    )
);

    The random number generator derived from Lori is

    PseudoRandλ
= LAMBDA(length, [seed0],
//  Written by Lori Miller
    LET(
        seed, IF(ISOMITTED(seed0), 123456789, seed0),
        case, SEQUENCE(length, , , 0) * {13, -17, 5},
        rand, SCAN(seed, case, LAMBDA(s, i, BITXOR(s, BITAND(BITLSHIFT(s, i), 2 ^ 32 - 1)))),
        TAKE(rand,,-1) / (2^32)     
    )
);

     

     

  • IlirU's avatar
    IlirU
    Brass Contributor
    Jan 17, 2026

    Or you can use a formula like this:

    ="Contractor " & TAKE(UNIQUE(RANDARRAY(10,, 1, 10, TRUE)), 2)

    Press F9 to randomly find the two different contractors.

    Hope this helps.

    IlirU

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 17, 2026

    Hi

    One option with Excel 365/Web (you did not mention the version you use/run). With [Contractor ID]s from 1 to 10 in A2:A1 and their Names in B2:B11:

    in D2 above:

    =XLOOKUP( TAKE( UNIQUE( RANDARRAY(10,,1,10,TRUE) ), 2 ), A2:A11, B2:B11 )

     

Resources