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

Question

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:

This is what I'm trying to get:

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

score 1 · Accepted Answer · 2019-02-07 18:40:04Z

1

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
    }
}

edited Feb 7, 2019 at 18:40

answered Feb 7, 2019 at 17:21

user6811411

Sign up to request clarification or add additional context in comments.

1 Comment

user6811411 Over a year ago

Thanks for feedback, I just realized it shoukd be ` Where-Object EPROD -eq 'HOP' `

AdminOfThings · Accepted Answer · 2019-02-07 16:13:51Z

0

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

answered Feb 7, 2019 at 16:13

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

1 Comment

brownbe Over a year ago

This wasn't quite what I was looking for but thank you for the submission. I will be sure to provide an example of my actual data in any of my future questions.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related