Arrange dates, Asc order -/- double entry!!!!!

buddy1000

Active Member
Joined
Oct 31, 2007
Messages
294
In Column B I have dates and in column C I have values correponding to the dates in B.
In Column J I have dates and in column L I have values correponding to the dates in J.

The first date entry in column B and J are equal but after that it is different based on a workday formula adding months, years, days etc. etc.

B31 = 06-apr-09 and J31 = 06-apr-09
B32 = 06-apr-10 and J32 = 06-Oct-09
B33 = 06-apr-11 and J33 = 06-apr-10
B34 = 10-apr-12 and J34 = 06-Oct-10
B35 = 08-apr-13 and J35 = 06-apr-11
B36 = 07-apr-14 and J36 = 06-Oct-11
J37 = 10-apr-12
J38 = 08-Oct-12
J39 = 08-apr-13
J40 = 07-Oct-13
J41 = 07-apr-14

As may notice column B adds 1 year to the previous date while column J adds 6 months to the previous date. (This is changing and B can add 1 month while J adds 3 months etc. etc.

I need a formula in column N that will look at the dates in column B and in column J and list them in column N in ascending order while not taking into account double entries.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
You'd need to creat a list in one column of all the dates. Then Data> filter>advanced filter and filter this column and ask for unique records. Copy it to column N and then sort it ascending.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In N31 enter:

=B31

In N32 enter:

=MIN(INDEX(B$31:B$41,MATCH(N31,B$31:B$41)+1),INDEX(J$31:J$41,MATCH(N31,J$31:J$41)+1))

and copy down. The formula will return #REF! when there are no more dates to return.
 

buddy1000

Active Member
Joined
Oct 31, 2007
Messages
294
Thank you Andrew....Could you please be so kind as to explain what the formula does?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

The formula looks up the previous date in both ranges, offsets by one row and returns the smallest.
 

buddy1000

Active Member
Joined
Oct 31, 2007
Messages
294
Thank you....

Now i have the dates in column N.
column C and L has values corresponding to the dates in B and J.

Is it possible tolookup the date in column N in B and J and return the sum.

So N 31 is 6-apr-09. i want to look up N31 in column B and J and return the sum of C and L
I tried it but found out that it only works when the date in N is present in both B and J.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Try:

=SUM(IF(COUNTIF(B$31:B$41,N31),VLOOKUP(N31,B$31:C$41,2,FALSE)),IF(COUNTIF(J$31:J$41,N31),VLOOKUP(N31,J$31:K$41,2,FALSE)))
 

buddy1000

Active Member
Joined
Oct 31, 2007
Messages
294
PERFECT...

final issue....back to the "offset" formula.

=MIN(INDEX(B$31:B$41,MATCH(N31,B$31:B$41)+1),INDEX(J$31:J$41,MATCH(N31,J$31:J$41)+1)).

Is it possible to alter it in a way such that thhe references (B$31:B$41) and (J$31:J$41) automatically adjust to the last input in column B of J.

I have noticed that if I extend the dates in column B and/or J I have to alter the references.

I tried using (B$31:B$1000) and (J$31:J$1000)--> I found out that in that case the last date in column N is 0-jan-00.
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Choose Insert|Name|Define and add a couple of dynamic named ranges:

ColB =OFFSET(Sheet1!$B$31,0,0,MAX(COUNT(Sheet1!$B$31:$B$1000),COUNT(Sheet1!$J$31:$J$1000)),1)

ColJ =OFFSET(Sheet1!$J$31,0,0,MAX(COUNT(Sheet1!$B$31:$B$1000),COUNT(Sheet1!$J$31:$J$1000)),1)

Then you can use the formulas:

=MIN(INDEX(ColB,MATCH(N31,ColB)+1),INDEX(ColJ,MATCH(N31,ColJ)+1))

=SUM(IF(COUNTIF(ColB,N31),VLOOKUP(N31,OFFSET(ColB,0,0,,2),2,FALSE)),IF(COUNTIF(ColJ,N31),VLOOKUP(N31,OFFSET(ColJ,0,0,,2),2,FALSE)))
 

buddy1000

Active Member
Joined
Oct 31, 2007
Messages
294
When I go to Insert|Name|Define..should I put the formula in refers to:? and should I put anything in names in workbook?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,647
Messages
5,597,363
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top