Wednesday, July 15, 2015

Excel: Find differences between two columns

The problem is this... you have a column of stuff (in my case it is usually user names).  You do whatever you are supposed to do with that column of stuff but find that some of it is not valid data (user doesn't exist) so you create a second column of the data you know to be valid.  Now you want to compare those two columns and find the DIFFERENCE so that you know which pieces of the first column are not valid.

When I sought a solution on the internet, people responded with a whole bunch of ways to find the items that are in common between the two columns.  They did not seem to understand that I was looking for the difference.  So... for those of you who have ever needed to find the difference between two columns here you go:

=IF(ISERROR(NOT(MATCH(A1,$B$1:$B$11,0))),A1,"")

This assumes that your two columns are column A (the orignal list) and column B (your shorter list of items that you know for a fact are in column A) Change the numeral 11 to whatever row is at the end of your second column (column B).

Put this function into another column (like column C) and then drag it down to fill the fields through the end of the sheet.  You will get results that look like this:


Column A Column B Column C
item1 item1
item2 item10
item3 item11
item4 item12 item4
item5 item13
item6 item15
item7 item2 item7
item8 item3
item9 item5 item9
item10 item6
item11 item8
item12
item13
item14 item14
item15


The third column, the one that tells me what is in column A but not in column B is the result that I'm looking for.  If you have this same issue then enjoy.

If this helped you then please give a thumbs up, or leave a comment, or link back to this blog.
Thanks!

Countdown to Reboot - vbScript to inform users of impending reboot

This was another script that I was surprised I couldn't get from the web in under 5 minutes.  I ended up having to write it myself.

The use case is that we have computers sitting in a "Pending Reboot" state (you'll have to look that up if you don't know what that is) and we want to remediate them.  To do so we'll send a reboot but it will have a 90 minute countdown to let the users close things down and generally not be surprised.



The notifications from shutdown.exe were sufficient because it doesn't warn often enough.  If set to 5400 (seconds) it would warn immediatly but not warn again until 5 minutes and even the immediate warning only pops up in the systray for a few seconds.  That means, generally, that the users would only get a 5 minute warning.

The notifications from SCCM only show up when SCCM initiates the reboot.  So... only if SCCM is doing software updates or something like that.  Unfortunately those things don't happen / don't work if the computer is sitting in a pending reboot state.

This script is very "in your face" about things.  It keeps nagging the user with increasing frequency until either the cscript process is killed or the computer is rebooted.

So, if you need it please enjoy but give credit where its due.

'-------------------------------------------------
' 90MinToReboot.vbs
' by Mark Randol
' randoltech.blogspot.com
'-------------------------------------------------

DIM iMins
DIM iMinutesLeft
DIM iSecondsLeft

SET WshShell = WScript.CreateObject("WScript.Shell")

'-------------------------------------------------
' Set the number of minutes you want to countdown
' in the iMins variable on the next line
'-------------------------------------------------
iMins = 91 'number of minutes to countdown before reboot


'-------------------------------------------------
' Initialize Variables
'-------------------------------------------------
iMinutesLeft = iMins
iSecondsLeft = iMinutesLeft * 60
tRebootTime = DateAdd("s",iSecondsLeft,Now) 'Calculates the time for the reboot
sRebootTime = FormatDateTime(tRebootTime,3) 'Converts the reboot time to a string for display purposes


'-------------------------------------------------
' Warn at intervals in the if statements
'-------------------------------------------------
DO WHILE iSecondsLeft > 0 'warn user that the computer will reboot in x seconds
    iMinutesLeft = int(iSecondsLeft/60) 'calculate minutes left for display in the dialogues
    sMessageTitleMin =  "Rebooting in "& iMinutesLeft & " minutes." 'set up the dialogue title for minutes left
    sMessageTitleSec =  "Rebooting in "& iSecondsLeft & " seconds." 'set up the dialogue title for seconds left
    sMessageBodyMin = "Your computer is in a 'Pending Reboot' state." & vbCrLf & "This state is usually caused by software updates or new software installs." & vbCrLf & "For the health and security of your computer's operating system it must be rebooted.  You can wait for this countdown to finish or you can reboot the computer yourself, whichever is more convenient for you."& vbCrLf & vbCrLf & "Your computer will be rebooted in " & iMinutesLeft & " minutes." & vbCrLf & "Reboot will happen at aproximately " & sRebootTime & vbCrLf & vbCrLf &"Please save all your work." 'set up the dialogue body for minutes left
    sMessageBodySec = "Your computer is in a 'Pending Reboot' state." & vbCrLf & "This state is usually caused by software updates or new software installs." & vbCrLf & "For the health and security of your computer's operating system it must be rebooted.  You can wait for this countdown to finish or you can reboot the computer yourself, whichever is more convenient for you."& vbCrLf & vbCrLf & "Your computer will be rebooted in " & iSecondsLeft & " seconds." & vbCrLf & "Reboot will happen at aproximately " & sRebootTime & vbCrLf & vbCrLf &"Please save all your work." 'set up the dialogue body for seconds left
    if iSecondsLeft = 5400 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 90 minutes
    if iSecondsLeft = 4800 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 80 minutes
    if iSecondsLeft = 4200 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 70 minutes
    if iSecondsLeft = 3600 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 60 minutes
    if iSecondsLeft = 3000 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 50 minutes
    if iSecondsLeft = 2400 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 40 minutes
    if iSecondsLeft = 1800 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 30 minutes
    if iSecondsLeft = 1200 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 20 minutes
    if iSecondsLeft = 600 then wshShell.popup sMessageBodyMin,297,sMessageTitleMin,64 'warn at 10 minutes
    if iSecondsLeft = 300 then wshShell.popup sMessageBodyMin,59,sMessageTitleMin,64 'warn at 5 minutes
    if iSecondsLeft = 240 then wshShell.popup sMessageBodyMin,59,sMessageTitleMin,64 'warn at 4 minutes
    if iSecondsLeft = 180 then wshShell.popup sMessageBodyMin,59,sMessageTitleMin,64 'warn at 3 minutes
    if iSecondsLeft = 120 then wshShell.popup sMessageBodyMin,59,sMessageTitleMin,64 'warn at 2 minutes
    if iSecondsLeft = 60 then wshShell.popup sMessageBodyMin,25,sMessageTitleMin,64 'warn at 1 minutes
    if iSecondsLeft = 30 then wshShell.popup sMessageBodySec,9,sMessageTitleSec,64 'warn at 30 seconds
    if iSecondsLeft = 20 then wshShell.popup sMessageBodySec,9,sMessageTitleSec,64 'warn at 20 seconds
    if iSecondsLeft = 10 then wshShell.popup sMessageBodySec,9,sMessageTitleSec,64 'warn at 10 seconds
    if iSecondsLeft = 9 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 9 seconds
    if iSecondsLeft = 8 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 8 seconds
    if iSecondsLeft = 7 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 7 seconds
    if iSecondsLeft = 6 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 6 seconds
    if iSecondsLeft = 5 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 5 seconds
    if iSecondsLeft = 4 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 4 seconds
    if iSecondsLeft = 3 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 3 seconds
    if iSecondsLeft = 2 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 2 seconds
    if iSecondsLeft = 1 then wshShell.popup sMessageBodySec,1,sMessageTitleSec,64 'warn at 1 seconds
    wscript.sleep(1000) 'wait 1 seconds
    iSecondsLeft = DateDiff("s",Now,tRebootTime) 'recalculate the amount of seconds remaining before sending back to the top of the loop
LOOP


'-------------------------------------------------
' Reboot the computer
' Uncomment the shutdown line below and 
' remove the wshShell.popup line
'-------------------------------------------------
'WshShell.Run "shutdown.exe -r -f -t 0", 1, true
wshShell.popup "Your machine would have just rebooted if the command were not commented out.",0,"Reboot just happened"

There is a safety on this script, look at the last couple of lines, the reboot is commented out so you need to remove that comment if you actually want your computer(s) to reboot.

Wednesday, July 1, 2015

WinPE Version Numbers


I needed to add some PE drivers from Dell into my WinPE but, unfortunately, the person that set up the current WinPE has left the company so I had to determine for myself which WinPE version was used.  To do so go to the properties of your Boot Image and choose the "Imagess" tab.



WinPE Version
OS Version in Boot Image Properties
Operating System from which the WinPE is derived
WinPE 1.5
5.1.x
Win XP SP2
WinPE 2.0
6.0.x
Vista
WinPE 3.0
6.1.7600.16385
Windows 7
WinPE 4.0
6.2.x
Windows 8
WinPE 5.0
6.3.x
Windows 8.1?