Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel problem
I have list of events which are dated. More often than once, i have
more events on one day. I want to filter todays events from that list and show them on a different sheet. Can someone please help me..... Data looks like this: 09/11/07 IBM 0.47 10/11/07 INTC 0.50 10/11/07 DELL 0.75 11/11/07 GOOG 3.25 If today is 10/11/07, i want to show on a different sheet 10/11/07 INTC 0.50 10/11/07 DELL 0.75 ps: it can be 3 events, but it can also be 15. So you cannot really say how many events there are going to be. Thanking you all in advance for looking at this problem! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel problem
Look at Advanced Filter in help.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Maureeze" wrote in message ups.com... I have list of events which are dated. More often than once, i have more events on one day. I want to filter todays events from that list and show them on a different sheet. Can someone please help me..... Data looks like this: 09/11/07 IBM 0.47 10/11/07 INTC 0.50 10/11/07 DELL 0.75 11/11/07 GOOG 3.25 If today is 10/11/07, i want to show on a different sheet 10/11/07 INTC 0.50 10/11/07 DELL 0.75 ps: it can be 3 events, but it can also be 15. So you cannot really say how many events there are going to be. Thanking you all in advance for looking at this problem! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel problem
You need two sheet names old and new. Change these sheet names as required
Sub copy_today() ShNewRowCount = 1 ShOldRowCount = 1 With Sheets("Old") Do While .Cells(ShOldRowCount, "A") < "" If Date = .Cells(ShOldRowCount, "A") Then .Rows(ShOldRowCount).Copy Destination:= _ Sheets("New").Rows(ShNewRowCount) ShNewRowCount = ShNewRowCount + 1 End If ShOldRowCount = ShOldRowCount + 1 Loop End With End Sub "Maureeze" wrote: I have list of events which are dated. More often than once, i have more events on one day. I want to filter todays events from that list and show them on a different sheet. Can someone please help me..... Data looks like this: 09/11/07 IBM 0.47 10/11/07 INTC 0.50 10/11/07 DELL 0.75 11/11/07 GOOG 3.25 If today is 10/11/07, i want to show on a different sheet 10/11/07 INTC 0.50 10/11/07 DELL 0.75 ps: it can be 3 events, but it can also be 15. So you cannot really say how many events there are going to be. Thanking you all in advance for looking at this problem! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel problem
Hi Maureeze,
You didn't say whether you want to do this using VBA or manuallly but since you have posted to this group l assume you want to use VBA. Assuming the data is in culumns A,B,C the process would be, filter column A, copy the range, paste to another sheet, so the code would something like this (assuming no other data is on the sheet) Sheets("Sheet1").AutoFilter Field:=1, Criteria1:=myDate Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet2").Range("A1") where the variable myDate is the date you want to filter. This could be defined by using an input box. If the source sheet contains other data you can use the .Range("A1:C10".CurrentRegion.Copy instead of .Cells.Copy Hope this helps Regards Michael Beckinsale |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel problem
On Oct 26, 10:58 am, "michael.beckinsale"
wrote: Hi Maureeze, You didn't say whether you want to do this using VBA or manuallly but since you have posted to this group l assume you want to use VBA. Assuming the data is in culumns A,B,C the process would be, filter column A, copy the range, paste to another sheet, so the code would something like this (assuming no other data is on the sheet) Sheets("Sheet1").AutoFilter Field:=1, Criteria1:=myDate Sheets("Sheet1").Cells.Copy Destination:=Sheets("Sheet2").Range("A1") where the variable myDate is the date you want to filter. This could be defined by using an input box. If the source sheet contains other data you can use the .Range("A1:C10".CurrentRegion.Copy instead of .Cells.Copy Hope this helps Regards Michael Beckinsale Do you know if it is possible without VBA and without advance filtering? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel problem
Use MSQuery;
Name your data range, including field names Goto (insert if necessary) a new sheet (or you could do this from another WB) Select: Data, Import External Data, New Database Query Select 'Excel Files' from the database tab Select the current file name & folder from the next dialogue Open the range name (with the + symbol) from the next dialogue, &select the field names you require, click Next Enter any conditions, click Next. Enter Sort criteria, click Next. You can now test your output in MSQuery, or drop it straight back into your WS. Right-clicking the resulting data range will enable you to change the criteria, making the tool reusable. -- HTH Roger Shaftesbury (UK) "Maureeze" wrote in message ups.com... I have list of events which are dated. More often than once, i have more events on one day. I want to filter todays events from that list and show them on a different sheet. Can someone please help me..... Data looks like this: 09/11/07 IBM 0.47 10/11/07 INTC 0.50 10/11/07 DELL 0.75 11/11/07 GOOG 3.25 If today is 10/11/07, i want to show on a different sheet 10/11/07 INTC 0.50 10/11/07 DELL 0.75 ps: it can be 3 events, but it can also be 15. So you cannot really say how many events there are going to be. Thanking you all in advance for looking at this problem! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel problem
Hello Maureeze,
Suppose your data is in Sheet1, A1:C4. Then enter into Sheet2, A1:E4: Date 39397 Occurrence 0 1st =B2+MATCH(Sheet2!$B$1,OFFSET(Sheet1!$A$1:$A$1000,B 2,0),) =INDEX(Sheet1!$1:$65536,Sheet2!$B3,1) =INDEX(Sheet1!$1:$65536,Sheet2! $B3,2) =INDEX(Sheet1!$1:$65536,Sheet2!$B3,3) 2nd =B3+MATCH(Sheet2!$B$1,OFFSET(Sheet1!$A$1:$A$1000,B 3,0),) =INDEX(Sheet1!$1:$65536,Sheet2!$B4,1) =INDEX(Sheet1!$1:$65536,Sheet2! $B4,2) =INDEX(Sheet1!$1:$65536,Sheet2!$B4,3) [39397 is 11-Nov-2007] Copy B4:E4 down as far as necessary. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 | Excel Programming | |||
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 | Excel Programming | |||
Weird problem with Excel 2000...Worksheets disappearing in a shared Excel file | Excel Programming | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) |