Filter data based on the date provided by the user

[sindhuja]

Hi,

I have a excelsheet with more number of rows and column.

In column H and column I we have date fields. All i need to do is below.

Step 1 - filter blanks column i and by using the formula =h2 i will fill the blanks in column I
Step 2 - copy paste formulas as values
Step 3 - i want the user to provide the date and based on the date i have to delete all the dates greater than date provided by the user in column I.
Step 4 - now insert a column next to column I, name it as ageing.
Step 5 - now difference to be find between the date provided by the user and the dates in column I
Step 6 - in ageing column filter for values greater than 90 and copy paste the results in new sheet naming '>90'
step 7 - Put a piivot for the values in sheet '>90' and name it as Final

Can this be automated using vba.

please help me on this....

- Sindhuja

[Yongle]

Hi Sindhuja

I have looked at your previous threads and you never mark any of them as "Solved" - that is not very helpful and does not follow the forum's rules.
I am one of the "volunteers" mentioned in the extract from the site's FAQ below

How do I mark a thread as Solved, and why should I?

If your problem has been solved in your thread, mark the thread "Solved" by going to the "Thread Tools" dropdown at the top of the thread. You might also consider rating the thread by going to the "Rate Thread" dropdown which is next to the "Thread Tools" dropdown.

This lets future site visitors with the same problem know that the thread contains a solution. It also rewards the volunteer(s) who helped you solve your problem. Remember that the forum is filled with unpaid volunteers helping you with your problem -- marking your thread as solved and/or rating it is the payment for their help.


ScreenDump.jpg

[sindhuja]

Hi,

I have closed my previous thread. Can you help with this requirement pls...

[Yongle]

We need to understand exactly what you want - you are asking for quite a lot of things to be done here but I think it all sounds possible.
In order that someone can help you with this please answer the questions that follow and attach a workbook with 3 sheets which look like this:

- Sheet1 - sheet BEFORE the macro - including all the columns
- Sheet2 - sheet AFTER the macro - this must look EXACTLY like you want it to look after everything has happened (I would expect this to have one extra column in it compared to sheet1)
- Both sheet1 and sheet2 should have a minimum of 10 rows of sample data ONE before and ONE after the macro (using the SAME data) - it is important to have 10 rows of data so that we can understand the logic of the changes between sheet1 and sheet2
- Sheet3 - the pivot - see below

Also please answer the following questions:

Step 1 - filter blanks column i and by using the formula =h2 i will fill the blanks in column I

1) should every blank cell in column I have the formula =H2 OR if it is I3 should I3 = H3, and I4 = H4 ...etc?

Step 3 - i want the user to provide the date and based on the date i have to delete all the dates greater than date provided by the user in column I.

2) Do you want the user to provide the date via an input box?
3) Will the date provided by the user be the same for every row?
4) If the dates in column I > date provided by user are deleted, what goes into column I?- is it the date provided by the user or is Column I left blank?

Step 4 - now insert a column next to column I, name it as ageing.

5) Is the new column inserted before or after column I?

Step 5 - now difference to be find between the date provided by the user and the dates in column I

6) In step 3 you wanted to delete all the dates that were greater than the dates provided by the user, so do you want Step 5 only to calculate the difference between the date provided by the user and the dates less than that?

Step 6 - in ageing column filter for values greater than 90 and copy paste the results in new sheet naming '>90'

7) Is this everything in the new column which is > 90 days?
8) Which columns should be pasted

step 7 - Put a piivot for the values in sheet '>90' and name it as Final

9) What does this mean? - please include on sheet3 exactly how this should look using the SAME data as provided in sheet1 and sheet2


When you reply, please include EVERYTHING at the same time
- to make it easy for us, please answer the questions like this (using the same numbers as the questions)
1)
2)
3)
4)
5)
6)
7)
8)
9)

- attach a workbook containing the 3 sheets requested
- do not attach any pictures (jpegs etc)

thank you

[sindhuja]

Hi,

Apologies if am not clear with my requirement.

Please find the comments and also I have attached the sample sheet for the reference.

1) Column H will have dates and column I will also have dates. If there are blanks in column H then we have to use the formula (eg. I2=H2)
2) Yes, user has to provide the input via input box and the input should be date.
3) The difference in dates should be the date in the column I and the date provided by the user
4) Once we use the formula =h in all the blank values of column I, there will not be blank cells in column I. Now these values to be compared with value provided by the user. Ex if the user enters 4/30/2015 and value in column I is greater than 4/30/2015 then all the rows to be deleted.
5) Column ageing to be inserted after column I
6) yes, ageing to be find for the data after deleting the values which are greater than user provided date.
7) in Sheet >90, the ageing which are greater than 90 should be copied.
8) Source for the pivot is from >90 sheet and name it as Final

Hope I made it clear now

-Sindhuja

[Yongle]

Thanks.
If no one else has looked at this for you, I will do so tomorrow - I am away from my PC for a few days and cannot open your workbook on my phone!
Yon

[Yongle]

Please test using the attached workbook.
Put copy of your data into Sheet1 and run the macro.

How it works
UserDate variable captured from user input (input box)
Calculates last row number based on columnH - there must not be any blank cells in columnH
Blank cells in columnI are replaced with values from columnH
ColumnJ (=Ageing) is added, value = UserDate - columnI
Sheet named ">90" is created (sheet will be deleted if it already exists to avoid errors)
Using "data filter" ageing over 90 days is copied to new sheet
Using "data filter" rows with negative ageing are deleted

Sub Ageing()
'declare variables
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Integer
    Dim LastRow As Long
    Dim UserDate As Date
    Set ws1 = ActiveSheet
    UserDate = Application.InputBox("Please input ageing date", "User Date", FormatDateTime(Date, vbShortDate), Type:=1)
'determine number of last row of data
    With ws1
        LastRow = Range("H2").End(xlDown).Row
'replace blank cells in columnI
        For i = 2 To LastRow
            If .Cells(i, 9) = "" Then
                .Cells(i, 9).Value = .Cells(i, 8).Value
            Else
            End If
        Next i
'add column for ageing
        Range("J1").Value = "Ageing"
        For i = 2 To LastRow
            .Cells(i, 10).Value = UserDate - .Cells(i, 9)
        Next i
'add sheet ">90" - deletes previous sheet with name ">90"
        Application.DisplayAlerts = False
        On Error Resume Next
        Sheets(">90").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        Sheets.Add.Name = ">90"
        Set ws2 = Worksheets(">90")
'copy >90 days
        .Range("$A:$J").AutoFilter Field:=10, Criteria1:=">90"
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=ws2.Range("A1")
        ws2.Cells.EntireColumn.AutoFit
'delete rows with negative ageing values
        .Range("$A:$J").AutoFilter Field:=10, Criteria1:="<0"
        .Range("A2:J" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub