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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0
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:
Upvote 0
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)))
 
Upvote 0
When I go to Insert|Name|Define..should I put the formula in refers to:? and should I put anything in names in workbook?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top