filtering weekly data (removing certain weeks)

[jimjohn]

hi, can someone please help me with this: i have weekly rate information in Excel. but i want to remove all other weeks and only include the first week of every month.

so my data looks like this:

Date Product CustRate
Nov 5, 2005 Mtg 4.5
Nov 5, 2005 Term 3.3
Nov 5, 2005 CD 2.3
Nov 12, 2005 Mtg 4.8
Nov 12, 2005 Term 6.6
Nov 12, 2005 CD 1.8
...

its not necessary that the first week always lands of the 5th day of the month sometimes its March 3rd, or so on.

any ideas how i can get rid of other data? Thanks a lot!

 

[venkat1926]

I am giving you a hybrid solution

see the sheet below

Excel Workbook
	A	B	C	D	E	F
1	Date	Product	CustRate	weekday offirst of month	date in whichthe first week starts	last date offirst week
2	5-Nov-05	Mtg	4.5	3	4-Nov-05	11-Nov-05
3	5-Nov-05	Term	3.3
4	5-Nov-05	CD	2.3
5	12-Nov-05	Mtg	4.8
6	12-Nov-05	Term	6.6
7	12-Nov-05	CD	1.8
Sheet1

see the formulas in D2,E2,F2
once these formulas are done copy the sheet1 with formulas to sheet2
I have given step by step so that the formula can be understood easily.
If there are different months in column A you have to make to formulas you have to create the formulas for each month and run the macro modified
I hope you can do that.

now run this macro

Sub test()
Dim j As Integer, k As Integer
Worksheets("sheet1").Activate
j = Range("a1").End(xlDown).Row
For k = j To 2 Step -1
If Cells(k, 1) > CDate(Range("F2")) Or Cells(k, 1) < CDate(Range("E2")) Then Cells(k, 1).EntireRow.Delete
Next k
End Sub

the second macro is to undo(that is why copying sheet1 to sheet2 after formulas are activated.

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
End Sub

BEFORE RUNNING THE MACRO TO ORIGINAL FILE KEEP A COPY SOMEWHERE SAFE SO THAT THE DATA CAN BE RETRIEVED IF NECESSARY

 

[jimjohn]

thanks a lot venkat!