CODEWITHSUNDEEP
excelpowershellsum
brownbe
brownbe

Reputation: 25

Using Powershell, how can I sum the values in an excel column and sort by the value of another variable?

I'm trying to import data from an Excel spreadsheet into a Powershell script and add up the values of one column while sorting the results based on the value of another column. The example below probably illustrates this better than I can in words.

The end goal is to add up the values in the SUBMIT column for each Agent and then export that data to a separate spreadsheet, but I can't figure out the first step of adding the values together and grouping them with the Agent.

foreach ($data in (Import-Excel "C:\prod\actstest.xlsx")) {
    $result += $data.Submit
}

Write-Host $result

When I run the block of code above, it successfully adds up the values in the Submit column but doesn't sort it based on Agent. When I run the below code it successfully sorts them by Agent but doesn't add them up. There must be a way to combine these together but I've been unsuccessful in figuring it out to this point.

$testdata = Import-Excel "C:\prod\actstest.xlsx"

ForEach ($Agency in $testdata) {

    $AgentID = $Agency.AGENT 
    $Product = $Agency.EPROD
    $Submit = $Agency.SUBMIT

    [int]$SubNum = [convert]::ToInt32($Submit, 10)

        ForEach($AgentID in $Agency) {

            If ($Product -eq "HOP") {

                $Agency | Group-Object AGENT | %{
                New-Object psobject -Property @{
                Agent = $_.Name
                Sum = ($_.Group |  Measure-Object -property SUBMIT -Sum).Sum

                }

            }

        }

    }

}

This is what I'm getting: 

Sum Agent 
--- ----- 
  2 05007
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05008
  1 05018
  1 05018
  1 05018
  1 05018
  1 05018

This is what I'm trying to get:

Sum Agent 
--- ----- 
  2 05007
 13 05008
  5 05018

Upvotes: 2

Views: 1987

Answers (2)

user6811411
user6811411

Reputation:

It's quite difficult to deduce what you are after without seeing your original data.

This might do:

## Q:\Test\2019\02\07\SO_54576574.ps1
$testdata = Import-Excel "C:\prod\actstest.xlsx"

$testdata | Where-Object EPROD -eq 'HOP' | Group-Object Agent | ForEach-Object {
    [PSCustomObject]@{
        Agent = $_.Name
        Sum   = ($_.Group | Measure-Object -Property SUBMIT -Sum).Sum
    }
}

Upvotes: 1

AdminOfThings
AdminOfThings

Reputation: 25001

Since I don't have your original file and can't really begin to understand fully what you are trying to accomplish, I can only provide a way to do the summation. I created my own array of agents to show how it works:

$agents = @(05007,05007,05008,05007,05008,05009,05018,05008,05009,05017,05009)
$agentsSorted = $agents | sort
$uniqueAgents = $agentsSorted | get-unique

$data = foreach ($uniqueAgent in $uniqueAgents) {
$count = 0
$count = $agentssorted.where({$_ -eq $uniqueAgent}).count
$obj = [pscustomobject]@{Agent = $uniqueAgent; Sum = $count}
$obj
}

$data

Upvotes: 0

Related Questions