Macro to compare multiple columns then delete rows with older dates

**azurememories** · 02-23-2011, 12:17 AM

Hello,

I'm trying to write a macro to compare columns on the following data and delete rows with older dates. Here's the logic I am trying to convey based on following data:

Product A 15-Apr-06 14-Apr-07 Support A
Product A 14-Apr-08 13-Apr-09 Support A
Product A 14-Apr-09 13-Apr-10 Support A
Product A 14-Apr-10 13-Apr-11 Support A
Product A 15-Apr-07 13-Apr-08 Support A
Product B 15-Apr-06 14-Apr-07 Support A
Product B 15-Apr-06 14-Apr-07 Support B
Product B 15-Apr-06 14-Apr-07 Support C
Product B 15-Apr-06 14-Apr-07 Support D
Product B 14-Apr-08 13-Apr-09 Support A
Product B 14-Apr-08 13-Apr-09 Support B
Product B 14-Apr-08 13-Apr-09 Support C
Product B 14-Apr-08 13-Apr-09 Support D
Product B 14-Apr-09 13-Apr-10 Support A
Product B 14-Apr-09 13-Apr-10 Support D
Product B 14-Apr-10 13-Apr-11 Support A
Product B 14-Apr-10 13-Apr-11 Support D
Product B 15-Apr-07 13-Apr-08 Support A
Product B 15-Apr-07 13-Apr-08 Support B
Product B 15-Apr-07 13-Apr-08 Support C
Product B 15-Apr-07 13-Apr-08 Support D

I want to compare column A, then column D. If column D has same data, delete all rows but leave row with the latest date in column C. In this example, I've colored the rows I wanted to keep in red.

Please let me know if you would like further clarification and thanks in advance!

**rylo** · 02-23-2011, 01:31 AM

Hi

what about Product B / Support B and Product B / Support C???

rylo

**azurememories** · 02-23-2011, 01:51 AM

since those two supports expired for over one year, i'm not including them. good eyes though, rylo. but humor me, what if we kept those two supports in as well?

**watersev** · 02-23-2011, 08:36 AM

hope this helps, one of the options to do that, save attachment and make sure the following reference is on:

1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. press the "Start" button

Please note that sheets names are hardcoded in macro. In case you change sheet names you'll need to change sheet names in the code as well.

**rylo** · 02-23-2011, 05:43 PM

Hi

Assuming that your data is in columns A:D then:
1) Sort columns A:D where Column A is ascending, Column D is ascending and column C is descending.
2) E1: =SUMPRODUCT(--($A$1:A1=A1),--($D$1:D1=D1)). Copy down as required.

All items that have a 1 in column C are the ones to keep. You could include some headings, and filter to get the ones you want to keep.

HTH

rylo