Date matching program

**drum9uy** · 08-07-2007, 11:50 AM

I'm trying to make a date matching program to form a list of only the matching dates from two columns of dates. So far I've only been able to do it with functions, however, the function becomes huge and wastes space/time. Any ideas for another way?

I.E. In Workbook "Business Days" Column A is business days in the US, Column B is business days in Japan. I have another Workbook to do the analysis of values "Common Business Days" where I would like to be a list of the common business days between the two.

The current model is running an excessive amount of vlookups to find corresponding days, but if there isn't a matching day in one of the countries it has to check the next set, and so on. If one cell ends up using 4 days ahead, the cell below it now needs to be checking even farther ahead, so as missing days add up over the years the formula will eventually not be able to look far enough ahead. What I need is something that works in a more general manner rather than checking specific cells so that the missing days won't compound and make the formula blow up.

All help is greatly appreciated!

**FrankBoston** · 08-07-2007, 03:58 PM

Maybe this will work for you.
Rearrange the data into a single column of dates B and mark the country
that the date belongs to in the next column C. Sort into order by Date.
Add the fill-down formulas to compare the dates, and use a Filter to show/select
the US date from each matching pair.
The attached workbook implements this.

This can be the basis for automating the procedure, if need be.

 A    B      C         D        E      
      Date   Country   Compare  Choose                           
 3    1      US        0        0       - Sort rows of dates into order by date                    
 4    2      Japan     1        0       - Fill down the formulas                    
 5    2      US        1        1       - Use Filter to select rows of interest                    
 6    3      US        0        0         Select B2:E16 and do Data: Filter: AutoFilter                    
 7    4      Japan     1        0         Click the Choose drop-down and select 1                    
 8    4      US        1        1       - Select and Copy the rows to another sheet                    
 9    5      US        0        0       - This works only if there are no duplicate dates                     
10    6      Japan     1        0         within the US data and Japan data considered separately                    
11    6      US        1        1                           
12    7      US        0        0                           
13    8      Japan     1        0                           
14    8      US        1        1                           
15    9      Japan     1        0                           
16    9      US        1        1                           
17                                                                
                       D3  =--OR(B3=B4,B3=B2)                                        
                                E3  =--AND(C3="US",D3=1)                              
          Compare: 1 if date has a partner                                                            
          Choose : Select the US date for filtering

FrankBoston is the pen name for Andrew Garland, Lexington MA