Formula to produce all permutations of 2 sets of values

F

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Say I have the following 2 data sets:

Data Set 1:
Auto
Finance
Consumer
Installment

Data Set 2:
1
2
4
6
7
8
9

I need to take those data sets, and transpose them into 2 adjacent columns, one row for each potential combination with those data items in each data set. The results in 2 adjacent columns would look like:

Auto 1
Auto 2
Auto 4
Auto 6
Auto 7
Auto 8
Auto 9
Finance 1
Finance 2
Finance 4
...etc.

I need the formulas that automatically transpose that data to be robust, where the number of rows of data in either data set can vary (i.e. the first data set is sometimes 32 rows, or 541 rows, but the formula still populates every combination/permutation.

Is this possible to do via a formula?
Thanks in advance
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
freeb1893 said:
Say I have the following 2 data sets:

Data Set 1:
Auto
Finance
Consumer
Installment

Data Set 2:
1
2
4
6
7
8
9

I need to take those data sets, and transpose them into 2 adjacent columns, one row for each potential combination with those data items in each data set. The results in 2 adjacent columns would look like:

Auto 1
Auto 2
Auto 4
Auto 6
Auto 7
Auto 8
Auto 9
Finance 1
Finance 2
Finance 4
...etc.

I need the formulas that automatically transpose that data to be robust, where the number of rows of data in either data set can vary (i.e. the first data set is sometimes 32 rows, or 541 rows, but the formula still populates every combination/permutation.

Is this possible to do via a formula?
Thanks in advance
Click to expand...
I don't understand what you mean by 2 adjacent columns. You could set up a table like the one below to generate combinations of each element in set 1 combined with each element of set 2 in the form set 1 set 2. You can then rearrange the output.
Copy B2 across and then down.
Excel Workbook
ABCDEFGH
1*1246789
2AutoAuto 1Auto 2Auto 4Auto 6Auto 7Auto 8Auto 9
3FinanceFinance 1Finance 2Finance 4Finance 6Finance 7Finance 8Finance 9
4ConsumerConsumer 1Consumer 2Consumer 4Consumer 6Consumer 7Consumer 8Consumer 9
5InstallmentInstallment 1Installment 2Installment 4Installment 6Installment 7Installment 8Installment 9
Sheet3
 
Upvote 0
Is this what you're looking for?

Excel 2012
A
B
C
D
E
Auto
Auto
Finance
Auto
Consumer
Auto
Installment
Auto
Auto
Auto
Auto
Finance
Finance
Finance
Finance
Finance
Finance
Finance
Consumer
Consumer
Consumer
Consumer
Consumer
Consumer
Consumer
Installment
Installment
Installment
Installment
Installment
Installment
Installment

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]24
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]27
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]28
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]29
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1
[/TH]
[TD="align: left"]=IF(ROW()>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-1))/COUNTA(B:B)+1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E1
[/TH]
[TD="align: left"]=IF(ROW()>COUNTA(A:A)*COUNTA(B:B),"",INDEX(B:B,MOD(ROW()-1,COUNTA(B:B))+1))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Put the formulas in D1:E1 and drag down as far as necessary.

You can add, change or remove items from columns A:B and the formulas will adapt automatically. These formulas assume that everything starts in row 1. If you have headers or something else above the data/results, they'll need to be adjusted.
 
Upvote 0
Eh main reason I wanted the results vertical in 2 adjacent columns was because I'm using some SUMPRODUCT formulas later on with that data, and I'd like to just drag my SUMPRODUCT formulas down to reference all the combinations in those 2 adjacent columns with the data already being vertical in that layout.

This is what I have so far: =IF(ROW()-ROW($D$1)+1>COUNTA($A$1:$A$4167)*COUNTA($B$1:$B$74),"",INDEX($A$1:$A$4167,INT((ROW()-ROW($D$1))/COUNTA($B$1:$B$74)+1))&INDEX($B$1:$B$74,MOD(ROW()-ROW($D$1),COUNTA($B$1:$B$74))+1))

But I have to trim the results into 2 separate columns via some LEFT and RIGHT formulas. My values in the 2nd column are always 2 characters, which makes this an option (would love to no have to ensure one original column of data is always the same number of characters though.) [
QUOTE=JoeMo;4657352]I don't understand what you mean by 2 adjacent columns. You could set up a table like the one below to generate combinations of each element in set 1 combined with each element of set 2 in the form set 1 set 2. You can then rearrange the output.
Copy B2 across and then down.
Sheet3

*ABCDEFGH
*
AutoAuto 1Auto 2Auto 4Auto 6Auto 7Auto 8Auto 9
FinanceFinance 1Finance 2Finance 4Finance 6Finance 7Finance 8Finance 9
ConsumerConsumer 1Consumer 2Consumer 4Consumer 6Consumer 7Consumer 8Consumer 9
InstallmentInstallment 1Installment 2Installment 4Installment 6Installment 7Installment 8Installment 9

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 78px;"><col style="width: 85px;"><col style="width: 85px;"><col style="width: 85px;"><col style="width: 85px;"><col style="width: 85px;"><col style="width: 85px;"><col style="width: 85px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=$A2&" "&B$1

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4 [/QUOTE]
 
Upvote 0
Ahh this is perfect. Thanks so much!
Eric W said:
Is this what you're looking for?

Excel 2012
A
B
C
D
E
Auto
Auto
Finance
Auto
Consumer
Auto
Installment
Auto
Auto
Auto
Auto
Finance
Finance
Finance
Finance
Finance
Finance
Finance
Consumer
Consumer
Consumer
Consumer
Consumer
Consumer
Consumer
Installment
Installment
Installment
Installment
Installment
Installment
Installment

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]4
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]22
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]

[TD="align: center"]23
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2
[/TD]

[TD="align: center"]24
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4
[/TD]

[TD="align: center"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]

[TD="align: center"]26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7
[/TD]

[TD="align: center"]27
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]

[TD="align: center"]28
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9
[/TD]

[TD="align: center"]29
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1
[/TH]
[TD="align: left"]=IF(ROW()>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-1))/COUNTA(B:B)+1))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E1
[/TH]
[TD="align: left"]=IF(ROW()>COUNTA(A:A)*COUNTA(B:B),"",INDEX(B:B,MOD(ROW()-1,COUNTA(B:B))+1))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Put the formulas in D1:E1 and drag down as far as necessary.

You can add, change or remove items from columns A:B and the formulas will adapt automatically. These formulas assume that everything starts in row 1. If you have headers or something else above the data/results, they'll need to be adjusted.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Formula that has sum and exception
Replies
4
Views
285
Cubist
Cubist
I
  • Solved
Irregular Data Set
2
Replies
19
Views
320
IdabaMalouki
I
S
  • Question Question
I am trying write a formula for when I enter in the machine number the machine name populates with the correct machine name
Replies
2
Views
128
Joe4
Joe4
A
  • Solved
Automatic Serial Number before Text in a Cell
2 3
Replies
25
Views
1K
Joe4
Joe4
T
  • Question Question
Calculate if staff are still enrolled in tasks
Replies
2
Views
72
tigerzen
T

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top