combining vlookup with random function

[erniepoe]

Hi all,

I'm using the =INDEX($A:$A,RANDBETWEEN(2,COUNTA($A:$A)),1) function to find a random business name in column A. The formula is located in column E I use this 7 times to find 7 random names (they are sometimes the same name but I don't know of a workaround other than to just keep refreshing).


I want to then use a lookup function that will lookup that name in column A, and then return A random values in columns B (date) given the name match in column A, and then also include the values of columns C and D (first and last name, respectively), and put them in columns F, G, and H.

I think this just requires a combination of the vlookup and the above random formula, but I'm open to any ideas.

Thanks for you help.

 

[scottylad2]

Hi all,

I'm using the =INDEX($A:$A,RANDBETWEEN(2,COUNTA($A:$A)),1) function to find a random business name in column A.

Are there duplicate company names are all they unique? If Unique, is the list static or are you adding to it, is that why your using Counta?

The formula is located in column E I use this 7 times to find 7 random names

Will you require the formula to be housed in E1:E7 or are you just using E1 7 times over

I want to then use a lookup function that will lookup that name in column A, and then return A random values in columns B (date) given the name match in column A, and then also include the values of columns C and D (first and last name, respectively), and put them in columns F, G, and H.

I'm assuming all the other data will be returned from the Vlookup once you have the company names, the answer to the first 2 queries wil help determine the solution.

 

[erniepoe]

Hi Scotty, to answer your questions

1) the list of names in column A contains duplicates, but not sure how to find 7 unique names from the list other than just refreshing the random formula until I get them.

2) The formula will be housed in cells E1:E7 to find (eventually) the 7 unique company names.

3) The other data will be returned after using the lookup to determine the value from column B (date), and then return the corresponding values in columns C and D from that date.

Thanks so much.

 

[scottylad2]

Hi Scotty, to answer your questions

1) the list of names in column A contains duplicates, but not sure how to find 7 unique names from the list other than just refreshing the random formula until I get them.

2) The formula will be housed in cells E1:E7 to find (eventually) the 7 unique company names.

3) The other data will be returned after using the lookup to determine the value from column B (date), and then return the corresponding values in columns C and D from that date.

Thanks so much.

Is it possible for you to create a unique list? If you were able to create a unixe list, say usng the advanced filter and with both copy to a new location and unique items only checked, then it's easy enough to combine the Rand() function with almost no chance of it ever selection and duplicating from that list. There's also several array formulas that can extract the unique list but some of them are quite resource hungry.

Using the randbetween will mean theres a chance that from 7 selections,m even if you are using a unique list Randbetween may select the same business twice. Will the Business names be added to?

 

[scottylad2]

I've created a unique list from approx 100 which resulted in the extracted list in column C
Column D houses the rand function, you could wrap that in an If to say if the column to the left of it is empty then leave blank, if there's a busines name insert Rand() the list is created using the 7 largest numbers created by the Rand and extracting. You could hide the Extract and Rand Columns elswhere, because the rand is creating numbers less than 1 and because there are so many paces after the decimal it will almost never select the same number

Sheet2

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:95.2px;"><col style="width:100.8px;"><col style="width:60.8px;"><col style="width:204px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; border-style:solid; border-width:1px; border-color:#000000; ">Extracted List</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Rand Number</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Companys Selected Using Formula</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.25906685</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Business 16</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.315768975</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Business 4</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.660161615</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Business 6</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.933849251</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Business 19</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.586137309</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Business 13</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.927240843</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Business 8</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.280398041</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Business 24</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.814375158</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.69945085</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">Vlookup selected from Above List</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 10</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.279388418</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 11</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.688278548</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 12</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.015262739</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 13</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.85210623</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 14</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.157783986</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 15</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.632093389</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 16</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.958503146</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 17</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.624976332</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 18</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.341019097</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 19</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.896536006</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 20</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.254838313</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 21</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.494759233</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 22</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.14875847</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 23</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.017994367</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 24</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.813809199</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.465468596</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">27</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 26</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.299404708</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 27</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.180384761</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 28</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.347189251</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 29</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.270733578</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">31</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Business 30</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">0.471448006</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td><td style=" border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>F2</td><td>=INDEX($C$2:$C$31,MATCH(LARGE($D$2:$D$31,ROW(A1)),$D$2:$D$31,0))</td></tr></tbody></table></td></tr></tbody></table>

Sheet2

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:100.8px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>D</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">0.25906685</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>D2</td><td>=RAND()</td></tr></tbody></table></td></tr></tbody></table>
The Formula in F2 should be dragged down the reqired number of rows
Excel tables to the web >> Excel Jeanie HTML 4

hope this helps

 

[Marcelo Branco]

Hi,

Assuming
Sheet-name as Sheet1 (can adjust)
Names in column A beginning in row 2

To deal with duplicates, maybe the macro below that generates 7 random names in E1:E7

(try it on a test-workbook)

Sub get7Random()
    Dim MyCol As New Collection
    Dim lastRow As Long, i As Long, lin As Long, aIndex As Long
    Dim wk As Worksheet
    
    Set wk = Sheets("Sheet1") '<---Adjust to suit
    
    With wk
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For i = 2 To lastRow
            On Error Resume Next
            MyCol.Add Range("A" & i).Value, CStr(Range("A" & i).Value)
        Next i
        On Error GoTo 0
        
        lin = 1
        For i = MyCol.Count To MyCol.Count - 6 Step -1
            aIndex = Application.RandBetween(1, i)
            Range("E" & lin) = MyCol(aIndex)
            MyCol.Remove aIndex
            lin = lin + 1
        Next i
            
    End With
End Sub

HTH

M.

 

[erniepoe]

Thanks Scotty, this was very helpful. One thing I don't think I mentioned is that each company has an associated list of dozens of dates, and once a company is chosen at random, i would then like to choose a random date (well, 10 random dates if possible) from column B associated with the company name in column A. Then, after the random date is chosen, the corresponding first and last name from that date that go in columns C and D, respectively.

But your solution was very helpful to get the unique names, so thanks a bunch.

 

[Marcelo Branco]

oops...

small corrections - forgot .Range(...)

Sub get7Random()
    'Generates 7 random names in E1:E7
    'Duplicates in column A
    Dim MyCol As New Collection
    Dim lastRow As Long, i As Long, lin As Long, aIndex As Long
    Dim wk As Worksheet
    
    Set wk = Sheets("Sheet1") '<---Adjust to suit
    
    With wk
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For i = 2 To lastRow
            On Error Resume Next
            MyCol.Add .Range("A" & i).Value, CStr(.Range("A" & i).Value)
        Next i
        On Error GoTo 0
        
        lin = 1
        For i = MyCol.Count To MyCol.Count - 6 Step -1
            aIndex = Application.RandBetween(1, i)
            .Range("E" & lin) = MyCol(aIndex)
            MyCol.Remove aIndex
            lin = lin + 1
        Next i
            
    End With
End Sub

M.

 

[erniepoe]

Thanks Scotty, this was very helpful. One thing I don't think I mentioned is that each company has an associated list of dozens of dates, and once a company is chosen at random, i would then like to choose a random date (well, 10 random dates if possible) from column B associated with the company name in column A. Then, after the random date is chosen, the corresponding first and last name from that date that go in columns C and D, respectively.

But your solution was very helpful to get the unique names, so thanks a bunch.