Excel – Ex-Shell

Excel Vlookup: Add a Value from a different column when finding matched content in Excel

Scenario: Lets say you have two different tables of data in Excel. You want to not only compare the values, but also bring over additional values from different columns if there is a match.

Example: Lets say you have the following Excel data on Sheet1; Names in Column A and a Numerical Value in Column B.

Sheet1:
1|A    | B
2|Steve| 1
3|Pat  | 4
4|Tom  | 3

And on Sheet2, you want to Compare the name values in the A column on Sheet1 with the name values on Sheet2. If there is a match, you want to fill in the B column on Sheet2 with the numerical value from the B column on Sheet1.

Sheet2:
1|A    | B
2|Steve| ?
3|Pat  | ?
4|Tom  | ?

Solution: In cell B2 on Sheet2, use VLOOKUP:

=vlookup(A2, Sheet1!$A$1:$B$4, 2, False)

The Arguments for the vlookup are broken like this:

A2 =  The value you want to compare on Sheet2
Sheet1!$A$1:$B$4    =    The range you want to compare the value to
 2 =  The Column index for which value should be returned based on a Match
False = Find an Exact Match

Scheduled Task doesn’t allow powershell script to convert/save XLSX file to a CSV file

Scenario: You have a PowerShell script running that will convert an .XLSX document into a .CSV Document. When you run it natively in PowerShell, the script works fine and the document converts. When you attempt to run it in Task Scheduler, it doesn’t convert and create the new document.

Solution: Create a Desktop folder in the following two directories and run the scheduled task again.:

C:WindowsSysWOW64configsystemprofile
C:WindowsSystem32configsystemprofile

Excel Magic – When inserting the cell value of a date into a formula, the date converts into a numeric value and doesn’t display in a date format correctly

Scenario: In Cell A1 you have a date of ‘6/3/2016’. In Cell B1, you have a formula that pulls in the date:

=”Today is “&A1

Instead of getting the value Today is 6/3/2016, you get Today is 41428

Solution: Use the Text function inside your fomula to convert the date from numeric form to date form.

=”Today is “& TEXT(A1,”M/D/YYYY”)

Today is 6/3/2016

Excel Magic – Use a formula to remove characters before and/or after a character of a cell value.

Scenario: You have an excel (CSV) worksheet of undeliverable emails. You need to pull only the email address out of the body of the message. Note: I replaced all carriage returns internal to the cell value by doing a Find and Replace on: <Control J> and replace with .

Example: Cell Value A1:

Delivery has failed to these recipients or groups:
.
.bob@xyz.net<mailto:bob@xyz.net>
. A problem occurred while delivering this message to this email address. Try sending this message again.

Solution:

Use the following Excel Formulas to remove all characters to the left and to the right of the < and >.

In cell B1, remove everything to the left of the ‘<‘ in A1.

=RIGHT(A1,LEN(A1)-FIND(“<“,A1))

Result: mailto:bob@xyz.net>
. A problem occurred while delivering this message to this email address. Try sending this message again.

In cell C1, remove everything to the right of the ‘>’ in cell B1.

=LEFT(B1,FIND(“>”,B1)-1)

Result: mailto:bob@xyz.net

Add a user as the manager of a distribution group without removing existing managers via Exchange PowerShell.

Scenario: Add a user as the manager of a distribution group without removing existing managers via Exchange PowerShell. If you were to use the Set-DistributionGroup command with the -managedby switch, it would remove the existing managers and set the managers you specified in the switch. To add the user jdoe1 as a manager and not remove the existing managers from the distribution group ‘HQ-All Employees’, run the following PowerShell commands below.

$Group = Get-DistributionGroup “HQ-All Employees”
$Managers = $Group.ManagedBy
$User = Get-User jdoe1
$Managers += $User
Set-DistributionGroup “HQ-All Employees” -Managedby $Managers -BypassSecurityGroupManagerCheck

Excel Magic: If you have an Excel sheet full of groups in Column A and wish to add jdoe1 as the manager to each of those groups, copy the Excel formula below and drag it down for the entire list of groups. The value of each cell will create the Powershell command that you will need for each group. Copy the cells (the cell values) and Paste it into Exchange PowerShell. Each command is separated by a ; so the 5 PowerShell commands needed will fit into one Excel Cell and fit on one PowerShell line for each group.

=”$Group= Get-distributiongroup “””&A2&”””; $Managers= $grp.managedby; $User=Get-User jdoe1;$Managers+=$User;Set-DistributionGroup “””&A2&””” -managedby $Managers -BypassSecurityGroupManagerCheck”