reporting list of dates

[peterpeter]

Hi all,
i have a worksheet with 'col A' a list of dates and 'row 14' a list of names.
under the names appears the text "late" to line up with a date.
I have a sumproduct section to total the no of times the text "late" appears
within a date range.
On another worksheet i want to list the names in one column and then the
dates where "late" appears in adjacent columns, but only if the sumproduct
total is >9

I'm a bit lost at this point any help appreciated.

 

[Max]

Here's one formulas set-up which delivers close fit results that might appeal
to you

Assume that in a source sheet named: x,
you have 3 names listed in B1414
with real dates listed in A15:A300
and with "late" labels appearing within B15300 for latecomers

In F15: =IF(B15="late",ROWS($1:1),"")
Copy across to H15, fill down to H300

Then in another sheet,
In A2:
=IF(COUNT(OFFSET(x!$F$15:$F$300,,ROWS($1:1)-1))<=9,"",INDEX(x!$B$14:$D$14,,ROWS($1:1)))
Copy A2 down to A4. Only names with more than 9 "late" labels in x will
appear (this is your 9 times "late" limit). Just adapt the "<=9" bit in the
formula to suit should you decide to change this control criteria.

Then, to extract the full list of all the "late" dates horizontally
for each name which appears within A2:A4,
with dates neatly bunched to the left

In B2:
=IF($A2="","",IF(COLUMNS($A:A)>COUNT(OFFSET(x!$F$15:$F$300,,ROWS($1:1)-1)),"",INDEX(x!$A$15:$A$300,SMALL(OFFSET(x!$F$15:$F$300,,ROWS($1:1)-1),COLUMNS($A:A)))))
Copy B2 across by as many cols as the max expected no. of "late" dates per
name (this maximum may actually be further associated with your internal
staff punctuality controls), say across by 15 cols to P2, fill down to P4.

Success? Savour it, click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik