Filter the data in Excel through formula Solved/Closed

Question

Hello,

I need a formula of the following quotation:

Column A Belongs To Customer Name

Column B Belongs The Date

Column C Belongs To Item

Column D Belongs To Amount

There Are Too Many Data Already Posted In These Columns..

& I Need To Filter These Data In Other Sheet With Using Formula, Not Using The By-Default Filter Option.

For Example:
Suppose This Is A Data
Sheet 1:

Customer Name           Date                    Item                      Amount
Paul                        01-Jan-10             HandSet                      18000
Shaun                     15-Jan-10             Charger                         200
Rahul                      30-Dec-09            Battery                          600
Naeem                    11-Jan-10             Handset                      16000
Paul                        14-Nov-09            Battery                          250
Naeem                    19-Jan-10            Battery                          150
Paul                        25-Dec-09           Handset                         9000
Shaun                     15-Jan-09            Charger                          100
Rahul                      18-May-09           Battery                          250

Sheet 2:

If I Need To Filter My Data As Customer Name.. In A2 I Type The Customer Name Than In Sheet 3 Or In Same Sheet My Data Will Filtered As Customer Name, If I Need To Filter My Data As DateWise.. In B2 I Type The Date Than In Sheet 3 Or In Same Sheet My Data Will Filtered As DateWise, If I Need To Filter My Data As Itemwise, In C2 I Type Item Name Than In Sheet 3 Or In Same Sheet My Data Will Filtered As Itemwise..

But I Need To Filter My Data Through Formula, I Think This Solution Will Be Solved As Macro.....

Like I Need My Solution As Following:

Sheet2:
A2 Is Input Name Of Customer Name
B2 Is Input Value Of Date
C2 Is Input Name Of Item
D2 Is Input Value Of Amount Or Cost

                    A                       B              C                D
1          Customer Name     Date          Item           Amount
2           If I Type Here Paul

Than It Go To Sheet 1 & Find The Paul's Entry & Display All Pauls Entry In Sheet 3


Sheet 3:
I Need This Data Through Formula
Customer Name           Date           Item             Amount
Paul                         01-Jan-10    HandSet           18000
Paul                        14-Nov-09     Battery                250
Paul                         25-Dec-09    Handset             9000

I Think Now You Can Understand What I Want.........

Looking Forward To Your Usual Co-operation

Thanks & Regards,
NaeemSystem Configuration: Windows XP
Firefox 3.5.7

Trowa · Accepted Answer

Hi Naeem,

I put together a code for you which works when you input a customer name in "A2" of sheet2.
Unfortunately I am out of time to figure it out for the other 3 criteria, but hopefully you can figure the rest out by yourself.
I also hope that I understood you correctly, because I don't see a benefit of sorting a list like this.

So here is the code:
Sub Test()
    Sheets("Sheet3").Rows("2:10").ClearContents
    Sheets("sheet1").Select
    Range("A2").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("A2")
    Rows("2:10").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Sheets("Sheet3").Select
    Range("A2").Select
End Sub

Since you gave 9 lines of example data, I used Rows("2:10") on line 2 and 7 in the code. Adjust the rows to the amount of data you have.

In case you don't know how to implement the code: Open VBE (ALT+F11), goto top menu's, insert>module and paste the code into the empty field.

Good luck and best regards,
Trowa

Trowa · Answer

You are welcome Naeem.

1. When there is a blank row in your list the filter won't work properly, since you now have two lists. Make sure there are no empty rows in your list.

2. To autorun the code, right click sheet2's tab and select "view code", then insert the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A2") <> "" Then Call Test Else Exit Sub
End Sub

The code will now run whenever you enter something in cell A2 of sheet2. When you erase the data in sheet2's cell A2, the compiled list of sheet3 will still be there untill a new entry has been made in sheet2's A2.

Does this suit your needs?

Best regards,
Trowa

rizvisa1 · Answer

