Populating Cells based on next Row

[Steven547]

I have an excel spreadsheet. The first two lines are duplicates, except for a FROM DATE. So, 4 columns are the same, but the 5th column (from date column) is different. The 6th column is an empty field (Thru date).

It looks like this:

Col A Col B Col C Col D From Date Thru Date


What I would like to do is a systematic way of adding the "end of the year date" (ie 12/31/08 or 09 or 10, etc) when the FROM DATE of one Dupe line is LESS than the FROM date of the other Dupe line.

So one line will have a FROM DATE of 01/01/2009 and the other Dupe line will be 05/31/2006. I want to populate the THRU date on the earlier FROM DATE (05/31/2006) with a date of 12/31/2006 (because this is the day before the "greater" FROM date). That make sense?

I was trying this in MS Access and couldn't figure it out. Any help would be greatly appreciated!

 

[Gavona]

Do you have further data in rows 4&5 that need the same approach?
Are there always pairs of rows or sometimes are there 3 or more Dupe rows?
Can you sort the data so the earlier From Date appears first?

(Just looking to keep the formula as simple as possible)


Gavin

 

[Steven547]

There can be 3 or more dupe lines per "group". And after that "group" of dupes, there will be another set of lines needing the same thing done. This is a sample of how it looks: (and this is all made up data..just trying to show an example)

Column A Column B From Date Thru Date
5 6 05/08/2006
5 6 01/01/2009
100 120 04/01/2005
100 120 01/01/2008
100 120 01/01/2009

And how I would like it to look:

Column A Column B From Date Thru Date
5 6 05/08/2006 12/31/2008
5 6 01/01/2009
100 120 04/01/2005 12/31/2007
100 120 01/01/2008 12/31/2008
100 120 01/01/2009

What we need to do is "close" that line in the system and only use the "later" date. That is why we use the 12/31 date.

So the dupes could be 2 lines to 4 lines long.

 

[Gruuuu]

(in D2, copy formula to end of data)

=IF(AND(A2=A3,B2=B3),IF(C3="","",C3-1),"")

 

[Gavona]

Alternatively (more useful the more columns to compare)combine the values an A&B and then compare the combined values - like this:

=IF(A2&B2=A3&B3,IF(C3="","",C3-1),"")

(Your examples don't show the possibility of a blsnk FromDate so the formula might be further simplifiable:

=IF(A2&B2=A3&B3,C3-1),""

Gavin