CODEWITHSUNDEEP
arrayspowershellpermutation
rock
rock

Reputation: 107

How to create a permutation array in powershell?

I have a question about the permutation. For example, I have the following table:

enter image description here

I want to make the permutation with this table. There are two options in the 1st column, three options in the 2nd and 3rd columns. The output permutation array should look like as follow:

enter image description here

There are 18 options to arrange the iterms in 1-3 columns. The question is how to use powershell to create a script to get such an array? And how to output this array into excel?

Upvotes: 0

Views: 2239

Answers (2)

TheMadTechnician
TheMadTechnician

Reputation: 36287

Ok, branching off of Joey's excellent answer, to iterate through an array with known columns you can do the following (PowerShell v3 or higher, it can be done in older versions but gets more complicated).

#clean up any existing variables
Remove-Variable array, alliterations -ea 4

#create the sample array
[array]$array += [PSCustomObject]@{
    'Column 1' = 'Single load'
    'Column 2' = 'MAT1'
    'Column 3' = 'Country A'
}
[array]$array += [PSCustomObject]@{
    'Column 1' = 'Uniform distributed load'
    'Column 2' = 'MAT2'
    'Column 3' = 'Country B'
}
[array]$array += [PSCustomObject]@{
    'Column 1' = ''
    'Column 2' = 'MAT3'
    'Column 3' = 'Country C'
}

#Create all iterations from that array
ForEach($a in ($array.'Column 1'|Where{$_})){

    ForEach($b in ($array.'Column 2'|Where{$_})){

        ForEach($c in ($array.'Column 3'|Where{$_})){
            [array]$AllIterations += [PSCustomObject]@{
                'Column 1' = $a
                'Column 2' = $b
                'Column 3' = $c
            }
        }
    }
}

#Display results
$AllIterations

That will generate the array:

Column 1                 Column 2 Column 3 
--------                 -------- -------- 
Single load              MAT1     Country A
Single load              MAT1     Country B
Single load              MAT1     Country C
Single load              MAT2     Country A
Single load              MAT2     Country B
Single load              MAT2     Country C
Single load              MAT3     Country A
Single load              MAT3     Country B
Single load              MAT3     Country C
Uniform distributed load MAT1     Country A
Uniform distributed load MAT1     Country B
Uniform distributed load MAT1     Country C
Uniform distributed load MAT2     Country A
Uniform distributed load MAT2     Country B
Uniform distributed load MAT2     Country C
Uniform distributed load MAT3     Country A
Uniform distributed load MAT3     Country B
Uniform distributed load MAT3     Country C

To get that into Excel you can either export to a CSV file as Joey suggested, or you can have PowerShell open Excel and paste the data into it directly.

#Copy array to clipboard as a tab delimited CSV
$AllIterations | ConvertTo-CSV -Delimiter "`t" -NoTypeInfo | Select -Skip 1 | Clip

#Open Excel, create a blank workbook, and paste the data in at cell A1
$XL = New-Object -ComObject Excel.Application
$WB = $XL.Workbooks.Add()
$XL.Visible = $true
$XL.ActiveSheet.Cells.Item(1).PasteSpecial()

Upvotes: 3

Joey
Joey

Reputation: 354416

You can easily do this with nested loops:

$(foreach ($a in 'Single Load','Uniform distributed load') {
  foreach ($b in 'MAT1','MAT2','MAT3') {
    foreach ($c in 'Country A','Country B','Country C') {
      New-Object -Prop @{Column1=$a;Column2=$b;Column3=$c}
    }
  }
}) | Export-Csv foo.csv -NoTypeInformation

The CSV file can then be opened by Excel.

Upvotes: 2

Related Questions