VBA Permutations and Combinations

Mr2017 · Sep 4, 2020

Hi

I have 3 columns of data - Locations, Packages and Suppliers.

In the small sample below (Table 1), Locations 1 - 10 are listed in column A from cells A2:A11, respectively.

Packages 1 - 5 are listed in cells B2:B6, respectively.

And Suppliers 1 - 12 are listed in cells C2:C13, respectively.

I'd like to use VBA to ensure that for EACH Location AND Supplier, the list of packages is displayed (please see tables 2 and 3 below), as examples.

However, I'd like all the results to be in just three columns - a column for Locations, a column for Packages and a column for Suppliers.

Does anyone know how to do this with VBA, please?

TIA

Table 1

Location	Package	Supplier
Location 1	Package 1	Supplier 1
Location 2	Package 2	Supplier 2
Location 3	Package 3	Supplier 3
Location 4	Package 4	Supplier 4
Location 5	Package 5	Supplier 5
Location 6		Supplier 6
Location 7		Supplier 7
Location 8		Supplier 8
Location 9		Supplier 9
Location 10		Supplier 10
		Supplier 11
		Supplier 12

Tables 2 and 3

Table 2		Table 3
Location	Package	Supplier	Location	Package	Supplier
Location 1	Package 1	Supplier 1	Location 1	Package 1	Supplier 2
Location 1	Package 2	Supplier 1	Location 1	Package 2	Supplier 2
Location 1	Package 3	Supplier 1	Location 1	Package 3	Supplier 2
Location 1	Package 4	Supplier 1	Location 1	Package 4	Supplier 2
Location 1	Package 5	Supplier 1	Location 1	Package 5	Supplier 2
Location 2	Package 1	Supplier 1	Location 2	Package 1	Supplier 2
Location 2	Package 2	Supplier 1	Location 2	Package 2	Supplier 2
Location 2	Package 3	Supplier 1	Location 2	Package 3	Supplier 2
Location 2	Package 4	Supplier 1	Location 2	Package 4	Supplier 2
Location 2	Package 5	Supplier 1	Location 2	Package 5	Supplier 2
Location 3	Package 1	Supplier 1	Location 3	Package 1	Supplier 2
Location 3	Package 2	Supplier 1	Location 3	Package 2	Supplier 2
Location 3	Package 3	Supplier 1	Location 3	Package 3	Supplier 2
Location 3	Package 4	Supplier 1	Location 3	Package 4	Supplier 2
Location 3	Package 5	Supplier 1	Location 3	Package 5	Supplier 2
Location 4	Package 1	Supplier 1	Location 4	Package 1	Supplier 2
Location 4	Package 2	Supplier 1	Location 4	Package 2	Supplier 2
Location 4	Package 3	Supplier 1	Location 4	Package 3	Supplier 2
Location 4	Package 4	Supplier 1	Location 4	Package 4	Supplier 2
Location 4	Package 5	Supplier 1	Location 4	Package 5	Supplier 2
Location 5	Package 1	Supplier 1	Location 5	Package 1	Supplier 2
Location 5	Package 2	Supplier 1	Location 5	Package 2	Supplier 2
Location 5	Package 3	Supplier 1	Location 5	Package 3	Supplier 2
Location 5	Package 4	Supplier 1	Location 5	Package 4	Supplier 2
Location 5	Package 5	Supplier 1	Location 5	Package 5	Supplier 2
Location 6	Package 1	Supplier 1	Location 6	Package 1	Supplier 2
Location 6	Package 2	Supplier 1	Location 6	Package 2	Supplier 2
Location 6	Package 3	Supplier 1	Location 6	Package 3	Supplier 2
Location 6	Package 4	Supplier 1	Location 6	Package 4	Supplier 2
Location 6	Package 5	Supplier 1	Location 6	Package 5	Supplier 2
Location 7	Package 1	Supplier 1	Location 7	Package 1	Supplier 2
Location 7	Package 2	Supplier 1	Location 7	Package 2	Supplier 2
Location 7	Package 3	Supplier 1	Location 7	Package 3	Supplier 2
Location 7	Package 4	Supplier 1	Location 7	Package 4	Supplier 2
Location 7	Package 5	Supplier 1	Location 7	Package 5	Supplier 2
Location 8	Package 1	Supplier 1	Location 8	Package 1	Supplier 2
Location 8	Package 2	Supplier 1	Location 8	Package 2	Supplier 2
Location 8	Package 3	Supplier 1	Location 8	Package 3	Supplier 2
Location 8	Package 4	Supplier 1	Location 8	Package 4	Supplier 2
Location 8	Package 5	Supplier 1	Location 8	Package 5	Supplier 2
Location 9	Package 1	Supplier 1	Location 9	Package 1	Supplier 2
Location 9	Package 2	Supplier 1	Location 9	Package 2	Supplier 2
Location 9	Package 3	Supplier 1	Location 9	Package 3	Supplier 2
Location 9	Package 4	Supplier 1	Location 9	Package 4	Supplier 2
Location 9	Package 5	Supplier 1	Location 9	Package 5	Supplier 2
Location 10	Package 1	Supplier 1	Location 10	Package 1	Supplier 2
Location 10	Package 2	Supplier 1	Location 10	Package 2	Supplier 2
Location 10	Package 3	Supplier 1	Location 10	Package 3	Supplier 2
Location 10	Package 4	Supplier 1	Location 10	Package 4	Supplier 2
Location 10	Package 5	Supplier 1	Location 10	Package 5	Supplier 2

kweaver · Sep 5, 2020

E2 spills

Code:

