# Filter the data in Excel through formula

Solved/Closed
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 - Updated on Nov 11, 2018 at 03:46 PM
Ash - Jan 9, 2018 at 02:33 AM
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,
Naeem

## 11 responses

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
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Jan 30, 2010 at 01:30 AM
Thanks Alot My Friend

This Formula Is Really Working Dude But If There Is A Gape Between Data. Means If A4 Is Blank Than Don't Show That Blank Row In Filtered Data In Sheet 3.

And Thanks Again My Friend...

Regards,

Naeem
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Jan 31, 2010 at 10:52 AM
Thanks Alot My Friend

This Formula Is Really Working Dude But If There Is A Gape Between Data. Means If A4 Is Blank Than Don't Show That Blank Row In Filtered Data In Sheet 3. & 1 More Thing Friend, When I Type The Customer Name In A2 In Sheet 2. After That I Want To Play The Macro To Get The Result In Sheet 3.

Is That Possible That When I Type Customer Name In A2 In Sheet 2. Macro Play Auto Run And Show The Result.

And Thanks Again My Friend...

Regards,

Naeem
It really works...but I wanted one liner if possible. which can filter and let me know the count and sum of it..
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 1, 2010 at 02:08 PM
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
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 03:31 AM
Thx Friend This Formula Is Working In Blank Rows...

But When I Leave A2 As Blank In Sheet 2 & Run The Macro, It Give Me Debug Error......

Thanks & Regards,

Naeem
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 2, 2010 at 08:06 AM
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
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 08:14 AM
Yup

Format Was Changed Dude

Thx Alot My Friend :)

Love You :)

Can I Give The Date Range In These Formula?

Like Date Range From 01-Jan-10 To 31-Jan-10

If I Type In Sheet 2 In E Column As Starting Date & F Column As Ending Date....

So It Filter As Starting Date & Ending Date....

If I Dont Type Any Date In These Format Do Nothing..
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.

Best regards,
Trowa
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 1, 2010 at 08:36 AM
It Works My Friend But I Have A Problem With Blank Row:(

Hope You Will Find The Solution Of That........

Best Regards,
Naeem
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 08:41 AM

Didn't find the answer you are looking for?

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 2, 2010 at 07:47 AM
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 ?
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 08:00 AM
Just Like

Formula Filtered The Data Through Customer Name Wise Is Perfect

But It Is Not Filtering The Data When I Type Date For Filter..........

Got It ?
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 09:50 AM
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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 2, 2010 at 06:52 AM
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
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 07:36 AM
My Friend Can You E-mail Me That Sheet.

Thanks & Regards,
Naeem
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 07:57 AM
This Formula Is Not Filtering The Data By Date, If I Type Date In B2 In Sheet 2.......

Else Everything Is Fine :0
i dont know how to apply this formula pls help me
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 2, 2010 at 09:05 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 2, 2010 at 10:27 AM
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
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Feb 2, 2010 at 10:46 AM
Thanks Alot Genius..... :)

Love You :)

Regards,
Naeem
Hi