Remove duplicates is a quick way to obtain a unique list of records; every time the source data is updated, though, you’ve got to copy and ‘remove duplicates’ again. There’s a better way! Use Power Query to create a unique list that can be updated with a single click.

To use Power Query, first highlight the column containing the information for which you want a list of unique values.

On the “Data” ribbon bar, select “From Table/Range”

You’ll be asked to confirm where the source data is located – the highlighted selection should appear. Click “OK” to continue.

A new window will open – the Power Query Editor. On the “Home” ribbon bar, click on “Remove Rows” and select “Remove Duplicates”

A unique list of values has been extracted in the Power Query editor – but you want to insert that data into your spreadsheet. Click the drop-down by “Close & Load” then select “Close & Load To …”

Now you can select where you want your list of unique values to appear – I am creating a table in an existing worksheet. Click “OK” to insert the unique list.

Voila, I now have a unique list.

What happens when new records are added to my source data? The Power Query table does not automatically update as values are added to the source data. On the “Data” ribbon bar, click “Refresh All” to update the unique value list.

 

I mentioned yesterday that we’re creating groups based on the upper level manager through whom individuals report. Since my groups are based on the upper level managers, I need to be able to identify when a new individual pops into the list of upper level managers. Real upper level management doesn’t change frequently, but unfilled positions create gaps in the reporting structure. I call the manager before the gap the highest-ranking person in that vertical and that individual’s reporting subtree becomes a group.

Determining if values from one list appear in another list is easy in Microsoft Access – it’s an unmatched query. I’d rather not have to switch between the two programs, and I was certain an Excel formula could do the same thing. It can!

The formula is:

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

And it does flag any manager from column H that does not appear in my list of upper level managers.

I am also able to filter my spreadsheet to display only records where the upper level manager does not appear in my summary table.

What is my formula doing? It is a combination of three functions

=IF(ISNA(VLOOKUP(H2,SOA6MgrSummary!A:A,1,FALSE)),”Not in Manager Summary”,””)

It starts with the IF function – a logical comparison – which is used as if(Test,ResultIfTestIsTrue, ResultIfTestIsFalse).

If the test is true, “Not in Manager Summary” will be put into the cell. If the test is false, nothing (“”) will be put into the cell.

The test itself is two functions. I’ve documented the VLOOKUP function previously, but briefly it searches a range of data for a specific value. If the value is found, it returns something. If the value isn’t found, it returns N/A.

In conjunction with the VLOOKUP, I am using the ISNA function. This function is a logic test – it returns TRUE when the value is N/A and FALSE otherwise.

So my formula says “Look for the value of cell H2 in column A of the SOA6MgrSummary tab. If the result is N/A, put ‘Not in Manager Summary’ in this cell, otherwise leave this cell empty”.

 

For a project, we need to divide the entire company into groups. I chose organizational structure because it’s easy – I can determine the reporting structure for any employee or contractor, and I can roll people into groups under which ever level of manager I want.

The point of making groups, though, is to have close to the same number of people in each group. While I can use COUNTIFS to count the number of people who report up through each manager, I need to add those totals for each group of managers to determine how many individuals fall in each group. How many employees are included in Group 0?

This is actually quite easy – just like count has a conditional counterpart, countifs, sum has a conditional counterpart sumifs

The usage is =SUMIFS( Range Of Data To Sum, Range Of Data Where Criterion Needs To Match, Criterion That Needs To Match)

You can use multiple criteria ranges and corresponding criteria in your conditional sum — =SUMIFS(SumRange,CriterionRange1,CriterionMatch1,CriterionRange2,CriterionMatch2,…,CriterionRangeN,CriterionMatchN).

I only have one condition, so with a quick listing of the groups, I can add a column that tells me how many individuals are included in each group.

Bonus did you know – instead of specifying a start and end cell for a range, you can use the entire column. Instead of saying my “Range of data to sum” is B2:B101, I just used B:B to select the entire “B” column.

Viewing the values, I can see that my group size is not consistent.

As I adjust the group to which the manager is assigned, these sums are updated in real-time.

 

I frequently need to correlate two sets of data – generally information about accounts, where the logon ID will be found in both data sets. I’ve imported my information into Access, defined a relationship between the two tables, and used a query to correlate my data. I’ve written quick scripts to pull the data into an associative array for correlation. These are not quick approaches.

