excelexcel-formulaexcel-2010

Generate a random list based on a main list (consisting two columns)

Salam Alaikum,

I want to generate a random list from a main list based on two columns as shown in this pictures. The genarated list values can be repeated and duplicated. the names and designation values are linked with each other. it does not need to be in any special order.and for names e.g. Name1 the designation must be Designation1. as shown in the output.

picture

Solution

  • Add a numerical column next to your input, starting from 1 and increasing by one. In a new column, generate random numbers from 1 to the maximum number from your input list. Use INDEX/MATCH to look up the names and designations.

    To generate an integer from 1 to the required maximum:

    =INT(RAND()*3)+1

    To look up the required values from the random integer:

    =INDEX($A$2:$A$4,MATCH(E2,C$2:$C$4))

    enter image description here

    enter image description here