• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find closest date in the past (the date is concatenated)

J

Josedv

New Member
Hello there, I am looking for some help. I have a data base on sheet1 made of 3 columns and 9999 rows. It looks as follows:


A. B. C.

1. John Lewis. 13/10/12. Madrid.

2. John Lewis. 13/10/12. Paris.

3. John Lewis. 13/10/12. New York.

4. Mike Smith. 13/10/12. Madrid.

5. Mike Smith. 13/10/12. Paris.

6. Rob Harris 21/09/12. Madrid.

7. Rob Harris. 21/09/12. Paris.

8. John Lewis. 21/09/12. Madrid.

9. John Lewis. 21/09/12. Paris.

...

9999. .... ..... .... .....


On sheet2 I have another column that concatenates the three columns of my data base. So A1 on sheet2 would look like this:


A.

1. John Lewis13/10/12Madrid.

...

9999


I need a formula on row 2 that finds the closest past date in which John Lewis was in Madrid and concatenate the result as shown in cell A1. In my example, the formula should bring up the result "John Lewis21/09/12Madrid". I have to find all the previous dates where John Lewis was in Madrid, which means that I will have to copy the formula downwards.


Any help on what combination of formulas to use would be much appreciated.


Best,

Josedv
 
Hi Josedv


I have slightly changed the data to include all the possibilities.


John Lewis 13-Oct-12 Madrid 23-Sep-12

John Lewis 13-Oct-12 Paris 13-Oct-12

John Lewis 13-Oct-12 New York 13-Oct-12

Mike Smith 13-Oct-12 Madrid 21-Sep-12

Mike Smith 13-Oct-12 Paris 13-Oct-12

Mike Smith 21-Sep-12 Madrid 21-Sep-12

John Lewis 20-Sep-12 Madrid 20-Sep-12

John Lewis 21-Sep-12 Madrid 20-Sep-12

John Lewis 23-Sep-12 Madrid 21-Sep-12


In the last column I have used the array formula to calculate the closest date and if the person has travelled only once on that route the formula will give the date for that particular row only. The formula is as follows:


=B1-MIN(IF((B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1)>0,(B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1)))


You can concatenate the results as you want.


Hope this helps.


Cheers

Amritansh
 
Hi Amritansh,


Thanks. I have tried your formula and it doesn't work with the cells formatted as "date". If I replace the dates with numbers instead it works fine. Any ideas? The date format I am using is dd/mm/yyyy


Pd. Kaushik- Thanks for your suggestion. It works fine but I prefer not having to build the lists for the data validation.


Josedv
 
Hi Josdev


I tried with the date format "dd/mm/yyyy" and faced the same issue. This may be due to the reason that MS Excel is not supporting this particular date format but I am not sure. Experts may help on this.


But as a workaround you can enter the dates as mm/dd/yyyy and later change the format to dd/mm/yyyy by going to Format Cell dialog box and slecting the first option under English(UK).


But if you already have data this may not be feasible. :-(


Amritansh
 
Hi Josedv,


Is there any specific reason that stops you from using data validation list? Because, we can also make the data validation list dynamic so that you do not need to change the list range every time whenever the data changes.


Regards,

Kaushik
 
Amritansh,


I can certainly change the date format to the american one. One more question, on my example above the dates are sorted from the newest to the oldest one. How can I change the formula if they are sorted from oldest to newest?? Also I need to program the formula for a range of 9999 rows but not all the rows are filled with values. Does it matter if the rows below the last input are empty???


Kaushik- i could look into making the validation list dynamic is that makes the process easier.


Thanks both of you for your help.


Josedv
 
Kaushik,


Your the result of your formula is the newest date from all the group of dates. What I need is the next future date?


Thanks again,

Josedv
 
Hi Josedv


Enter this formula in Sheet 2 & drag down, On your sheet2, i have assumed that B1 Contains the person's name and B2 contains the city name:


=IFERROR($B$1&" "&TEXT(SMALL(IFERROR((FIND($B$1,Data!$A$2:$A$10)>1)*(FIND($B$2,Data!$A$2:$A$10)>1)*DATE(2000+TRIM(MID(Data!$A$2:$A$10,FIND("*",SUBSTITUTE(Data!$A$2:$A$10,"/","*",2))+1,2)),TRIM(MID(Data!$A$2:$A$10,FIND("/",Data!$A$2:$A$10)+1,2)),TRIM(MID(Data!$A$2:$A$10,FIND("/",Data!$A$2:$A$10)-2,2))),FALSE),ROW(A1)),"DD-MMM-YY")&" "&$B$2,"--")


or see this file:


http://dl.dropbox.com/u/60644346/Extract%20date%20from%20String%20%26%20find%20match.xlsx


Press Ctrl+Shift+Enter & Drag Down...


Hopefully it will help :)


Regards,
 
Hi Josdev


As you can see, in the data used by me I have used no particular order for dates. So it will work with or without any order.


Regarding your second point that some rows may not have data, could you please clarify how do you want to treat those rows.


Cheers

Amritansh
 
Amritansh,


How would you adjust the formula is the dates are sorted from oldest to newest?


Regarding the empty cells, I don't want to do anything specific with these cells, however I will keep adding information to the data base over the time.


Thanks again,

Josedv
 
Hi Josdev


Please try this.


=IF((B1-MIN(IF((B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1)>0,(B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1))))=0,B1,(B1-MIN(IF((B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1)>0,(B1-($B$1:$B$9))*($A$1:$A$9=A1)*($C$1:$C$9=C1)))))


Amritansh
 
You must log in or register to reply here.
Back
Top