Highlight Duplicate Records in an Excel List

With Excel’s conditional formatting, you can highlight cells based on specific rules. There are some built-in rules available, and you can use formulas to create your own formatting rules.

Watch the Video

To see the steps for setting up the conditional formatting, watch this short video. The written steps are below the video.
To download the sample file, go to my Contextures website: Highlight Duplicate Records in a List

Highlight Duplicates

In this example, we want highlight duplicate records in a table. There is a built-in rule for highlighting duplicate values in a single column, but nothing that will check an entire row.

built-in rule for highlighting duplicate values
built-in rule for highlighting duplicate values

So, we’ll create our own rule, and it will require a new column on the worksheet, before we add the conditional formatting.

Concatenate the Data

In the sample data, there are two identical rows, and these should be highlighted after we apply our conditional formatting.

two identical rows in sample data
two identical rows in sample data

The first step is to use the CONCATENATE function to combine all the data into one cell in each row.

Add a new heading in cell G1 – AllData – and in cell G2, enter this formula, to combine the data from all the cells in that row.

=CONCATENATE(A2,B2,C2,D2,E2,F2)

formula to combine the data
formula to combine the data

Next, copy the formula down to the last row of data.

Apply the Conditional Formatting

Then, a conditional formatting rule is set, to color the rows that are duplicate records. We’ll use the COUNTIF function to check for duplicates in the AllData column.

=COUNTIF($G$2:$G$8,$G2)>1

create new conditional formatting rule
create new conditional formatting rule

If there is more than one instance of a data combination, that indicates a duplicate row, and the cells in columns A:F will be coloured. The two rows with duplicate records are highlighted, so our conditional formatting formula worked!

duplicate records are highlighted
duplicate records are highlighted

Download the Sample File

For detailed instructions, and to download the sample file, go to my Contextures website: Highlight Duplicate Records in a List

_________________________

Limit Date Range with Excel Data Validation

Yesterday, one of my clients emailed to let me know that she was having trouble entering January dates in a file that I had created.

My first guess was that there was an issue with the regional settings, because her company uses the dd/mm/yyyy format.

But when I tried entering a January date, with my mm/dd/yyyy settings, I got an “Invalid date” message too.

datavaldates02

Limit Dates with Data Validation

The date that I had entered – 1/3/13 – was a valid date and in a valid format, so I checked the data validation settings. And that’s where I found the problem.

The cell had been restricted to dates from 60 days prior to the current date:

=TODAY()-60

and up to 60 days after the current date:

=TODAY()+60

datavaldates01

Fix the Problem

Those date range settings had made sense when we set up the file. The date range limits prevented people from accidentally entering strange dates, such as mistyping a year – 2031 instead of 2013, for example.

Do you ever find records like that in your database or workbook? It can really mess things up!

Anyway, a simple change to the data validation formula fixed the problem. Instead of 60 days, I changed the formulas to 120 days.

=TODAY()-120

and

=TODAY()+120

It still prevents those year typos, but gives my client a bigger window for entering data in the file.

Video: Prevent Invalid Dates

In this video, three different data validation methods are used to validate dates. From the Allow drop down in the data validation settings, the following options will be used:

  • Specify a starting date and an ending date. (Date option)
  • Show a drop down list of valid dates (List option)
  • Create a rule in a custom formula (Custom option)

Video Timeline:

  • 0:00 Drop Down List of Dates
  • 3:04 Set a Date Range
  • 5:43 Check the Year
  • 6:55 Get the Sample File

More Data Validation for Dates

For more examples of data validation for dates, you can visit the Excel Data Validation – Dates page on my Contextures website.

_____________

Create Amazon Affiliate Links in Excel

This week, Dick Kusleika posted his Amazon Linkerator – an Excel file lets you create links to Amazon products.

First, you find a product on Amazon, and copy its web page URL. Then, open the form, enter a product code and description, and it creates a link for you.

linkerator1

It’s very fancy, and you can download the sample file, to try it for yourself. It uses an Excel UserForm, and you can modify the code to add your own information.

Jimmy Pena has an Amazon Link Builder too, and you can see the details here: Amazon Link Builder

Low Tech Amazon Link Builder

I build Amazon links too, and you can see lots of them on my Excel Book List page.

To create my links, I use worksheet formulas, instead of a fancy UserForm. If you like things simple, you can try this method.

You enter the product code and product title, and then copy the link or the HTML code, and paste it into your blog post or web page.

WARNING: Check the latest information on the Amazon website, to be sure that these short links are still permitted. Their policies can change at any time.

amazonlink01

How It Works

First, a link is created in cell B5, from the Amazon URL, the product code (ASIN) and the Tracking ID.

=https://amzn.com/ & ProdCode & “?tag=” &TrackID

Note: Cell B3 is formatted as Text, because some codes start with a zero, and you don’t want Excel to remove those.

amazonlink02

Then, that link is used in the to create the HTML code in cell B7.

The four HTML cells have snippets of text that are required for building the HTML code. The formula in cell B7 combines the product title and the link, with four snippets of text.

=HTML_01 & ProdTitle & HTML_02 & ProdLink
& HTML_03 & ProdTitle & HTML_04

Download the Sample File

To test the Amazon link formulas, you can download my sample file, from the Excel Sample Files page on my Contextures website. In the Functions section, look for FN0025 – Build Amazon Affiliate Links

____________________

Validate Entries in Excel Combo Box

One of my Excel sample files lets you double-click on a cell that has a data validation list, and a combo box pops up. The combo box has advantages, because you can set the font size, and the number of rows in the drop down list, and the entry autocompletes as you type.

Continue reading “Validate Entries in Excel Combo Box”