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

[buddy1000]

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.

 

[Andrew Poulsom]

Example:

Names in workbook: ColB
Refers to: =OFFSET(Sheet1!$B$31,0,0,MAX(COUNT(Sheet1!$B$31:$B$1000),COUNT(Sheet1!$J$31:$J$1000)),1)

 

[buddy1000]

Sorry....Found It...

Thank you very much my friend!!!!

 

[buddy1000]

Thank You Andrew...I have a question about the formula..

=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)))

It works but now I have a formula in column K.

=IF(J32=INDEX(J$31:J$511,MATCH(9.99999999999999E+307,J$31:J$511)),IF($B$17=2,$I$6*-1,$I$6),0).

When I use this everything is ok but when I change the false (0) into blank ("") then the formula gives a blank eventhough there are values in column C.

 

[Andrew Poulsom]

Sorry, I don't see any reference to column C in that formula.

 

[buddy1000]

=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)))

This formula you gave sums up values in column C and column K.

 

[Andrew Poulsom]

I thought you were talking about this formula:

=IF(J32=INDEX(J$31:J$511,MATCH(9.99999999999999E+307,J$31:J$511)),IF($B$17=2,$I$6*-1,$I$6),0)

 

[buddy1000]

Yes, you are right.... your sumIfCountIf formula is in column G and sums up values in C AND K.

First I had hard input in colum K and then your formula in column G worked fine.
But when I put my formula in column K your formula works well but when I change the false (0) into blank then your formula gives a blank eventhough column C has values.

Formula in column K-->
=IF(J32=INDEX(J$31:J$511,MATCH(9.99999999999999E+307,J$31:J$511)),IF($B$17=2,$I$6*-1,$I$6),0)

 

[Andrew Poulsom]

Why don't you leave the formula as it is? If you don't want to see the zeroes format the cells as eg 0;0;.

 

[buddy1000]

Perfect andrew
Thank you