If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
extract data from range,and place in the same sheet
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 |
#3
|
|||
|
|||
extract data from range,and place in the same sheet
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 |
Thread Tools | |
Display Modes | |
|
|