I am using Excel 2003. I am creating a data-entry spreadsheet that contains supplier costs for components. For each component (Record), it has a begin date for when the price goes into effect and an end date. Since I want a history of this item and the dates that it increased or changed, there will be duplicate records in the spreadsheet. I need to make sure that there is only one of these records with an active effectivity date; meaning, if there is a record that is the same component with a more recent begin date, then the end date is the most recent begin date - 1 day. I'm sure this involves a loop, but I know that loops are expensive in terms of time it takes to run them. Please let me know if anyone needs additional information. The begin date is in column J and the end date is in column K. If the end date is empty, then it is considered "active".

Software/Hardware used: