Quick and Dirty Array Subtraction – Otherwise known as “Just say NO to VLOOKUP”


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 (2)

  1. Nice
    i am in same boat as you i hate excel(for the most part) 🙂

    1. 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.

Skip to main content