Choosing a set of random values from one column where another column matches a certain criteria
up vote
0
down vote
favorite
I'm producing an Excel report where I need to input a randomised selection from a data column, but to omit the human element of selection (for audit purposes).
The column names are "Date", "Staff Name" and "Cad Number".
The "Cad number" column is specific to the "staff member" on a particular date.
Is it possible to enter a command for the report to select 5 random "cad numbers" per "staff member"?
microsoft-excel
edited Aug 14 at 11:36
robinCTS
3,99941527
asked Aug 14 at 10:20
david
1
|
show 1 more comment
up vote
0
down vote
favorite
I'm producing an Excel report where I need to input a randomised selection from a data column, but to omit the human element of selection (for audit purposes).
The column names are "Date", "Staff Name" and "Cad Number".
The "Cad number" column is specific to the "staff member" on a particular date.
Is it possible to enter a command for the report to select 5 random "cad numbers" per "staff member"?
microsoft-excel
edited Aug 14 at 11:36
robinCTS
3,99941527
asked Aug 14 at 10:20
david
1
Hi-I've tried to use the rand formula but can only use it specific to one column i.e. the "cad number" column to give 5 random numbers. I can't/or don't have the relevant knowledge to actually link the name column to the cad number column to carry out this function. Attempted a macro but without the formula or array to carry out the command i cant get it going either
– david
Aug 14 at 10:27
Please edit your answer, and add the information from your comment, While you're at it please add a screenshot of some sample data and/or a formatted table of the data (useCtrl+Kor click the { } icon).
– robinCTS
Aug 14 at 10:33
For start.. do=RANDBETWEEN(1,5)and share your results.. || Some info are missing from your question. Like is the staff number repeated? + is the card number are allowed to be repeated? + does each staff will only have 1 card or multiple cards? || A sample data would help to clarify your Q
– p._phidot_
Aug 14 at 10:35
OK-I will try add some test data but for starters I have a set number of staff names i.e. 50 names with each having approx 300 cad numbers against their name which for audit purposes I would like excel to randomise down to 5 random cad numbers per staff name. the '=randbetween(1,5)' is good if I only want one specific staff name but I want excel to do this for the complete staff name column. Again hopefully I am making sense and thank you for the help so far.
– david
Aug 14 at 10:49
staff name cad number Bill 1607838 Bill 1677385 Bill 1607385 Phil 1607877 Phil 1607789 Phil 1699880 Belle 1688989 Belle 1678793 Belle 1676652 George 1679898 George 1655886 George 1677955
– david
Aug 14 at 10:55
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm producing an Excel report where I need to input a randomised selection from a data column, but to omit the human element of selection (for audit purposes).
The column names are "Date", "Staff Name" and "Cad Number".
The "Cad number" column is specific to the "staff member" on a particular date.
Is it possible to enter a command for the report to select 5 random "cad numbers" per "staff member"?
microsoft-excel
edited Aug 14 at 11:36
robinCTS
3,99941527
asked Aug 14 at 10:20
david
1
I'm producing an Excel report where I need to input a randomised selection from a data column, but to omit the human element of selection (for audit purposes).
The column names are "Date", "Staff Name" and "Cad Number".
The "Cad number" column is specific to the "staff member" on a particular date.
Is it possible to enter a command for the report to select 5 random "cad numbers" per "staff member"?
microsoft-excel
microsoft-excel
edited Aug 14 at 11:36
robinCTS
3,99941527
asked Aug 14 at 10:20
david
1
edited Aug 14 at 11:36
robinCTS
3,99941527
asked Aug 14 at 10:20
david
1
edited Aug 14 at 11:36
robinCTS
3,99941527
edited Aug 14 at 11:36
robinCTS
3,99941527
edited Aug 14 at 11:36
robinCTS
3,99941527
3,99941527
asked Aug 14 at 10:20
david
1
asked Aug 14 at 10:20
david
1
asked Aug 14 at 10:20
david
1
1
Hi-I've tried to use the rand formula but can only use it specific to one column i.e. the "cad number" column to give 5 random numbers. I can't/or don't have the relevant knowledge to actually link the name column to the cad number column to carry out this function. Attempted a macro but without the formula or array to carry out the command i cant get it going either
– david
Aug 14 at 10:27
Please edit your answer, and add the information from your comment, While you're at it please add a screenshot of some sample data and/or a formatted table of the data (useCtrl+Kor click the { } icon).
– robinCTS
Aug 14 at 10:33
For start.. do=RANDBETWEEN(1,5)and share your results.. || Some info are missing from your question. Like is the staff number repeated? + is the card number are allowed to be repeated? + does each staff will only have 1 card or multiple cards? || A sample data would help to clarify your Q
– p._phidot_
Aug 14 at 10:35
OK-I will try add some test data but for starters I have a set number of staff names i.e. 50 names with each having approx 300 cad numbers against their name which for audit purposes I would like excel to randomise down to 5 random cad numbers per staff name. the '=randbetween(1,5)' is good if I only want one specific staff name but I want excel to do this for the complete staff name column. Again hopefully I am making sense and thank you for the help so far.
– david
Aug 14 at 10:49
staff name cad number Bill 1607838 Bill 1677385 Bill 1607385 Phil 1607877 Phil 1607789 Phil 1699880 Belle 1688989 Belle 1678793 Belle 1676652 George 1679898 George 1655886 George 1677955
– david
Aug 14 at 10:55
|
show 1 more comment
Hi-I've tried to use the rand formula but can only use it specific to one column i.e. the "cad number" column to give 5 random numbers. I can't/or don't have the relevant knowledge to actually link the name column to the cad number column to carry out this function. Attempted a macro but without the formula or array to carry out the command i cant get it going either
– david
Aug 14 at 10:27
Please edit your answer, and add the information from your comment, While you're at it please add a screenshot of some sample data and/or a formatted table of the data (useCtrl+Kor click the { } icon).
– robinCTS
Aug 14 at 10:33
For start.. do=RANDBETWEEN(1,5)and share your results.. || Some info are missing from your question. Like is the staff number repeated? + is the card number are allowed to be repeated? + does each staff will only have 1 card or multiple cards? || A sample data would help to clarify your Q
– p._phidot_
Aug 14 at 10:35
OK-I will try add some test data but for starters I have a set number of staff names i.e. 50 names with each having approx 300 cad numbers against their name which for audit purposes I would like excel to randomise down to 5 random cad numbers per staff name. the '=randbetween(1,5)' is good if I only want one specific staff name but I want excel to do this for the complete staff name column. Again hopefully I am making sense and thank you for the help so far.
– david
Aug 14 at 10:49
staff name cad number Bill 1607838 Bill 1677385 Bill 1607385 Phil 1607877 Phil 1607789 Phil 1699880 Belle 1688989 Belle 1678793 Belle 1676652 George 1679898 George 1655886 George 1677955
– david
Aug 14 at 10:55
Hi-I've tried to use the rand formula but can only use it specific to one column i.e. the "cad number" column to give 5 random numbers. I can't/or don't have the relevant knowledge to actually link the name column to the cad number column to carry out this function. Attempted a macro but without the formula or array to carry out the command i cant get it going either
– david
Aug 14 at 10:27
Hi-I've tried to use the rand formula but can only use it specific to one column i.e. the "cad number" column to give 5 random numbers. I can't/or don't have the relevant knowledge to actually link the name column to the cad number column to carry out this function. Attempted a macro but without the formula or array to carry out the command i cant get it going either
– david
Aug 14 at 10:27
Please edit your answer, and add the information from your comment, While you're at it please add a screenshot of some sample data and/or a formatted table of the data (use
Ctrl+K or click the { } icon).– robinCTS
Aug 14 at 10:33
Please edit your answer, and add the information from your comment, While you're at it please add a screenshot of some sample data and/or a formatted table of the data (use
Ctrl+K or click the { } icon).– robinCTS
Aug 14 at 10:33
For start.. do
=RANDBETWEEN(1,5) and share your results.. || Some info are missing from your question. Like is the staff number repeated? + is the card number are allowed to be repeated? + does each staff will only have 1 card or multiple cards? || A sample data would help to clarify your Q– p._phidot_
Aug 14 at 10:35
For start.. do
=RANDBETWEEN(1,5) and share your results.. || Some info are missing from your question. Like is the staff number repeated? + is the card number are allowed to be repeated? + does each staff will only have 1 card or multiple cards? || A sample data would help to clarify your Q– p._phidot_
Aug 14 at 10:35
OK-I will try add some test data but for starters I have a set number of staff names i.e. 50 names with each having approx 300 cad numbers against their name which for audit purposes I would like excel to randomise down to 5 random cad numbers per staff name. the '=randbetween(1,5)' is good if I only want one specific staff name but I want excel to do this for the complete staff name column. Again hopefully I am making sense and thank you for the help so far.
– david
Aug 14 at 10:49
OK-I will try add some test data but for starters I have a set number of staff names i.e. 50 names with each having approx 300 cad numbers against their name which for audit purposes I would like excel to randomise down to 5 random cad numbers per staff name. the '=randbetween(1,5)' is good if I only want one specific staff name but I want excel to do this for the complete staff name column. Again hopefully I am making sense and thank you for the help so far.
– david
Aug 14 at 10:49
staff name cad number Bill 1607838 Bill 1677385 Bill 1607385 Phil 1607877 Phil 1607789 Phil 1699880 Belle 1688989 Belle 1678793 Belle 1676652 George 1679898 George 1655886 George 1677955
– david
Aug 14 at 10:55
staff name cad number Bill 1607838 Bill 1677385 Bill 1607385 Phil 1607877 Phil 1607789 Phil 1699880 Belle 1688989 Belle 1678793 Belle 1676652 George 1679898 George 1655886 George 1677955
– david
Aug 14 at 10:55
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
Do 2 list.. 1st to generate the random sequence, 2nd to call the randomized sequence cad number.
list A ( and put it in column A, start at A2 ), the 300 cad list. should occupy A2:A301
then in B2 put =RAND() , then drag downwards till end of list.
in C2 put =RANK(B2,B:B,0) , then drag downwards till end of list.
list B ( and put it in column E, start at E2 ), a running number from 1 to 250.
then in F2 onwards, the staff namex5.. should occupy F2:F251 Eg Bill1,Bill2,Bill3,Bill4,Bill5,Phil1,Phil2,Phil3,Phil4,Phil5,Belle1,Belle2....
Then in G2, put =index(A:A,match(E2;C:C,0)), then drag downwards till end of list.
I think that's it. Have a try.
answered Dec 6 at 6:41
p._phidot_
49028
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1348934%2fchoosing-a-set-of-random-values-from-one-column-where-another-column-matches-a-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Do 2 list.. 1st to generate the random sequence, 2nd to call the randomized sequence cad number.
list A ( and put it in column A, start at A2 ), the 300 cad list. should occupy A2:A301
then in B2 put =RAND() , then drag downwards till end of list.
in C2 put =RANK(B2,B:B,0) , then drag downwards till end of list.
list B ( and put it in column E, start at E2 ), a running number from 1 to 250.
then in F2 onwards, the staff namex5.. should occupy F2:F251 Eg Bill1,Bill2,Bill3,Bill4,Bill5,Phil1,Phil2,Phil3,Phil4,Phil5,Belle1,Belle2....
Then in G2, put =index(A:A,match(E2;C:C,0)), then drag downwards till end of list.
I think that's it. Have a try.
answered Dec 6 at 6:41
p._phidot_
49028
add a comment |
up vote
0
down vote
Do 2 list.. 1st to generate the random sequence, 2nd to call the randomized sequence cad number.
list A ( and put it in column A, start at A2 ), the 300 cad list. should occupy A2:A301
then in B2 put =RAND() , then drag downwards till end of list.
in C2 put =RANK(B2,B:B,0) , then drag downwards till end of list.
list B ( and put it in column E, start at E2 ), a running number from 1 to 250.
then in F2 onwards, the staff namex5.. should occupy F2:F251 Eg Bill1,Bill2,Bill3,Bill4,Bill5,Phil1,Phil2,Phil3,Phil4,Phil5,Belle1,Belle2....
Then in G2, put =index(A:A,match(E2;C:C,0)), then drag downwards till end of list.
I think that's it. Have a try.
answered Dec 6 at 6:41
p._phidot_
49028
add a comment |
up vote
0
down vote
up vote
0
down vote
Do 2 list.. 1st to generate the random sequence, 2nd to call the randomized sequence cad number.
list A ( and put it in column A, start at A2 ), the 300 cad list. should occupy A2:A301
then in B2 put =RAND() , then drag downwards till end of list.
in C2 put =RANK(B2,B:B,0) , then drag downwards till end of list.
list B ( and put it in column E, start at E2 ), a running number from 1 to 250.
then in F2 onwards, the staff namex5.. should occupy F2:F251 Eg Bill1,Bill2,Bill3,Bill4,Bill5,Phil1,Phil2,Phil3,Phil4,Phil5,Belle1,Belle2....
Then in G2, put =index(A:A,match(E2;C:C,0)), then drag downwards till end of list.
I think that's it. Have a try.
answered Dec 6 at 6:41
p._phidot_
49028
Do 2 list.. 1st to generate the random sequence, 2nd to call the randomized sequence cad number.
list A ( and put it in column A, start at A2 ), the 300 cad list. should occupy A2:A301
then in B2 put =RAND() , then drag downwards till end of list.
in C2 put =RANK(B2,B:B,0) , then drag downwards till end of list.
list B ( and put it in column E, start at E2 ), a running number from 1 to 250.
then in F2 onwards, the staff namex5.. should occupy F2:F251 Eg Bill1,Bill2,Bill3,Bill4,Bill5,Phil1,Phil2,Phil3,Phil4,Phil5,Belle1,Belle2....
Then in G2, put =index(A:A,match(E2;C:C,0)), then drag downwards till end of list.
I think that's it. Have a try.
answered Dec 6 at 6:41
p._phidot_
49028
answered Dec 6 at 6:41
p._phidot_
49028
answered Dec 6 at 6:41
p._phidot_
49028
answered Dec 6 at 6:41
p._phidot_
49028
49028
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1348934%2fchoosing-a-set-of-random-values-from-one-column-where-another-column-matches-a-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Hi-I've tried to use the rand formula but can only use it specific to one column i.e. the "cad number" column to give 5 random numbers. I can't/or don't have the relevant knowledge to actually link the name column to the cad number column to carry out this function. Attempted a macro but without the formula or array to carry out the command i cant get it going either
– david
Aug 14 at 10:27
Please edit your answer, and add the information from your comment, While you're at it please add a screenshot of some sample data and/or a formatted table of the data (use
Ctrl+Kor click the { } icon).– robinCTS
Aug 14 at 10:33
For start.. do
=RANDBETWEEN(1,5)and share your results.. || Some info are missing from your question. Like is the staff number repeated? + is the card number are allowed to be repeated? + does each staff will only have 1 card or multiple cards? || A sample data would help to clarify your Q– p._phidot_
Aug 14 at 10:35
OK-I will try add some test data but for starters I have a set number of staff names i.e. 50 names with each having approx 300 cad numbers against their name which for audit purposes I would like excel to randomise down to 5 random cad numbers per staff name. the '=randbetween(1,5)' is good if I only want one specific staff name but I want excel to do this for the complete staff name column. Again hopefully I am making sense and thank you for the help so far.
– david
Aug 14 at 10:49
staff name cad number Bill 1607838 Bill 1677385 Bill 1607385 Phil 1607877 Phil 1607789 Phil 1699880 Belle 1688989 Belle 1678793 Belle 1676652 George 1679898 George 1655886 George 1677955
– david
Aug 14 at 10:55