Author Posts

May 28, 2016 at 4:37 am

Hello Powershell.org. I have been searching google all day for an answer to this and I am just not getting what I need so hopefully somebody here can offer some assistance. The short explanation is that I have 2 arrays that each contain some duplicate info. See below
$ObjColl

Name TotalUsers TotalInbox
—- ———- ———-
IT 3 3
Accounting 4 2
Marketing 2 1

And the second one....
$ObjColl1

Name TotalUsers TotalInbox
—- ———- ———-
Accounting 2 1
Marketing 2 1

So when I add them together:

$Final += $Objcoll
$Final += $Objcoll1

I get the following output:
$Final

Name TotalUsers TotalInbox
—- ———- ———-
IT 3 3
Accounting 4 2
Marketing 2 1
Accounting 2 1
Marketing 2 1

What I actually want to do is aggregate the totals rather than doing a literal "combining" of the arrays. How can this be accomplished? Example of desired output below:

$Final

Name TotalUsers TotalInbox
—- ———- ———-
IT 3 3
Accounting 6 3
Marketing 4 2

Thanks in advance for any assistance!

May 31, 2016 at 1:02 pm

You would have to compare the actual values in the array. It may or may not be the most efficient way, but you could use a nested foreach loop such as:

Foreach ($name in $ObjColl)
{
      Foreach ($name1 in $ObjColl1)
      {
            If ($name.Name -eq $name1.Name)
            {
                    $row = new-object PSObject -Property @{
                        Name = $name.Name;
                        TotalUsers = ($name.TotalUsers + $name1.TotalUsers);
                        TotalInbox = ($name.TotalInbox + $name1.TotalInbox)
                    }

                    $Final += $row
            }
       }
}

This is assuming that the TotalUsers and TotalInbox columns are formatted as integers and not strings.

$Final

Name TotalUsers TotalInbox
—- ———- ———-
Accounting 6 3
Marketing 4 2

  • This reply was modified 2 years, 2 months ago by  Edmond Yee.

May 31, 2016 at 1:12 pm

You don't need the first two custom objects as you already have them but I needed to build them for example.


$one = 'IT','Accounting','Marketing' | % {

[pscustomobject]@{

name = $_
totalusers = (1..9) |Get-Random
totalinbox = (1..9) |Get-Random

}

}



$two = 'IT','Accounting','Marketing' | % {

[pscustomobject]@{

name = $_
totalusers = (1..9) |Get-Random
totalinbox = (1..9) |Get-Random


}

}



$total = $one + $two

$total | group name | %{

$n = $_.name
$group = $total | ? {$_.name -eq $n}

[pscustomobject]@{

name = $n
totalusers = ($group.totalusers | Measure-Object -Sum).sum
totalinbox = ($group.totalinbox | Measure-Object -Sum).sum



}

}

  • This reply was modified 2 years, 2 months ago by  Dan Potter.

May 31, 2016 at 1:48 pm

Sorry I misunderstood the question, I thought you wanted ONLY duplicates, do what Dan said 🙂

May 31, 2016 at 2:17 pm

$ObjCol = "
Name TotalUsers TotalInbox
IT 3 3
Accounting 4 2
Marketing 2 1" -split "`n"

$ObjCol2 = "
Name TotalUsers TotalInbox
Accounting 2 1
Marketing 2 1 " -split "`n"

$groups = $ObjCol,$ObjCol2 | ForEach-Object {
    ConvertFrom-Csv -InputObject $_ -Delimiter ' '} | 
    Group-Object -Property name

$groups | ForEach-Object {
    $name = $_ | Select-Object -ExpandProperty Name
    $measure = $_.Group | Measure-Object -Property totalusers,totalinbox -Sum
    [PSCustomObject]@{
        Name = $name
        TotalUsers = $measure[0].sum
        TotalInbox = $measure[1].Sum
        }} | Format-Table -AutoSize

# Results
# Name       TotalUsers TotalInbox
# ----       ---------- ----------
# IT                  3          3
# Accounting          6          3
# Marketing           4          2