Excel create table with all combinations
6
Let's assume I have 3 columns with labels
- A B C D
- a b c d e
- 1 2 3 4 5 6 7 8 9 10
Is there a quick and easy way to create an empty table with all permutations? i.e.
- A - a - 1
- A - a - 2
- ....
- D - e - 10
microsoft-excel
edited Jan 20 at 0:50
fixer1234
18.9k144982
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
add a comment |
6
Let's assume I have 3 columns with labels
- A B C D
- a b c d e
- 1 2 3 4 5 6 7 8 9 10
Is there a quick and easy way to create an empty table with all permutations? i.e.
- A - a - 1
- A - a - 2
- ....
- D - e - 10
microsoft-excel
edited Jan 20 at 0:50
fixer1234
18.9k144982
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
2
Why does this look to me like a school homework assignment?
– Jeff Zeitlin
Mar 31 '17 at 15:49
No worries, it's for work and I'm trying to automatize cumbersome copy paste tasks as I might be required to redo this several times in the future
– SuperMartingale
Apr 3 '17 at 7:04
Clearly worded question that solved my exact problem. Thank you. Well asked questions don't get enough upvotes.
– Myrddin Emrys
Nov 21 '17 at 14:11
add a comment |
6
6
6
3
Let's assume I have 3 columns with labels
- A B C D
- a b c d e
- 1 2 3 4 5 6 7 8 9 10
Is there a quick and easy way to create an empty table with all permutations? i.e.
- A - a - 1
- A - a - 2
- ....
- D - e - 10
microsoft-excel
edited Jan 20 at 0:50
fixer1234
18.9k144982
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
Let's assume I have 3 columns with labels
- A B C D
- a b c d e
- 1 2 3 4 5 6 7 8 9 10
Is there a quick and easy way to create an empty table with all permutations? i.e.
- A - a - 1
- A - a - 2
- ....
- D - e - 10
microsoft-excel
microsoft-excel
edited Jan 20 at 0:50
fixer1234
18.9k144982
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
edited Jan 20 at 0:50
fixer1234
18.9k144982
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
edited Jan 20 at 0:50
fixer1234
18.9k144982
edited Jan 20 at 0:50
fixer1234
18.9k144982
edited Jan 20 at 0:50
fixer1234
18.9k144982
18.9k144982
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
asked Mar 31 '17 at 15:36
SuperMartingaleSuperMartingale
33113
33113
2
Why does this look to me like a school homework assignment?
– Jeff Zeitlin
Mar 31 '17 at 15:49
No worries, it's for work and I'm trying to automatize cumbersome copy paste tasks as I might be required to redo this several times in the future
– SuperMartingale
Apr 3 '17 at 7:04
Clearly worded question that solved my exact problem. Thank you. Well asked questions don't get enough upvotes.
– Myrddin Emrys
Nov 21 '17 at 14:11
add a comment |
2
Why does this look to me like a school homework assignment?
– Jeff Zeitlin
Mar 31 '17 at 15:49
No worries, it's for work and I'm trying to automatize cumbersome copy paste tasks as I might be required to redo this several times in the future
– SuperMartingale
Apr 3 '17 at 7:04
Clearly worded question that solved my exact problem. Thank you. Well asked questions don't get enough upvotes.
– Myrddin Emrys
Nov 21 '17 at 14:11
2
2
Why does this look to me like a school homework assignment?
– Jeff Zeitlin
Mar 31 '17 at 15:49
Why does this look to me like a school homework assignment?
– Jeff Zeitlin
Mar 31 '17 at 15:49
No worries, it's for work and I'm trying to automatize cumbersome copy paste tasks as I might be required to redo this several times in the future
– SuperMartingale
Apr 3 '17 at 7:04
No worries, it's for work and I'm trying to automatize cumbersome copy paste tasks as I might be required to redo this several times in the future
– SuperMartingale
Apr 3 '17 at 7:04
Clearly worded question that solved my exact problem. Thank you. Well asked questions don't get enough upvotes.
– Myrddin Emrys
Nov 21 '17 at 14:11
Clearly worded question that solved my exact problem. Thank you. Well asked questions don't get enough upvotes.
– Myrddin Emrys
Nov 21 '17 at 14:11
add a comment |
2 Answers
2
active
oldest
votes
4
Running this short macro:
Sub marine()
Dim i As Long, j As Long, k As Long, N As Long
N = 1
For i = 1 To 4
For j = 1 To 5
For k = 1 To 10
Cells(N, 4) = Cells(i, 1) & "-" & Cells(j, 2) & "-" & Cells(k, 3)
N = N + 1
Next k
Next j
Next i
End Sub
will produce a column of the desired 200 combinations:
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
after long search your way is the easiest if the person can write code
– shady sherif
Jun 12 '18 at 12:34
@shadysherif Thank you, I also like Scott's answer
– Gary's Student
Jun 12 '18 at 12:36
In my case I have data in different sheets.that's why this answer is the best for me.
– shady sherif
Jun 12 '18 at 15:07
1
Exactly! These are COMBINATIONS, not permutations. If they were permutations, you would include A-a-1, A-1-a, a-A-1, etc...
– richard1941
Aug 11 '18 at 19:08
add a comment |
6
You can do this with a formula:
=IFERROR(INDEX($A$1:$A$4,INT((ROW(1:1)-1)/((COUNTA(B:B)*(COUNTA(C:C)))))+1)&" - "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/COUNTA(C:C)),COUNTA(B:B))+1)&" - "&INDEX(C:C,MOD((ROW(1:1)-1),COUNTA(C:C))+1),"")
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
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',
autoActivateHeartbeat: false,
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
});
}
});
draft saved
draft discarded
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%2f1194312%2fexcel-create-table-with-all-combinations%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
Running this short macro:
Sub marine()
Dim i As Long, j As Long, k As Long, N As Long
N = 1
For i = 1 To 4
For j = 1 To 5
For k = 1 To 10
Cells(N, 4) = Cells(i, 1) & "-" & Cells(j, 2) & "-" & Cells(k, 3)
N = N + 1
Next k
Next j
Next i
End Sub
will produce a column of the desired 200 combinations:
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
after long search your way is the easiest if the person can write code
– shady sherif
Jun 12 '18 at 12:34
@shadysherif Thank you, I also like Scott's answer
– Gary's Student
Jun 12 '18 at 12:36
In my case I have data in different sheets.that's why this answer is the best for me.
– shady sherif
Jun 12 '18 at 15:07
1
Exactly! These are COMBINATIONS, not permutations. If they were permutations, you would include A-a-1, A-1-a, a-A-1, etc...
– richard1941
Aug 11 '18 at 19:08
add a comment |
4
Running this short macro:
Sub marine()
Dim i As Long, j As Long, k As Long, N As Long
N = 1
For i = 1 To 4
For j = 1 To 5
For k = 1 To 10
Cells(N, 4) = Cells(i, 1) & "-" & Cells(j, 2) & "-" & Cells(k, 3)
N = N + 1
Next k
Next j
Next i
End Sub
will produce a column of the desired 200 combinations:
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
after long search your way is the easiest if the person can write code
– shady sherif
Jun 12 '18 at 12:34
@shadysherif Thank you, I also like Scott's answer
– Gary's Student
Jun 12 '18 at 12:36
In my case I have data in different sheets.that's why this answer is the best for me.
– shady sherif
Jun 12 '18 at 15:07
1
Exactly! These are COMBINATIONS, not permutations. If they were permutations, you would include A-a-1, A-1-a, a-A-1, etc...
– richard1941
Aug 11 '18 at 19:08
add a comment |
4
4
4
Running this short macro:
Sub marine()
Dim i As Long, j As Long, k As Long, N As Long
N = 1
For i = 1 To 4
For j = 1 To 5
For k = 1 To 10
Cells(N, 4) = Cells(i, 1) & "-" & Cells(j, 2) & "-" & Cells(k, 3)
N = N + 1
Next k
Next j
Next i
End Sub
will produce a column of the desired 200 combinations:
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
Running this short macro:
Sub marine()
Dim i As Long, j As Long, k As Long, N As Long
N = 1
For i = 1 To 4
For j = 1 To 5
For k = 1 To 10
Cells(N, 4) = Cells(i, 1) & "-" & Cells(j, 2) & "-" & Cells(k, 3)
N = N + 1
Next k
Next j
Next i
End Sub
will produce a column of the desired 200 combinations:
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
answered Mar 31 '17 at 17:33
Gary's StudentGary's Student
13.8k31730
13.8k31730
after long search your way is the easiest if the person can write code
– shady sherif
Jun 12 '18 at 12:34
@shadysherif Thank you, I also like Scott's answer
– Gary's Student
Jun 12 '18 at 12:36
In my case I have data in different sheets.that's why this answer is the best for me.
– shady sherif
Jun 12 '18 at 15:07
1
Exactly! These are COMBINATIONS, not permutations. If they were permutations, you would include A-a-1, A-1-a, a-A-1, etc...
– richard1941
Aug 11 '18 at 19:08
add a comment |
after long search your way is the easiest if the person can write code
– shady sherif
Jun 12 '18 at 12:34
@shadysherif Thank you, I also like Scott's answer
– Gary's Student
Jun 12 '18 at 12:36
In my case I have data in different sheets.that's why this answer is the best for me.
– shady sherif
Jun 12 '18 at 15:07
1
Exactly! These are COMBINATIONS, not permutations. If they were permutations, you would include A-a-1, A-1-a, a-A-1, etc...
– richard1941
Aug 11 '18 at 19:08
after long search your way is the easiest if the person can write code
– shady sherif
Jun 12 '18 at 12:34
after long search your way is the easiest if the person can write code
– shady sherif
Jun 12 '18 at 12:34
@shadysherif Thank you, I also like Scott's answer
– Gary's Student
Jun 12 '18 at 12:36
@shadysherif Thank you, I also like Scott's answer
– Gary's Student
Jun 12 '18 at 12:36
In my case I have data in different sheets.that's why this answer is the best for me.
– shady sherif
Jun 12 '18 at 15:07
In my case I have data in different sheets.that's why this answer is the best for me.
– shady sherif
Jun 12 '18 at 15:07
1
1
Exactly! These are COMBINATIONS, not permutations. If they were permutations, you would include A-a-1, A-1-a, a-A-1, etc...
– richard1941
Aug 11 '18 at 19:08
Exactly! These are COMBINATIONS, not permutations. If they were permutations, you would include A-a-1, A-1-a, a-A-1, etc...
– richard1941
Aug 11 '18 at 19:08
add a comment |
6
You can do this with a formula:
=IFERROR(INDEX($A$1:$A$4,INT((ROW(1:1)-1)/((COUNTA(B:B)*(COUNTA(C:C)))))+1)&" - "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/COUNTA(C:C)),COUNTA(B:B))+1)&" - "&INDEX(C:C,MOD((ROW(1:1)-1),COUNTA(C:C))+1),"")
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
add a comment |
6
You can do this with a formula:
=IFERROR(INDEX($A$1:$A$4,INT((ROW(1:1)-1)/((COUNTA(B:B)*(COUNTA(C:C)))))+1)&" - "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/COUNTA(C:C)),COUNTA(B:B))+1)&" - "&INDEX(C:C,MOD((ROW(1:1)-1),COUNTA(C:C))+1),"")
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
add a comment |
6
6
6
You can do this with a formula:
=IFERROR(INDEX($A$1:$A$4,INT((ROW(1:1)-1)/((COUNTA(B:B)*(COUNTA(C:C)))))+1)&" - "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/COUNTA(C:C)),COUNTA(B:B))+1)&" - "&INDEX(C:C,MOD((ROW(1:1)-1),COUNTA(C:C))+1),"")
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
You can do this with a formula:
=IFERROR(INDEX($A$1:$A$4,INT((ROW(1:1)-1)/((COUNTA(B:B)*(COUNTA(C:C)))))+1)&" - "&INDEX(B:B,MOD(INT((ROW(1:1)-1)/COUNTA(C:C)),COUNTA(B:B))+1)&" - "&INDEX(C:C,MOD((ROW(1:1)-1),COUNTA(C:C))+1),"")
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
answered Mar 31 '17 at 19:08
Scott CranerScott Craner
12.2k11218
12.2k11218
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f1194312%2fexcel-create-table-with-all-combinations%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
2
Why does this look to me like a school homework assignment?
– Jeff Zeitlin
Mar 31 '17 at 15:49
No worries, it's for work and I'm trying to automatize cumbersome copy paste tasks as I might be required to redo this several times in the future
– SuperMartingale
Apr 3 '17 at 7:04
Clearly worded question that solved my exact problem. Thank you. Well asked questions don't get enough upvotes.
– Myrddin Emrys
Nov 21 '17 at 14:11