Interesting problem in excel, producing a list for aspecific date

[mark]

Hi probably a silly one but i want to make a list of people to be seen on a
specific date, my data is in a table where column one is a date list and
subsequent columns are headed with clients names. Data is recorded against a
date as the number of reviewsthatclient has had so the sheet look something
like this

AB CD EF GH IJ
01/01 1 3 1
01/02 2 4
01/03 4 2
01/04 2 3
01/05 5



It would be great ro be able to type in a date and have a listof those to be
seen and whatnumber review it is produced to print off: to look alittle like
this:

Date Client Review No
01/04 AB 2
01/04 CD 3


Cananyone help?

 

[Susan]

since no one else has answered...............
could you use auto filter on the dates? it wouldn't produce results
in the same format that you mentioned in your 2nd example, it would be
the same format as the original list.
just an idea.

susan

 

[T. Valko]

Try this...

Data in the range A1:F6

Names used in the formulas:

Dates: refers to $A$1:$A$6
Client: refers to $B$1:$F$1
Review: refers to $B$2:$F$6

A10 = some date

Enter this formula in B10. This will return the count of records for the
date entered in A10:

=COUNT(INDEX(Review,MATCH(A10,Dates,0),0))

Enter this array formula** in C10. This will return the client names:

=IF(ROWS(C$10:C10)>B$10,"",INDEX(Client,SMALL(IF(INDEX(Review,MATCH(A$10,Dates,0),0)<>"",COLUMN(Client)),ROWS(C$10:C10))-MIN(COLUMN(Client))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Enter this formula in D10. This will return the review numbers:

=IF(C10="","",INDEX(Review,MATCH(A$10,Dates,0),MATCH(C10,Client,0)))

Select both C10 and D10 and copy down until you get blanks. You'll have to
copy down to a number of rows that is at least equal to max number of
reviews for any date.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Interestinglynoformulasrequired!
http://www.mediafire.com/file/xmyztm2mr0y/08_17_09.xlsx