extract data from range,and place in the same sheet

Col A-------------------Col B------------------ColC---------------------------
Col D-----------------------ColE
Name-------------------Date-------------------Amount----------------------"
Mary"-------------------- Apr-09
John------------------01-Apr-09--------------$100 ------------------------
01-Apr-09---------------$125
Mary------------------01-Apr-09--------------$125-----------------------------
02-Apr-09---------------$567
Raj--------------------02-Apr-09---------------$50----------------------------
--04-Apr-09---------------$321
Mary------------------02-Apr-09---------------$567
John-------------------03-Apr-09---------------$213
Bill--------------------03-Apr-09----------------$456
Mary-----------------04-Apr-09-------------------$321
Cathy----------------06-Apr-09---------------$310
Mary------------------01-May-09--------------$78
Raj---------------------02-May-09--------------$567

D1 has dropdown list of Names,and E1 has drop down list of Mon-YY.
I need a macro code to get Mary's Apr-09 data from the used range A1:C to
D2:E32.
If I select Mary(cell D1) and Apr-099Cell E1) ,the output should display like
above.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200904/1

Sub GetMatches()

'fill in column A with names
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For RowCount = 3 To LastRow
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = Range("A" & (RowCount - 1))
End If
Next RowCount

Range("D2").Formula = _
"=if(And($A2=$D$1," & _
"month($B2)=month($E$1)," & _
"year($B2)=year($E$1)),A2,"""")"
Range("D2").Copy _
Destination:=Range("D2:E" & LastRow)

Columns("E").NumberFormat = "D-MMM-YY"

End Sub




"tkraju via OfficeKB.com" wrote:

Col A-------------------Col B------------------ColC---------------------------
Col D-----------------------ColE
Name-------------------Date-------------------Amount----------------------"
Mary"-------------------- Apr-09
John------------------01-Apr-09--------------$100 ------------------------
01-Apr-09---------------$125
Mary------------------01-Apr-09--------------$125-----------------------------
02-Apr-09---------------$567
Raj--------------------02-Apr-09---------------$50----------------------------
--04-Apr-09---------------$321
Mary------------------02-Apr-09---------------$567
John-------------------03-Apr-09---------------$213
Bill--------------------03-Apr-09----------------$456
Mary-----------------04-Apr-09-------------------$321
Cathy----------------06-Apr-09---------------$310
Mary------------------01-May-09--------------$78
Raj---------------------02-May-09--------------$567

D1 has dropdown list of Names,and E1 has drop down list of Mon-YY.
I need a macro code to get Mary's Apr-09 data from the used range A1:C to
D2:E32.
If I select Mary(cell D1) and Apr-099Cell E1) ,the output should display like
above.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200904/1

Thank you Joel,but small tweak.
Out put not coming from D2:En, out put coming to the corresponding row of the
Name.Say my Name's 1st occurance is in ColA is A5,output is coming in the
corresponding row D5&E5 ,my 2nd occurance is A8,out put coming in D8 &E8.I
need output starts from D2,E2 next D3,E3,next D4,E4.like that.Can you please
modify a little bit.Thanks.



joel wrote:
Sub GetMatches()

'fill in column A with names
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For RowCount = 3 To LastRow
If Range("A" & RowCount) = "" Then
Range("A" & RowCount) = Range("A" & (RowCount - 1))
End If
Next RowCount

Range("D2").Formula = _
"=if(And($A2=$D$1," & _
"month($B2)=month($E$1)," & _
"year($B2)=year($E$1)),A2,"""")"
Range("D2").Copy _
Destination:=Range("D2:E" & LastRow)

Columns("E").NumberFormat = "D-MMM-YY"

End Sub

Col A-------------------Col B------------------ColC---------------------------
Col D-----------------------ColE
[quoted text clipped - 19 lines]
If I select Mary(cell D1) and Apr-099Cell E1) ,the output should display like
above.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200904/1