Excel: VLOOKUP + randomized data depending on the cell

excelnewbieneedhelp

New Member
Joined
Apr 6, 2018
Messages
3
Hello guys :)

Thanks for viewing my thread, I have some question regarding on using VLOOKUP+randomized data.

Sheet1

<code>branchID

BRANCH01
BRANCH02
BRANCH03
BRANCH04
</code> Sheet2

<code>employeeID

EMPLOYEE01 -- BRANCH01
EMPLOYEE02 -- BRANCH01
EMPLOYEE03 -- BRANCH02
EMPLOYEE04 -- BRANCH02
EMPLOYEE05 -- BRANCH03
EMPLOYEE06 -- BRANCH03
EMPLOYEE07 -- BRANCH04
EMPLOYEE08 -- BRANCH04


What I want for the result -
Sheet3


BRANCHID EMPLOYEEID

</code><code><code>BRANCH01 </code></code><code><code><code>EMPLOYEE01 </code>
<code><code><code><code><code>BRANCH01 EMPLOYEE02
BRANCH03 EMPLOYEE05
BRANCH03 EMPLOYEE06
BRANCH04 EMPLOYEE07
BRANCH01 EMPLOYEE02
BRANCH01 EMPLOYEE01
BRANCH02 EMPLOYEE03

which that the employeeID is depending on branchid (and it will randomized the value between(EMPLOYEE01 & EMPLOYEE02) whenthe branchid is BRANCH01, sames goes to BRANCH02 (EMPLOYEE03 &EMPLOYEE04) , BRANCH03 (EMPLOYEE05 & EMPLOYEE06) , BRANCH04(EMPLOYEE07 & EMPLOYEE08)</code></code></code></code></code></code><code><code><code><code><code>
</code></code></code></code></code>


</code>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,806
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

Perhaps something like this:

E3: =INDEX($B$2:$B$10,AGGREGATE(15,6,(ROW(B$1:B$10)-ROW(B$1)+1)/(A$2:A$10=D3),RANDBETWEEN(1,COUNTIF(A$2:A$10,D3))))

But as always, it depends what you mean by "random". Perhaps in this case you want each of the employees 11, 33, 44, 77 and 99 to appear once each (i.e. not randomly), but in random order?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Branch ID</td><td style="font-weight: bold;text-align: center;;">Employee ID</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">A</td><td style="text-align: center;;">11</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Branch</td><td style="font-weight: bold;text-align: center;;">Random</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">B</td><td style="text-align: center;;">22</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">A</td><td style="text-align: center;;">33</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">B</td><td style="text-align: center;;">55</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">77</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">B</td><td style="text-align: center;;">66</td><td style="text-align: right;;"></td><td style="text-align: center;;">A</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">A</td><td style="text-align: center;;">77</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">B</td><td style="text-align: center;;">88</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">A</td><td style="text-align: center;;">99</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />
 
Last edited:

excelnewbieneedhelp

New Member
Joined
Apr 6, 2018
Messages
3
Welcome to the Forum!

Perhaps something like this:

E3: =INDEX($B$2:$B$10,AGGREGATE(15,6,(ROW(B$1:B$10)-ROW(B$1)+1)/(A$2:A$10=D3),RANDBETWEEN(1,COUNTIF(A$2:A$10,D3))))

But as always, it depends what you mean by "random". Perhaps in this case you want each of the employees 11, 33, 44, 77 and 99 to appear once each (i.e. not randomly), but in random order?

ABCDE
1Branch IDEmployee ID
2A11BranchRandom
3B22A11
4A33A44
5A44A44
6B55A77
7B66A44
8A77
9B88
10A99

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hello, Thanks for the response :D

sorry I don't think that is what I wanted.

i will explain more with pics ( sorry for poor english , not my main languages )

http://prntscr.com/j22ogn ( BRANCH sheet )

http://prntscr.com/j22oq5 ( Employee sheet)

http://prntscr.com/j22ow3 (Sheet that i need to combine both data (employee and branch) )


in the third picture, I used 'RANDBETWEEN' to random around 400 data between (BRANCH01 to BRANCH04), and I wanted to do the same to my employeeID too , but there is a requirement which that the EMPLOYEEID need to match with the BRANCHID
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,806
Office Version
  1. 365
Platform
  1. Windows
The solution will depend on exactly what you mean by "random"?

Expanding on my previous example:

D3: =INDEX(A$15:A$17,RANDBETWEEN(1,COUNTA(A$15:A$17)))
randomly selects branch A, B or C from the unique branch list. Note that using this formula A, B and C are equally likely to be selected. Is this OK? Or should we perhaps select Branch A three times as frequently as B or C, given that A has six employees, and B and C each have two employees?

E3: =INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW(B$1:B$11)-ROW(B$1)+1)/(A$2:A$11=D3),RANDBETWEEN(1,COUNTIF(A$2:A$11,D3))))
randomly selects an employee from the randomly selected Branch B. Note that for Branch C, employee 88 has been randomly selected four times, and employee 66 nil times. Is this OK? Or do you want "random" to mean something different?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;;">Branch ID</td><td style="font-weight: bold;text-align: center;color: #333333;;">Employee ID</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">11</td><td style="text-align: right;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;">Branch</td><td style="font-weight: bold;text-align: center;color: #333333;;">Random</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">33</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">44</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">55</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">66</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">77</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;color: #333333;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">99</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;color: #333333;;">00</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;color: #333333;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;color: #333333;;">Unique Branch ID</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,531
Messages
5,529,389
Members
409,870
Latest member
Well59
Top