Dear all,
I need to distribute 114 names across 10 columns and 3000 rows with the condition that no name is to be repeated per row across the ten columns.
Can I get some help with this question please?
Many thanks in advance.
T.
Dear all,
I need to distribute 114 names across 10 columns and 3000 rows with the condition that no name is to be repeated per row across the ten columns.
Can I get some help with this question please?
Many thanks in advance.
T.
I am not clear what you are asking, why not include an attachment , scaled down if necessary
do you wish to have a random sample of 10 names in each of 3000 rows? just making sure the same name doesn't appear twice?
so the same name will on average appear around 250 times or be it in different rows?
Thanks davsth for your reply. My apologies if my question was not clear enough. Yes, I have a pool of 114 names (A1:A114) in Sheet1. I need to randomize those names in Sheet2 across 10 columns (A:J) and deep down to 3000 rows. The condition of randomizing here is that no row of those 3000 rows should have a name duplicated in its 10 cells across the 10 columns.
I hope I made it a bit clearer.
Once again, thank you.
T.
I would use the analysis tools add in and create 20 variables and 3000 rows from a uniform distribution between 1 and 115
then in the next 20 columns truncate the values int(a1) etc
then leave a column blank (AO)
so in ap2
=(COUNTIFS($U2:U2,U2,$U2:U2,"<>115")=1)*1+AO2
then finally to your results in the first cell you want your master list in
=INDEX($U2:$AN2,MATCH(COLUMN(A2),$AP2:$BI2,0))
where u2:an2 contain the 20 columns of potential values
If needed you could then lookup the numbers to return names from this
hopefully a start
Another Option :-
Regards MickPlease Login or Register to view this content.
Marvellous Mick, really marvellous. I spent the entire day trying to solve this problem which you solved in a few seconds with your genius code.
I'm more than thankful to you Mick, please accept my best regards.
T.
You're welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks