Blog Archives

Tom’s Tutorials For Excel: Deleting Duplicate Records

Tom’s Tutorials For Excel: Deleting Duplicate Records

When you have a list of data, sometimes it is not enough to simply delete rows with duplicated information based only on the items in one column. Multi-column lists can have duplicated records based on the fact that every item in every column of a row’s data matches that of another row’s entire data,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Tom’s Tutorials For Excel: Updating a Comment to List Unique Items

Here is how you can automatically update a comment that shows unique items in sorted order from the larger worksheet list, whenever a new unique item is added to that list in the worksheet.

In the pictured example,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array

Tom’s Tutorials For Excel: Deleting or Keeping Rows With a Multiple Criteria Array

Here’s a “this way or that way” pair of macros that use an array to hold a set of items to determine which rows you want to keep or delete. In the picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Concatenating Multiple Items For Unique Partners

Tom’s Tutorials For Excel: Concatenating Multiple Items For Unique Partners

Here’s how you can take several associated items that are located in their own cells, and concatenate them as a single string to be housed into one cell.

In the picture, you see Primary Items in column A,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Getting Unique Items From a List

Tom’s Tutorials For Excel: Getting Unique Items From a List

In the following picture, you see a list of names, many of which are repeated 2, 3, or more times. Here’s how you can create a shorter list to show each unique item only once.

To create a list of unique names in column C based on the list of many repeated names in column A,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Tom’s Tutorials For Excel: Listing Unique Items From Multiple Ranges

Here’s a method for looping through possible named ranges, and if they exist, compiling a unique list of items found among all those ranges.

I have the word “possible” here as a design wrinkle to demonstrate an option of convenience.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Sets of Identical Items

Tom’s Tutorials For Excel: Summing Sets of Identical Items

Here’s a formula method to subtotal numbers in one column for each set of items in another column.

A company’s quarterly sales are listed, with varying salespeople each quarter. At each change in quarter in column A,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences

Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences

Here’s how you can format differences in a long list when comparing it to a shorter list.

In the picture, column A holds a comprehensive list of names, and column B contains a shorter list where some names are found in column A (colored green),

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Keep a Running Count of Duplicate Entries

Tom’s Tutorials For Excel: Keep a Running Count of Duplicate Entries

In cases where duplicate entries are expected, you can easily keep a running count of each entry with a formula to tell you how many times the entry exists in your list. There is a number next to each entry to tell you how many times in the list that particular entry has thus far appeared.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Unique List From Multi-Column Table

Tom’s Tutorials For Excel: Unique List From Multi-Column Table
You may know that from a single-column list containing repeated items, you can extract a list of unique items using Advanced Filter. But what if you want to extract a unique list from a table having many columns of repeatedly listed items?

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Identify unique and duplicate entries

Tom’s Tutorials for Excel: Identify unique and duplicate items in a list.

Today we have the second of Tom Urtis’ super tips to include screen shots.  Enjoy!

In the example, Conditional Formatting is applied to range A2:A12.
Select range A2:A12 and then…

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Fill empty cells with value from above

Tom’s Tutorials for Excel: Fill empty cells with value from above.
Today we have great new tip from Tom Urtis complete with screen shots. Enjoy & Excel!

Step 1
Select the range you want to work with,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials for Excel: Prevent Duplicate Entries

Tom’s Tutorials for Excel: Prevent duplicate entries

There are times you will be using Excel that duplicate entries could be disastrous such as lists of employee’s Social Security numbers, patient’s confidential medical records or student’s contact information information to name a few. Here Tom Urtis explains how to ensure that never happens:

If you are entering a list of values that are meant to be unique,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,