Excel: VLOOKUP + randomized data depending on the cell

[excelnewbieneedhelp]

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>

 

[StephenCrump]

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?

Book1
	A	B	C	D	E
1	Branch ID	Employee ID
2	A	11		Branch	Random
3	B	22		A	11
4	A	33		A	44
5	A	44		A	44
6	B	55		A	77
7	B	66		A	44
8	A	77
9	B	88
10	A	99
Sheet1

 

[excelnewbieneedhelp]

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?

	A	B	C	D	E
1	Branch ID	Employee ID
2	A	11		Branch	Random
3	B	22		A	11
4	A	33		A	44
5	A	44		A	44
6	B	55		A	77
7	B	66		A	44
8	A	77
9	B	88
10	A	99

<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

 

[excelnewbieneedhelp]

Need help on this

 

[StephenCrump]

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?

Book1
	A	B	C	D	E
1	Branch ID	Employee ID
2	A	11		Branch	Random
3	B	22		B	22
4	A	33		A	11
5	A	44		C	88
6	B	55		B	22
7	C	66		C	88
8	A	77		C	88
9	C	88		C	88
10	A	99		A	11
11	A	00		B	55
12				B	22
13				B	55
14	Unique Branch ID
15	A
16	B
17	C
Sheet1