Freigeben über

Quick and Dirty Array Subtraction - Otherwise known as "Just say NO to VLOOKUP"

  • Artikel

Today, I had to subtract one list of users from another list of users.  Some Excel wizards can do this with VLOOKUP, but I spend more time in trial-and-error with VLOOKUP and verifying my results that it would take to just do a CTRL-F for every object.  It's really painful for me. Like, embarrassingly painful.

So, I thought ... How can I do this with PowerShell?  Because, you know. PowerShell.

Here are a couple of quick ways I discovered to do this:

 [array]$BigUserList = (Get-Mailbox -ResultSize Unlimited).PrimarySmtpAddress
[array]$UsersToSubtract = Import-Csv Pilot.Csv -Header PrimarySmtpAddress
[array]$Result = $BigUserList | Select-String $($UsersToSubtract -join "|") -NotMatch

and

 [array]$BigUserList = (Get-Mailbox -Resultsize Unlimited).PrimarySmtpAddress
[array]$UsersToSubtract = Import-Csv Pilot.csv -Header PrimarySmtpAddress
[array]$Result = $BigUserList | ? { $UsersToSubtract -notcontains $_ }

and

 [array]$BigUserList = (Get-Mailbox -Resultsize Unlimited).PrimarySmtpAddress
[array]$UsersToSubtract = Import-Csv Pilot.csv -Header PrimarySmtpAddress
[array]$Result = Compare-Object $BigUserList $UsersToSubtract | ? { $_.SideIndicator -eq '<=' } | ForEach-Object { $_.InputObject }
$Result
b@c.com
d@e.com

You can also test out each method with some basic arrays:

Method 1:

 [array]$BigUserList = @('a@b.com','b@c.com','c@d.com','d@e.com','e@f.com')
[array]$UsersToSubtract = @('a@b.com','c@d.com','e@f.com')
[array]$Result = $BigUserList | Select-String $($UsersToSubtract -join "|") -NotMatch
$Result

b@c.com
d@e.com

arraysubtract-1

 

Method 2:

 [array]$BigUserList = @('a@b.com','b@c.com','c@d.com','d@e.com','e@f.com')
[array]$UsersToSubtract = @('a@b.com','c@d.com','e@f.com')
[array]$Result = $BigUserList | ? { $UsersToSubtract -notcontains $_ }
$Result
b@c.com
d@e.com

arraysubtract-2

 

Method 3:

 [array]$BigUserList = @('a@b.com','b@c.com','c@d.com','d@e.com','e@f.com')
[array]$UsersToSubtract = @('a@b.com','c@d.com','e@f.com')
[array]$Result = Compare-Object $BigUserList $UsersToSubtract | ? { $_.SideIndicator -eq '<=' } | ForEach-Object { $_.InputObject }
$Result
b@c.com
d@e.com

arraysubtract-3

Comments

  • Anonymous
    August 10, 2016
    Nicei am in same boat as you i hate excel(for the most part) :)
    • Anonymous
      August 10, 2016
      Excel is a wonderfully powerful tool (I used to use it for ad-hoc scripting before I really started using PowerShell)--but its advanced functions work a different part of my brain and I have a hard time remembering how to construct things like VLOOKUPs.Besides, this is way faster and much nerdier.