=INDEX(($A2:$A11),ROUNDUP(SEQUENCE(COUNTA($A2:$A11)*COUNTA($B2:$B6),1,1,1)/COUNTA($B2:$B6),0))&" "&INDEX($B2:$B6,MOD(SEQUENCE(COUNTA($A2:$A11)*COUNTA($B2:$B6),1,0,1),COUNTA($B2:$B6))+1)& " Supplier 1"

F2 spills

Code:

=INDEX(($A2:$A11),ROUNDUP(SEQUENCE(COUNTA($A2:$A11)*COUNTA($B2:$B6),1,1,1)/COUNTA($B2:$B6),0))&" "&INDEX($B2:$B6,MOD(SEQUENCE(COUNTA($A2:$A11)*COUNTA($B2:$B6),1,0,1),COUNTA($B2:$B6))+1)& " Supplier 2"

Not exactly what you asked for but I think this formula gives you the combinations you wanted.

kweaver · Sep 5, 2020

You could make E2 this formula and fill across.

Code:

=INDEX(($A2:$A11),ROUNDUP(SEQUENCE(COUNTA($A2:$A11)*COUNTA($B2:$B6),1,1,1)/COUNTA($B2:$B6),0))&" "&INDEX($B2:$B6,MOD(SEQUENCE(COUNTA($A2:$A11)*COUNTA($B2:$B6),1,0,1),COUNTA($B2:$B6))+1)& " Supplier "&COLUMN()-4

Mr2017 · Sep 5, 2020

Hi @kweaver thanks for the prompt response.

That's an interesting solution, which gives the right permutations in concatenated columns.

However, the only problem is that I would then need to manually create formulae to then split them out and because the number of words and characters in the locations, packages, and Suppliers varies wildly, it could take quite a bit of effort to split them out into the right cells!

So whilst I appreciate your suggested solution, my preference would be for a VBA solution that would prevent a lot of subsequent manual intervention....

kweaver · Sep 5, 2020

What about:

Code:

Sub combineTwoCol()
Dim k As Long, i As Long, j As Long
Dim lrl As Long, lrp As Long
lrl = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
lrp = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
    ' i,j,k are counters for first col, second col and the answer col in order.
    k = 1
    For i = 2 To lrl
        For j = 2 To lrp
            Cells(k, "E").Value = Cells(i, "A").Value
            Cells(k, "F").Value = Cells(j, "B").Value
            k = k + 1
        Next j
    Next i
End Sub

Fluff · Sep 5, 2020

Another option

VBA Code:

Sub Mr()
   Dim Lary As Variant, Pary As Variant, Sary As Variant, Oary As Variant
   Dim rl As Long, rp As Long, rs As Long, ro As Long, c As Long
   
   Lary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   Pary = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value2
   Sary = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value2
   ReDim Oary(1 To UBound(Lary) * UBound(Pary), 1 To UBound(Sary) * 4)
   c = 1
   For rs = 1 To UBound(Sary)
      For rl = 1 To UBound(Lary)
         For rp = 1 To UBound(Pary)
            ro = ro + 1
            Oary(ro, c) = Lary(rl, 1)
            Oary(ro, c + 1) = Pary(rp, 1)
            Oary(ro, c + 2) = Sary(rs, 1)
         Next rp
      Next rl
      ro = 0
      c = c + 4
   Next rs
   Range("F2").Resize(UBound(Oary), UBound(Oary, 2)).Value = Oary
End Sub

Mr2017 · Sep 5, 2020

Thanks for the updated code, @kweaver. I tried to run it, but it didn't work, as exptected... some of the Locations didn't have a number next to them in the end result.

The code that @Fluff wrote above works, however i.e it gives the permutations and combinations and also splits the columns out.

The only thing I'd ask, is if there is a way to group the data into just three columns? So that the final result has the output that Fluff's code above produced but one column with all the Locations, one column with all the Packages and one column with all the Suppliers?

ie Table 2 to be below Table 1, Table 3 to be below Table 2, etc. So that all Tables are in one group of three columns.

Please let me know if you'd like me to clarify what I mean?

TIA

Fluff · Sep 5, 2020

Mr2017 said:
Table 2 to be below Table 1, Table 3 to be below Table 2, etc. So that all Tables are in one group of three columns.

Then why did you show it as separate columns.

Fluff · Sep 5, 2020

How about

VBA Code:

Sub Mr()
   Dim Lary As Variant, Pary As Variant, Sary As Variant, Oary As Variant
   Dim rl As Long, rp As Long, rs As Long, ro As Long
   
   Lary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   Pary = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value2
   Sary = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value2
   ReDim Oary(1 To UBound(Lary) * UBound(Pary) * UBound(Sary), 1 To 3)

   For rs = 1 To UBound(Sary)
      For rl = 1 To UBound(Lary)
         For rp = 1 To UBound(Pary)
            ro = ro + 1
            Oary(ro, 1) = Lary(rl, 1)
            Oary(ro, 2) = Pary(rp, 1)
            Oary(ro, 3) = Sary(rs, 1)
         Next rp
      Next rl
   Next rs
   Range("F2").Resize(UBound(Oary), 3).Value = Oary
End Sub

Mr2017 · Sep 5, 2020

Sorry, I'd originally shown separate columns in the original question because I wanted to make it easy to see how the data would be grouped.

VBA Permutations and Combinations

Mr2017

Well-known Member

Excel Facts

kweaver

Well-known Member

kweaver

Well-known Member

Mr2017

Well-known Member

kweaver

Well-known Member

Fluff

MrExcel MVP, Moderator

Mr2017

Well-known Member

Fluff

MrExcel MVP, Moderator

Fluff

MrExcel MVP, Moderator

Mr2017

Well-known Member

Similar threads

Forum statistics

Share this page

VBA Permutations and Combinations

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock