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

#### buddy1000

##### Active Member
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### BGY23

##### Well-known Member
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
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
Thank you Andrew....Could you please be so kind as to explain what the formula does?

#### Andrew Poulsom

##### MrExcel MVP

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

#### buddy1000

##### Active Member
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

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
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
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
When I go to Insert|Name|Define..should I put the formula in refers to:? and should I put anything in names in workbook?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,650
Messages
5,854,943
Members
431,687
Latest member
anthonyj

### 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.

### Which adblocker are you using?

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

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