Random name generator without repeats?

[Kaliwa]

Hi, I got a list of my employees from A1:A72, we got a game where in I need to display a single employee name without being repeated. So, that particular employee can perform the task.

Or to be more exact the list is already jumbled can I display a single employee name one after the other in that jumbled order?

Thank you,
Kaliwa

 

[JamesCanale]

I'd do something like this:

MrExcelPlayground6.xlsx
	A	B	C	D
1	Employee 01	0.456825		Employee 21
2	Employee 02	0.422956		Employee 71
3	Employee 03	0.414224		Employee 04
4	Employee 04	0.07124		Employee 59
5	Employee 05	0.58179		Employee 52
6	Employee 06	0.854596		Employee 62
7	Employee 07	0.974352		Employee 69
8	Employee 08	0.746346		Employee 29
9	Employee 09	0.131006		Employee 19
10	Employee 10	0.985354		Employee 09
11	Employee 11	0.57414		Employee 64
12	Employee 12	0.993247		Employee 47
13	Employee 13	0.620336		Employee 57
14	Employee 14	0.768704		Employee 50
15	Employee 15	0.585094		Employee 26
16	Employee 16	0.547376		Employee 63
17	Employee 17	0.321011		Employee 36
18	Employee 18	0.517291		Employee 23
19	Employee 19	0.111907		Employee 46
20	Employee 20	0.322017		Employee 25
21	Employee 21	0.016081		Employee 51
22	Employee 22	0.455375		Employee 32
Sheet4

Cell Formulas
Range	Formula
B1:B72	B1	=RANDARRAY(72)
D1:D72	D1	=SORTBY(A1:A72,B1#)
Dynamic array formulas.

 

[Kaliwa]

I'd do something like this:

MrExcelPlayground6.xlsx
	A	B	C	D
1	Employee 01	0.456825		Employee 21
2	Employee 02	0.422956		Employee 71
3	Employee 03	0.414224		Employee 04
4	Employee 04	0.07124		Employee 59
5	Employee 05	0.58179		Employee 52
6	Employee 06	0.854596		Employee 62
7	Employee 07	0.974352		Employee 69
8	Employee 08	0.746346		Employee 29
9	Employee 09	0.131006		Employee 19
10	Employee 10	0.985354		Employee 09
11	Employee 11	0.57414		Employee 64
12	Employee 12	0.993247		Employee 47
13	Employee 13	0.620336		Employee 57
14	Employee 14	0.768704		Employee 50
15	Employee 15	0.585094		Employee 26
16	Employee 16	0.547376		Employee 63
17	Employee 17	0.321011		Employee 36
18	Employee 18	0.517291		Employee 23
19	Employee 19	0.111907		Employee 46
20	Employee 20	0.322017		Employee 25
21	Employee 21	0.016081		Employee 51
22	Employee 22	0.455375		Employee 32
Sheet4

Cell Formulas
Range	Formula
B1:B72	B1	=RANDARRAY(72)
D1:D72	D1	=SORTBY(A1:A72,B1#)
Dynamic array formulas.

Thanks for the reply @JamesCanale !! Can you make it to one(single) display of emp so that if I click on F9 Or any other key for 72 times. I need all the employees names to be displayed without being repeated. I'm little bit new to excell...

Thank you

 

[JamesCanale]

Randarray is volatile, so any change will refigure the whole random array and you couldn't expect no repeats. I can't think of a good way to do what you want.

 

[Kaliwa]

Randarray is volatile, so any change will refigure the whole random array and you couldn't expect no repeats. I can't think of a good way to do what you want.

Makes sense, thank you @JamesCanale

Thanks,
Kaliwa.

 

[Kaliwa]

I just wanna know @JamesCanale Can you think of a solution to display a single name one after the other in an order from 1-72??

 

[JamesCanale]

It's all in the details, but that would be an easy job for VBA. Something like take one item after another from a long list and put it in a specific cell and then do something - like print the form each time.

 

[Kaliwa]

Oh, thank you!! Will look into VBA then. Any heads up on what particular topic I should cover to know this work done.. Also... Thanks for all the replies @JamesCanale

Thanks,
Kaliwa

 

[JamesCanale]

It depends on what you want to do with these sheets when the new names get put into them. Print, email, create new sheets tabs for each person...

Otherwise, you need to be able to have VBA find the size of the array (or you can key it into the code), you'll need to make a loop, and access existing cells in excel.

Start with recording macros to see how VBA does things.

 

[Eric W]

If you put the RANDARRAY formula in B1 as shown, then manually sort columns A:B by column B, you won't need to worry about it resorting the list by a volatile function. Then to display the names one at a time, I'd suggest opening another sheet. Add a spin button. (Go to the Developer tab. If you don't see it, click File > Options > Customize Ribbon > check the Developer button on the right. Then on the Developer tab, click Insert > Spin Button (4th icon, top row). Use the mouse to "paint" it on the sheet where you want it. Then right click the button, select Format Control. Set the minimum value to 1, and the maximum value to 72. Pick a cell, like A1, for the Cell link. Then put this formula somewhere on that sheet:

=INDEX(Sheet1!$A$1:$A$72,A1)

Then as you click on the Spinner button, it will go through the list of names. This way you don't need VBA.

Hope this helps!