Extract list of values from a list

[TUNGANA KURMA RAJU]

I need a function/formula to extract a list of values from a list basing on
two conditions.
I have date wise sales data > sales person,date,amount in sheet 1.
In sheet2, if I enter Sheet2!a1=name of salesman and in cell
B2=month-year(I have drop down list viz.salesmem list,month-year list.)
I need date wise sales details of that salesman , of the specified
month, to the w/sheet range A2:B32.

 

[TUNGANA KURMA RAJU]

my w/sheet1 data looks like this.
Col A--------------------ColB--------------------ColC
SalesMan---------------Date--------------------Amount
Raj -------------------01/Mar/09---------------500.00
Jones-----------------01/Mar/09---------------235.oo
Mary------------------02/Mar/09---------------190.00
Mary------------------03/Mar/09---------------100.00
Raj--------------------03/Mar/09---------------290.00
Cathy-----------------04/Mar/09---------------400.00
Raj--------------------30/Mar/09---------------500.00
Jones------------------30/Mar/09---------------450.00
Raj---------------------01/Apr/09---------------100.00
Mary-------------------02/Apr/09---------------450.00
the list goes on.........
output on w/sheet2 be looks like this:
Col A--------------------------Col B(drop down lists in cell a1 and B1)
Mary--------------------------Mar-09
Date--------------------------Amount
02/Mar-09--------------------190.00
03/Mar/09--------------------100.00

 

[KC Rippstein hotmail com>]

You should do one of two things. Either:
- apply an auto-filter or advanced filter to sheet 1 and eliminate sheet 2
altogether, or
- use a pivot table on sheet 2

you'll have to add another column to sheet 1 which gives the month &

year from your date column (so make column D labeled "mo-yr" and have this
formula in that column =b2 then format it as Mmm-yy)

set up the pivot table with the sales person's name and the mo-yr in

the Page area and the transaction date in the row area

then you can filter the pivot table by person and month/year using the

Page fields

 

[TUNGANA KURMA RAJU]

I don't want use both options.I need a formula,or vba procedure to achieve
the results.
somewhere in 2007 the same type of question was answered by T.Valko,I am not
finding that link file.

 

[T. Valko]

Is it possible that a salesperson might have duplicate date entries:

Mary...3/1/2009...10
Mary...3/1/2009...17

 

[TUNGANA KURMA RAJU]

No duplicate dates.A sales person is having one entry per one date.

 

[T. Valko]

With your data on Sheet1 in the range A2:C11...

Using named ranges:

Name refers to =Sheet1!$A$2:$A$11
Date refers to =Sheet1!$B$2:$B$11
Amt refers to =Sheet1!$C$2:$C$11

Sheet2:

A1 = drop down of names
B1 = drop down of true Excel dates
A2 = header = Date
B2 = header = Amount

Add a new header to a cell nearby, maybe C1.

C1 = header = Records

We'll use this to get the total count of records that meet the criteria.
This will help to shorten the formula needed to extract the data and it will
also act as an error trap test cell.

Enter this formula in C2 (count of records):

=SUMPRODUCT(--(Name=A1),--(MONTH(Date)=MONTH(B1)),--(YEAR(Date)=YEAR(B1)))

Enter this array formula** in A3 (extracts the dates):

=IF(ROWS(A$3:A3)<=C$2,SMALL(IF((Name=A$1)*(MONTH(Date)=MONTH(B$1))*(YEAR(Date)=YEAR(B$1)),Date),ROWS(A$3:A3)),"")

** 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 B3 (extracts the amounts):

=IF(A3="","",SUMPRODUCT(--(Name=A$1),--(Date=A3),Amt))

Select both A3 and B3 then copy down to a number of rows that is certain to
be enough to return all the corresponding data.

Format A3:An as Date