Archived Excel 2003 workaround: This example removes adjacent repeated weights from a date-ordered list. For current Excel, see how to remove duplicates and keep the right row and Microsoft's current duplicate-removal guidance.
I have a worksheet with duplicate values that need to be removed. There are two columns of data, with Date in column A and Weight in column B.
The Date values are for each day and extend to the end of June. Some of the Weight values repeat as you can see in the picture. The procedure below keeps the first row in each consecutive run of equal weights, preserving the chronological order.
This historical method uses a helper column and Find All to select the rows to delete. The companion Excel 2008 for Mac example uses a different selection method because its Find dialog differs.
The following procedure worked well for this data set.
Start with the records in ascending order by Date, keeping each date and its weight together.
In cell C2, enter =B2=B1. The result is TRUE when the Weight in B2 equals the cell immediately above it, and FALSE when they differ. Copying it down compares each row with the preceding row. In C2, B1 is the Weight header, so the first numeric weight returns FALSE.
Here's the limitation: FALSE does not mean the weight is unique in the whole list. Sorting by Date does not group equal weights together. If a weight reappears after a different weight, this formula won't flag it. Use this method only when you want to remove consecutive repeats in the chronological list.
Copy the formula down by double clicking the fill handle.
Select all formulas by making C2 the active cell, and use the keyboard shortcut Ctrl+ Shift+Down arrow.
Copy, and Paste as Values.
Find all the TRUE values by using the keyboard shortcut Ctrl+F.
Type TRUE in the Find What box, and click Find All.
Click inside the bottom window of the Find and Replace dialog box, and use Ctrl+A to select all the values found.
Close the Find and Replace dialog box.
Right click on one of the TRUE cells, and from the popup box select Delete...
In the Delete dialog box, select Entire row and then click OK.
Remove column C by right-clicking the header, and click Delete from the popup menu.
What's left is the first row from each consecutive run of equal Weight values, still in chronological order. A weight that reappeared later after a different weight remains in the list.