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

Match dates without helper column?

T

TimeWarp

New Member
Hi All,


I have a sheet data range of F16:AA28. The range of cells has dates, non-contiguous. The date format used is, "d/m/yyyy".

At cell AB15, I have a validation drop-down list to select dates of various months throughout the year. I would like to know of a formula I can use on each row of the date range, to display when one or more months dates in the date range, "F16:AA28" match the month of the selected date from the validation drop-down list.

The actual day of the month is not important, only the month and year.

Thus far, I have tried a few "LOOKUP" formulas applying the & symbol to combine two dates when more than one are located, as there are never more than two dates of the same month per row. Although the matching months/year have unique days of the month, I have been unsuccessful using one formula per row, that is unaided by a helper column, which is my goal. Does anyone know of a single formula that could work, that would not require a helper column to capture the second date found per row?


Thanks in advance for any assistance!

TimeWarp,
 
Timewarp

Following on from your previous post it is the same answer just switched sideways

ie:

AB16: =SUMPRODUCT(($F16:$AA16>EOMONTH($AB$15,-1))*($F16:$AA16<=EOMONTH($AB$15,0)))>0

or

=IF(SUMPRODUCT(($F16:$AA16>EOMONTH($AB$15,-1))*($F16:$AA16<=EOMONTH($AB$15,0))),"Match","No Match")


Copy down

And you know how to modify for EOMONTH
 
In AB16

=SUMPRODUCT(($F16:$AA16>EOMONTH($AB$15,-1))*($F16:$AA16<=EOMONTH($AB$15,0)))>0

and Copy Down

This is the same formula I used for your last post just rearranged for the row


To display a message

=IF(SUMPRODUCT(($F16:$AA16>EOMONTH($AB$15,-1))*($F16:$AA16<=EOMONTH($AB$15,0)))>0,"Match","No Match")


To return the cell which matches

Try this as an array formula, enter with Ctrl Shift Enter

=ADDRESS(INT(MIN(IF(D=$AB$15,ROW(D)*1000+COLUMN(D)))/1000), MOD(MIN(IF(d=$AB$15,ROW(D)*1000+COLUMN(D))),1000))


Where D is a Named Range: F16:AA28
 
Hi Hui,


I appreciate what you have done with all three formulas.

The first and second formulas both confirm if a first and/or a second date equal the same month selected from the validation drop-down list. Although, I did a poor job of explaining what my ultimate goal is.


What is needed is for the formula to "display" the matching dates that are found.

In other words, display one date if only one matches the same month from the validation drop-down list, or both dates together, if two dates match the month from the validation drop-down list.


I've spent a lot of time trying different modifications of the first two formulas, and the closest I have come is to display the numeric values of two dates together in a cell using the "&" (ampersand). The bigger problem has been trying to coerce Excel to display the two date values together in the date format "d/m/yyyy".

For example;


4/15/2011 ________________<-- Only one match found.

_________________________<-- No match found.

4/4/2011 - 4/19/2011 _______<-- Two matches found.

_________________________<-- No match found.

4/26/2011 ________________<-- Only one match found.

4/22/2011 ________________<-- Only one match found.


In stead of the following date results;


40648


40637 - 40652


40659

40655


I named my date range to accommodate the third formula that you offered, and have gotten a "#VALUE!" error, which appears to be a result of zero array values for all of the dates in the range, even where it is already known to be a match available in the ROW tested.


With that said, I suspect that the third formula too will have trouble with the dates format issue.


With your obvious experience and expertise, can please tell me what I am doing wrong?

This may be more challenging than I expected!


Thanks again for your assistance!

TimeWarp,
 
Yes, as you stated for the third formula.

I also went into formula auditing mode, to confirm the finds of the array, which evaluated to zeros.


Any ideas of the cause?
 
Theres way too many assumptions being made here!

Can you post your file?
 
Hi Hui,


Actually I want to thank you very much for your "IF(SUMPRODUCT()" statement.

Combining that with a "TEXT(the_formula, m/d/yyyy)" function, did the trick for me.


I'm not sure if someone else would find my solution useful, but for those like myself whom have quickly come to recognize how powerful the SUMPRODUCT function is, especially when used in an array, may appreciate that I shared it anyway!


It is rather lengthy and basically achieves three functions.


First the array searches for a date in a ROW that matches the month selected in the validation list, if found, it uses the TEXT function to convert it from Excel's serial value, back to the a standard date format for display in the formula cell in text format.


Next, using the "&" (ampersand) another search is done to count if a second date is a match, if found then the column number of it's location is fed two the final portion of the formula.

Again using the TEXT function to convert it's date from Excel's serial value, back to the a standard date format for display in the formula cell.


As you'll notice, the "&" (ampersand) is used again as " - "&TEXT(date_value, m/d/yyyy), to display both dates as the results in the same cell.

Had only one date been found, it would be displayed alone as I explained in my post.

Of course if no match is found, the cell is left empty.


With that said, here is the array formula, which is simply copied down the column to the right of each ROW in the range to be searched.

Note that this is all entered in the formula bar as one line of commands for each cell, and would have required three columns or more to achieve the same end result;


=IF(ISERROR(INDEX($F16:$AA16,MATCH(MONTH($AB$14),MONTH($F16:$AA16),0))),"",TEXT((INDEX($F16:$AA16,MATCH(MONTH($AB$14),MONTH($F16:$AA16),0))),"m/d/yyyy"))&(IF((IF($AE16="","",(MATCH(DATE(YEAR(AB16),MONTH(AB16),DAY(AB16)+(DATE(YEAR(AB16),MONTH(AB16)+1,0)-DATE(YEAR(AB16),MONTH(AB16),DAY(AB16)+0))),$B16:$AA16,1)-1)))="","",IF(OFFSET(B$15,MATCH(B16,B$16:B$28,0),(IF($AE16="","",(MATCH(DATE(YEAR(AB16),MONTH(AB16),DAY(AB16)+(DATE(YEAR(AB16),MONTH(AB16)+1,0)-DATE(YEAR(AB16),MONTH(AB16),DAY(AB16)+0))),$B16:$AA16,1)-1))))=AB16,""," - "&TEXT((OFFSET(B$15,MATCH(B16,B$16:B$28,0),(IF($AE16="","",(MATCH(DATE(YEAR(AB16),MONTH(AB16),DAY(AB16))+(DATE(YEAR(AB16),MONTH(AB16)+1,0)-DATE(YEAR(AB16),MONTH(AB16),DAY(AB16)+0)),$B16:$AA16,1)-1))))),"m/d/yyyy"))))


I hope this approach can be useful to someone-else with a similar need!

By the way Hui, I think I read that we are not allowed to post pictures or files, is that right?

Thanks again for your help,

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