Filter duplicate rows based on cell value

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I've done some "slight of hand" on his one.

First, I changed the data range to an Excel table. Excel tables have a lot of benefits: they know how big they are so you don't have to adjust formulas when you change the number of rows. They copy formulas down automatically. They used column headers instead of addresses in the formulas.

I added a helper column called Composite - since what makes a record unique is the combination of Product and Packaging, I concatenated them together into a "Composite Key" to see if the record is unique.

Then I found the maximum date for the composite key: =MAX(IF([Composite]=[@Composite],IF(NOT(ISBLANK([Package Date])),[Package Date],FALSE))) - this is entered as an array formula. The explanation of array formulas and this specific example is explained here: http://www.utteraccess.com/wiki/Array_Formulas. Essentially, this formula looks at the composite and considers only dates associated with that composite.

The Where Found Column has another array formula: =MATCH([@[Max Date]],IF([Composite]=[@Composite],[Max Date],FALSE),0) - This finds where the max date occurs for that composite key.

The Use Column has the formula: =MATCH([@[Where Found]],F:F,0)=ROW() - this formula is true for the first occurrence of the item and false otherwise. For example the value 1 appears twice in column F. The one in row 2 "counts;" the one in row 5 doesn't. I use this as a filter in the pivot table.

Then I make a pivot table based on this. If you right click on a field and go to field settings, you can select none for subtotals, then go to the layout & print tab and select show items in tabular form. I did this for row labels, product, packaging and date. That's why they wind up on the same line in the pivot table.

What is good about this is that you can clear out the contents of the table by selecting rows 2 to 6 (the data rows, not the headers), right clicking on them and selecting Delete -> Table Rows from the menu. Then you can copy and paste your real data into Cell A2. The table will "remember" the formulas contained in Columns D:G and copy them down automatically. Then right click in the pivot table and select refresh from the menu.

Here is an article that will get you started on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel - after a while you will wonder how you ever managed to get along without them.

You can hide the helper columns if you wish.