Match date to first occurence of data

[3G]

Hello-

I have a list of project Numbers, dates on one page (start & End), and, a spreadsheet of months horizontal with hours broken out in the appropriate months. What Im trying to do is match the Start & End dates with the first & last occurence of the data on the 2nd page to make sure they are both the same. There are multiple entries on page 2 for the project number on page 1.


Any ideas? I originally tried an OFFSET, but, that just brings back the data...I'd like to just get more of a "yes" or "no" if the dates & data line up.


Thoughts?


Thanks!

3G

 

[Luke M]

I'm not quite able to visualize the layout of sheet 2...could you try and show us an example, or upload to google docs?

 

[jeremymjp]

Set up an array formula (Hold ctrl+shift when you press enter):


Where "ProjectNumbers" are the list of multiple projects in the second sheet.

And $A1 is the first project number that you want to check if matching.

And "Dates List" is the horizontal list of dates.

And $B1 is your first project's start date.

And "Hours" is the entire set of hours, not including project numbers or dates.


This would check for start dates:


=IF(SUM((ProjectNumbers=$A1)*(DatesList<$B1)*(Hours))>0,"Mismatch","OK")


And this for end dates:


With $C1 containing your first project's desired end date.


=IF(SUM((ProjectNumbers=$A1)*(DatesList>$C1)*(Hours))>0,"Mismatch","OK")


In words, it's adding up any number of hours that match the project but are before/after the desired project window. If that number of hours is greater than zero, it alerts. What it doesn't check for is if the hours are not correctly distributed but still BETWEEN (inclusively) the start and end dates.


Hope it helps. Good luck.

 

[3G]

Thanks Jeremy! Will give this a shot...


Luke:

Sheet 1

Project#|Start Date|End Date

1|1/1/2011|12/31/2013

Sheeet 2


Project|Jan-11|Feb-11|Mar-11|...|Dec-13

1|15|15|15|...|15|

1|0|10|10|...|0|

1|24|0|0|...|0|


So, I want to validate that the hours in the columns of Jan-11 thru Dec-13 fall within the start/end dates of the project dates on sheet 1. Again...is the first occurence of data in the month column the same as the start date on sheet 1..same with end date...(assumed everything is populated in between)


3G

 

[Luke M]

To find earliest start date:

=INDEX(Sheet2!1:1,MIN(IF((Sheet2!$A$2:$A$4=$A2)*(Sheet2!$B$2:$AK$4>0),COLUMN(Sheet2!$B$2:$AK$4))))


End date:

=INDEX(Sheet2!1:1,MAX(IF((Sheet2!$A$2:$A$4=$A2)*(Sheet2!$B$2:$AK$4>0),COLUMN(Sheet2!$B$2:$AK$4))))


Both of these are array formulas. Works very similar to Jeremy's formulas; these will pull back the actual date. If you're just validating, I'd go w/ Jeremy's idea.

 

[3G]

Hey Jeremy-

Here's what I've got...But, I'm getting an N/A (yes I am Ctrl+Shift+ent)


=IF(SUM((LPNum=RProjNum)*(PMonths<PStart)*(LOE_Hours_All))>0,"Mismatch","Ok")


My Named ranges are as follows:


LP Num -project # in sheet 2 (multiple lines per project)

RProjNum - (A3) Sheet 1 (1 line per project number)

PMonths - $R$2:$BM$2 (48 colums 1/1/2011-12/31/2014)

PStart - $G3 - Sheet 1 Project Start Date

LOE_Hours_All - $R$4:$BM$635 - only hours data


Any additional thoughts?


Thanks!

3G


Luke-

Thanks bud!

G

 

[jeremymjp]

Hmmm...


Is "LP Num" pointing to an array that begins on line 4 and ends on line 635 of sheet two?


Like $Q$4:$Q$635 ?

 

[3G]

Yes.


And my bad...PMonths is $R$3:$BM$3

 

[jeremymjp]

Are your project numbers in the first list the same format (number vs text) as the project numbers in the second list? Are any of your dates text versus serial code number format?


And, when you view the formula that you've entered into the cell, does it have the bracketing "{}" that shows that it recognizes an array formula?


We'll figure this out. The formula is okay, it's gotta be references, formats or something screwy.

 

[3G]

Yep...all there. I'll keep checking through it. I think I had some problems with the named ranges. A lot of them were dynamic, and, I think that was throwing it off. I've since made them non-dynamic, and, will play around today...Will keep you posted!!


3G