Previous Topic Next Topic

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

Answer 1 / 11
Trowa
Jan 29, 2010 at 08:44 AM
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
9
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.

Waiting For Your Reply Trowa :)

And Thanks Again My Friend...

Regards,

Naeem
0
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.

Waiting For Your Reply

And Thanks Again My Friend...

Regards,

Naeem
0
Buzzy
Feb 28, 2012 at 03:38 PM
Thanks a lot Naeem for your expert advice
0
Ash
Jan 9, 2018 at 02:33 AM
It really works...but I wanted one liner if possible. which can filter and let me know the count and sum of it..
0
Answer 2 / 11
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
2
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......

Please Solve This Error.......

Thanks & Regards,

Naeem
0
Answer 3 / 11
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
2
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..
0
Answer 4 / 11
Trowa
Feb 1, 2010 at 07:59 AM
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
1
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........

Waiting For Your Reply....

Best Regards,
Naeem
0
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
Friend Read My Last Msg & Reply......
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 11
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 ?
1
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 ?
0
Answer 6 / 11
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..... ?
1
Answer 7 / 11
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
0
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.

My E-mail Address is "naeemahmed123@hotmail.com"


Thanks & Regards,
Naeem
0
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
0
siddhu
Feb 10, 2012 at 09:25 PM
i dont know how to apply this formula pls help me
0
Answer 8 / 11
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
0
Answer 9 / 11
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
0
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
0
ajay
Jan 22, 2011 at 10:50 AM
Hi
please help me to put the formulae in excel
in a cell, I am looking for one word... it can be at the end, middle or in the beginning
kindly help me to put the formulae, I know we can that by using the options contains form customs filter...but I want to know is there any formulae to get this
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 27, 2011 at 08:46 AM
your question is not too clear to me. It seems that you shold look into function "FIND"
0
Answer 10 / 11
Ketan
Feb 9, 2012 at 01:51 PM
Very educational and helpful
0
Answer 11 / 11
sagay
Apr 16, 2014 at 09:24 AM
can we execute the same thing without macro
0
levi
Dec 6, 2016 at 09:31 AM
anyone?
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
formulas for 1st and 2nd position in class
jack -
suff954 -

1 response