## How to create a unique list using conditional formatting in excel 2007

You may have tried to create a unique distinct list, using conditional formatting with excel 2007, merging duplicates into one distinct value. The problem is that it only highlights all values occurring only once. See picture.

If you highlight all duplicates, excel 2007 highlights all values occurring twice or more. See picture.

But to be able to delete values occuring the second time or more, we need to use another conditional formatting formula. So I thought why not create a conditonal formatting formula that highlights only the cells that needs to be deleted. The end result is a unique (distinct) list. Here is how to do that:

**Highlighting duplicate values occuring the second time or more:**

- Select the range (C2:C12)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formual is true" window.
- Type =IF(COUNT(IF($C$2:$C2=$C2, 1, ""))>1, TRUE, FALSE)
- Click OK!

**Sorting duplicates to bottom**

- Right click on any cell in range C2:C12
- Click "Sort"
- Click "Custom sort..."
- Select "On bottom" in the order column
- Click OK!

Select highlighted values and press "Delete" on keyboard

Download excel example file

highlight-duplicate-values.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

### Category: Conditional formatting

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Highlight dates within a date range using conditional formatting

Question: How do I highlight dates that meet a criteria using conditional formatting? Table of contents Highlight values in a […]Comments(15) Filed in category: Conditional formatting, Excel

Highlight closest value in excel

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]Comments(10) Filed in category: Conditional formatting, Excel

How to highlight max and min value in every month in excel

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]Comments(7) Filed in category: Conditional formatting, Dates, Excel

Comments(5) Filed in category: Conditional formatting, Cross reference table, Excel, Schedule, Templates

Create a dynamic border to your list using excel conditional formatting

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]Comments(5) Filed in category: Conditional formatting, Excel

Highlight duplicates in a filtered excel defined table

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]Comments(4) Filed in category: Conditional formatting, Excel, Excel table

Search with conditional formatting

I would like to show you how to search a table using conditional formatting. The criteria highlight matching column and […]Comments(4) Filed in category: Conditional formatting, Excel

Quickly highlight records in a list using multiple criteria in excel

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]Comments(3) Filed in category: Conditional formatting, Excel, Records

Use a drop down list to change conditional formatting

You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list. Here is […]Comments(3) Filed in category: Conditional formatting, Drop down lists, Excel

### 3 Responses to “How to create a unique list using conditional formatting in excel 2007”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Zen Archery

In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are

97,101,139,41,37,31,29,89,23,19,8,13,

131,19,73,97,19,139,79,67,61,17,113,127

Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8.

Zen Archery

In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are

97,101,139,41,37,31,29,89,23,19,8,13,

131,19,73,97,19,139,79,67,61,17,113,127

Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8. Zen Archery

In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are

97,101,139,41,37,31,29,89,23,19,8,13,

131,19,73,97,19,139,79,67,61,17,113,127

Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8. the result is 27 groups of 5 number, the optimal combination how can I do that in excel 2003

or do pivot table on the list it is much faster and easier

Rona,

you are right! Sometimes you just want to examine the values having duplicates and then take the appropriate actions.