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
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
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
Nice
i am in same boat as you i hate excel(for the most part) 🙂
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.