I am working on excel tables that have duplicates. I have to eliminate the duplicates from loc 3 and 4 (Column 4 and 5) based on earliest start date and latest end date. Duplicates from loc 1 and 2 (Column 2 and 3) SHOULD NOT be removed. For example Tylenol and Pepcid in the following: Only duplicates with a Yes in Loc 3 and 4 must be removed with earliest start date and latest end date (see desired output). I have a lot of complicated tables to do this. Does anyone have a suggestions?

enter image description here

  • Yes this is possible (most likely with VBA). However, SuperUser isn't a code-writing service, so please make some attempts first, and let us know what you tried, and what did/didn't work. Check out how to ask for some more info. – BruceWayne Mar 21 '16 at 16:58
  • Thanks Bruce. Before posting the question, I have tried to remove duplicates, but that does not help with the start and stop date criteria. I did look at other options of removing duplicates in excel on the forum, but they would work if I had one column with dates than two. I think its do able, but do not have the experience to implement it :). – S.Yat Mar 21 '16 at 18:20

I would use Power Query for this. Power Query is a free Add-In from Microsoft for Excel 2010 and 2013, and is built into Excel 2016 on the Data ribbon, under Get & Transform.

Power Query can read from Excel tables or named ranges. It has a Group By function that can calculate Min and Max values for your Start and Stop Date requirements.

For your conditional grouping requirement, I would add a Column to the Power Query design to group on e.g.

if [Loc 3] = "Y" or [Loc 4] = "Y" then "Y" else "N"

Then I would Group By that column, and calculate the Max of Loc 3 and Loc 4.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.