As as mentioned by Trowa, that you can delete blank rows. Other option could be Replace the following lines Range("A2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("A2") With the code below. I have added your other filters B2(for date), c2(for item), d2 (for cost). Leave the cell on which you do not want to filter blank. If the cells (a2, b2, c2, d2) are not empty then the script will apply the filter based upon the text. Range("A2").Select Cells.Select Selection.AutoFilter If (Sheets("Sheet2").Range("A2") <> "") Then Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("A2"), Operator:=xlAnd, Criteria2:="<>" End If If (Sheets("Sheet2").Range("B2") <> "") Then Selection.AutoFilter Field:=2, Criteria1:=Sheets("Sheet2").Range("B2").Text, Operator:=xlAnd, Criteria2:="<>" End If If (Sheets("Sheet2").Range("C2") <> "") Then Selection.AutoFilter Field:=3, Criteria1:=Sheets("Sheet2").Range("c2"), Operator:=xlAnd, Criteria2:="<>" End If If (Sheets("Sheet2").Range("D2") <> "") Then Selection.AutoFilter Field:=4, Criteria1:=Sheets("Sheet2").Range("d2"), Operator:=xlAnd, Criteria2:="<>" End If

rizvisa1 · Answer

I dont get any error. What the error message is or what line it stops at ?. What values you had typed when you get the error ? I need more information to know what is the issue.

I have made 3 changes to the macro 

Change No. 1
Replace
Private Sub Worksheet_Change(ByVal Target As Range)

  If Range("A2") <> "" Then Call Test Else Exit Sub
End Sub

with this 

Private Sub Worksheet_Change(ByVal Target As Range)

If ((Range("A2") = "") And _
    (Range("B2") = "") And _
    (Range("C2") = "") And _
    (Range("D2") = "")) Then
    
    Exit Sub
Else
    Call Test
End If

End Sub


Change No. 2

Replace

    Sheets("Sheet3").Rows("2:10").ClearContents
    Sheets("sheet1").Select

With 

    If ((Sheets("Sheet2").Range("A2") = "") And _
        (Sheets("Sheet2").Range("B2") = "") And _
        (Sheets("Sheet2").Range("C2") = "") And _
        (Sheets("Sheet2").Range("D2") = "")) Then
     
        Exit Sub
    End If
    
    Sheets("Sheet3").Rows("2:65536").ClearContents
    Sheets("sheet1").Select
        
    If ActiveSheet.AutoFilterMode Then
        Cells.Select
        Selection.AutoFilter
    End If


Change  No. 3
Replace

Rows("2:10").Select

With
    lMaxRowA = Cells(65536, 1).End(xlUp).Row
    lMaxRowB = Cells(6553, 2).End(xlUp).Row
    lMaxRowC = Cells(65536, 3).End(xlUp).Row
    lMaxRowD = Cells(65536, 4).End(xlUp).Row
    
    lMaxRow = lMaxRowA
    If (lMaxRowB > lMaxRow) Then lMaxRow = lMaxRowB
    If (lMaxRowC > lMaxRow) Then lMaxRow = lMaxRowC
    If (lMaxRowD > lMaxRow) Then lMaxRow = lMaxRowD
                  
    If (lMaxRow = 1) Then Exit Sub

    Rows("2:" & lMaxRow).Select

rizvisa1 · Answer

What sheet Naeem ? I dont have any sheet. I just took Your data (in the initial question) to test. For macro, I just used Trowa macro and made few modification to it. The 3 changes I just made were to make it better. But it still wont address the error you got as I never got any error. You need to tell what the error message is or what line it stops at ?. What values you had typed when you get the error ?

rizvisa1 · Answer

How are you typing in the date ? Date is a bit wacky thing in excel. Click on cell where you have data. and see how how actually date looks like. I think it would be like dd/mm/yyyy format or mm/dd/yyyy format. You need to type in the same manner. Here you have like 01-Jan-10. This is formatted view,. But if you look at the cell, you would see the date is showing up as 01/01/2010. Also if some thing looks like a date may not be really a date but just a text in excel. So Naeem, check those things

rizvisa1 · Answer

It can be done but the issue is your blank cells. Unless you can be sure that there never, ever will a blank cell for date. Hard to answer your question without knowing what cell can be blank or what cell will never be blank etc. So short answer is depends on how data would look

Game Start Now · Answer

Data Sheet Is Sheet1
Example
Customer Name                   Date                     Item                   Amount
Naeem                          01-Jan-10              Handset                      15000
Trowa                          15-Jan-10               HandSet                      18000

This Row Is Blank

Rizvisa                          18-Jan-10               Hand Set                  25000
Rahul                            14-Jan-10              Battery                       250
This Is Data For Example

Now In Sheet 2
E2 & F2 Is Input Date Range Like E2 Belongs To Starting Date Range & F2 Belongs To Ending Date Range.

If I Type In E2 As 01-Jan-10 F2 As 15-Jan-10.

In Sheet 3 It Copy The Filtered Data From Sheet 1 Which Date Belongs To 01-Jan-10 To 15-Jan-10.

I Want This Result In Sheet3..

Customer Name                 Date                Item                 Amount
Naeem                         01-Jan-10        Handset                15000
Trowa                          15-Jan-10       HandSet                 18000
Rahul                           14-Jan-10          Battery                   250

Filter Donot Count That Blank Row In Result Sheet 3........

Did You Got This Or 1 E-mail You That Sheet..... ?

rizvisa1 · Answer

OK man you can try this. If you put any value n E2, the macro will filter on less than or equal to that date. So if you want to filter on only one specific date, just put that date in b2 If you want to filter on a range of date then put start date in b2 and end date in e2 if you want filter on any thing less or equal than a given date, then put that date in e2 if you want to filter on any thing more or equal to a date then put that that in b2 and in e2 put some thing way in future may be like 12/31/2025 Also I would recommend that if you are formatting dates, better format them in yyyy format instead of yy format as excel for example will think 9999 as 1999 if you have yy formatting or even 50 would be 1950 instead of 2050 Make these changes Change 1 Replace If ((Sheets("Sheet2").Range("A2") = "") And _ (Sheets("Sheet2").Range("B2") = "") And _ (Sheets("Sheet2").Range("C2") = "") And _ (Sheets("Sheet2").Range("D2") = "") Then Exit Sub End If With If ((Sheets("Sheet2").Range("A2") = "") And _ (Sheets("Sheet2").Range("B2") = "") And _ (Sheets("Sheet2").Range("C2") = "") And _ (Sheets("Sheet2").Range("D2") = "") And _ (Sheets("Sheet2").Range("E2") = "")) Then Exit Sub End If Change 2 Replace If (Sheets("Sheet2").Range("B2") <> "") Then Selection.AutoFilter Field:=2, Criteria1:=Sheets("Sheet2").Range("B2").Text, Operator:=xlAnd, Criteria2:="<>" With If (Sheets("Sheet2").Range("B2") <> "") And (Sheets("Sheet2").Range("E2") <> "") Then Selection.AutoFilter Field:=2, Criteria1:=">=" & Sheets("Sheet2").Range("B2").Text, Operator:=xlAnd, Criteria2:="<=" & Sheets("Sheet2").Range("E2").Text ElseIf (Sheets("Sheet2").Range("B2") = "") And (Sheets("Sheet2").Range("E2") <> "") Then Selection.AutoFilter Field:=2, Criteria1:="<=" & Sheets("Sheet2").Range("E2").Text, Operator:=xlAnd, Criteria2:="<>" ElseIf (Sheets("Sheet2").Range("B2") <> "") Then Selection.AutoFilter Field:=2, Criteria1:=Sheets("Sheet2").Range("B2").Text, Operator:=xlAnd, Criteria2:="<>" End If Change 3 Change Private Sub Worksheet_Change(ByVal Target As Range) If ((Range("A2") = "") And _ (Range("B2") = "") And _ (Range("C2") = "") And _ (Range("D2") = "")) Then Exit Sub Else Call Test End If End Sub With Private Sub Worksheet_Change(ByVal Target As Range) If ((Range("A2") = "") And _ (Range("B2") = "") And _ (Range("C2") = "") And _ (Range("D2") = "")And _ (Range("E2") = "")) Then Exit Sub Else Call Test End If End Sub

Ketan · Answer

Very educational and helpful

sagay · Answer

can we execute the same thing without macro

Filter the data in Excel through formula

11 responses

Similar discussions

Subscribe To Our Newsletter!