Using the VLOOKUP function in Excel, you can search through data in rows and retrieve values from the record’s other columns. HLOOKUP provides the same function, but searches data in columns and retrieves values from the record’s other rows (Vertical Lookup and Horizontal Lookup).

Today, I have a list of individuals with their reporting structure and need to identify which accounts have Skype for Business provisioned.

The Skype user list is, unfortunately, comes from a different program.

To lookup user IDs from the first table against the Skype info in the second table, I use =VLOOKUP(B2,S4BInfo!A:B,2,FALSE)

The first parameter in the function is the information you want to find, the second parameter is the area where you’ll be looking for the data, the third parameter is the column in that range that you want to return when a match is found. The fourth parameter indicates if you want to find the closest match (‘TRUE’) or an exact match (‘FALSE’). So my formula says “find the value in B2 within columns A and B of the SBInfo tab. Return the value from column 2 of that range, and I want an identical match”.

Note that the third parameter column number may not match the column number in the sheet – if I used the range C:D from the table below, I would still want to return the data in column 2 because my target data is still the second column in the search range.

Fill down and I have a single table that contains both the reporting information that I needed and a column indicating if the individual has a Skype for Business account

 

I use Excel’s COUNTIF function a LOT for reporting. When I want to count the number of transactions that occurred per day (or during a date range), it’s easy enough to get the list of IF’s to count. But when I need to find the occurrence of different text strings, I need a unique list of the strings first. “Remove duplicates” quickly exactly what I need.

In this example, I have a list of all employees and contractor’s departments and titles – I want to know how many people are in each department and how many people have each title. Removing duplicates modifies the data, so the first step is to make a copy of the spreadsheet. Highlight the data. Select “Data” on the ribbon bar, then select “Remove Duplicates”

Select the column(s) where you want to remove duplicate data. This could be exact duplicates across multiple columns (e.g. the unique “City, State” combinations), or (in this case) I just want a unique list of departments. Click OK.

A summary will be displayed showing you how many records were removed and how many unique values remain.

Now that I have a complete listing of departments, I can use my COUNTIF function to show how many employees and contractors are in each department.

Remove duplicates only deletes records within the highlighted data. Here, I have a list of all employee titles next to the department and count info we just created. If I highlight just the ‘Title’ data and click “Remove Duplicates”, the department and count information is left unchanged.

Now I have a unique list of titles as well.

 

Reading through large tables of data is inefficient – it’s time consuming, error prone, and just not a heap of fun. Graphs are one way to visualize data – allowing you to quickly spot trends, outliers, etc. Excel offers another way to visually enhance data to make it more comprehensible – conditional formatting. Where some charts and graphs obscure the underlying data, conditional formatting allows the exact value to be quickly identified.

Highlight your data. On the ribbon bar, select “Home” and click the drop-down for “Conditional Formatting”.

Select the logic to determine which cells are highlighted – we’ll go through a few examples here, but click around on your own! To highlight cells that are higher than some value, select “Highlight Cell Rules” and then select “Greater Than”.

In the window that appears, enter the number and select the colouring scheme. The prepopulated number will be the average of the highlighted data. The changes are applied as you select formatting options, so you have an idea what it’ll look like ahead of time. In this case, there are still a lot of values higher than 125. I could increase my number to reduce the number of highlighted cells. When you have finished composing your formatting rule, click OK.

And the format is applied to your data. You can apply multiple formats – add another format to turn anything below 25 green, make values between 100 and 124 yellow. Whatever you want.

If you need to change your formatting rules, click on the “Conditional Formatting” drop-down and select “Manage Rules”.

If your rules do not appear, change “Current Selection” at the top to “This Worksheet”.

You can also define custom rules. From the “Conditional Formatting” drop down, select “New Rule”.

Again, select the logic used to determine which cells are formatted. Here, I am highlighting duplicated values. Click “Format” to define how the highlighted cells should appear. Click “OK” to apply the formatting to your spreadsheet.

Now every duplicated record is in green with a strike through the value.

Formatting rules can be nuanced – here I am creating a custom formatting rule that uses a three-colour gradient based on where a value falls within a range.

Now you can quickly compare each value by it’s colour.