Report

Filter The Data In Excel Through Formula [Solved]

Game Start Now 50Posts January 21, 2010Registration date September 3, 2010Last seen - Jan 27, 2010 5:57am GMT - Last answer on Feb 2, 2010 3:46pm GMT Game Start Now

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,
NaeemConfiguration: Windows XP Firefox 3.5.7

Filter The Data In Excel Through Formula »

I have an answer

Best answers

Filter The Data In Excel Through Formula - Search

Take data from excel through VB.NET - - Forum

Best answer: To create form go to visual basic editor (ALT + F11) >>Insert>> Userform find how to fill controls (text boxes, labels, buttons) into a userform by searching in google When userform is created and make a macro to show that userform on clickin

Validate cell data in excel through VB - Forum

Best answer: am not sure whether I have understood your question correctly according to me no need of a macro you have validation in the cells E1 to E5 and also F1 to F5 type the following formula in D1 =IF(C1=B1,E1,F1) if there is not entry in E1 or F1 (

Import data from text file and run query and export data into excel through vba access - Forum

Hello, I know little about VBA, but I am trying to do the following: I have numerous text files (no extension) and I need to import only some of the data for all of the files to Excel. All files have the same format, however they may have a different...

How to filter same data in excel - Forum

Hello, In my Excel there is a column, say, column A, where all the cells are text string such as company names. My task is to filter all the rows where the cells in column A contain the same company names. However, my problem is the company names was...

Deleting data in excel file through macro - Forum

Best answer: Hi, I have the same challenge, delete every row except for a specified value in col A. I'd like to use the approach listed for the macro, where you highlight a cell's value to keep, and delete everything else. When I pasted in the macro as sh

Macro to filter data in excel 2007 - Forum

Best answer: Thanks for that but the two files/workbooks already exist and I am looking for the actual macro that will cut and paste between the workbooks. Regards KM

I need to transfer the data in excel from one work book to other using formula in Excel - Forum

Hello, I am trying to transfer an array of data from several spreadsheets used by individuals to a "master" spreadsheet that will be stored on a shared drive. How do I transfer the data from an open excel spreadsheet to the closed spreadsheet that is...

Take data from excel into DataSet through VB.NET - - Forum

Best answer: hi could someone tell e how to connect ms access with ms excel?i need it plsss....

Categorizing Data for Excel - Download

Use Categorizing Data For Excel to organize your Excel spreadsheet. In its database, it invites you to classify your recording to table categories. It lets you handle the filed of your database and extract data from it in order to create a new one....

Excel - If formula sum not blank - Tips

Excel - If formula sum not blank Issue Solution Note Issue I'm sorry if my question had already been asked before but I can't find any answer in the internet about summing all values that is not blank. For example: Column A...

Excel - IF formulas for Printing and Calculation - Tips

Excel - IF formulas for Printing and Calculation Issue Solution Note Issue I am trying to get Excel IF formulas to print a text and calculation based on true/false. Such as, if I have some dates, and I don’t need to send a notice to a...

[Excel] – Countif Formula - Tips

[Excel] – Countif Formula Issue Solution Note Issue I have one excel file in that Column "B" has the "pending or Closed" Status and Column "C" has "Severity 1, Severity 2 and Severity 3" I want to count in one cell with "Pending...

Excel "IF" formula problem - Forum

Best answer: the problem is occurring because of "" "text" To avoid the "" error you can enter below formula =IF(SUM(C2,E2:F2)>0,SUM(N(C2)-N(D2)+N(E2)+N(F2)),"") OR =IF(SUM(C2,E2:F2)>0,N(C2)-SUM(E2:F2),"") both will work fine ....

Possible to extract partial data in Excel? - Forum

Best answer: suppose the hyperlink is in A1 in c1 (or any other cell in row 1) type this formula =RIGHT(A1,LEN(A1)-21) suppose other hyperlinks are in A2 A3 etc copy C1 down

Having Excel IF formulas print text And calc - Forum

Best answer: =IF([cell]>=15,"No Notice",IF([cell]<15,"This department only has "&ROUNDUP([cell],0)&"days left","")) Hope this helps.

Trowa Jan 29, 2010 1:44pm GMT

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

Add comment - Permalink (#1)

Game Start Now - Jan 30, 2010 6:30am GMT

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

Game Start Now - Jan 31, 2010 3:52pm GMT

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

Trowa Feb 1, 2010 12:59pm GMT

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

Add comment - Permalink (#4)

Game Start Now - Feb 1, 2010 1:36pm GMT

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

Game Start Now - Feb 2, 2010 1:41pm GMT

Friend Read My Last Msg & Reply......

rizvisa1 1898Posts January 28, 2010Registration date September 2, 2010Last seen Feb 1, 2010 7:08pm GMT

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

Add comment - Permalink (#6)

Game Start Now - Feb 2, 2010 8:31am GMT

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

rizvisa1 1898Posts January 28, 2010Registration date September 2, 2010Last seen Feb 2, 2010 11:52am GMT

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

Add comment - Permalink (#8)

Game Start Now - Feb 2, 2010 12:36pm GMT

My Friend Can You E-mail Me That Sheet.

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

Thanks & Regards,
Naeem

Game Start Now - Feb 2, 2010 12:57pm GMT

This Formula Is Not Filtering The Data By Date, If I Type Date In B2 In Sheet 2.......

Else Everything Is Fine :0

rizvisa1 1898Posts January 28, 2010Registration date September 2, 2010Last seen Feb 2, 2010 12:47pm GMT

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 ?

Add comment - Permalink (#10)

Game Start Now - Feb 2, 2010 1:00pm GMT

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 ?

rizvisa1 1898Posts January 28, 2010Registration date September 2, 2010Last seen Feb 2, 2010 1:06pm GMT

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

Add comment - Permalink (#13)

Game Start Now - Feb 2, 2010 1:14pm GMT

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

rizvisa1 1898Posts January 28, 2010Registration date September 2, 2010Last seen Feb 2, 2010 2:05pm GMT

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

Add comment - Permalink (#16)

Game Start Now 50Posts January 21, 2010Registration date September 3, 2010Last seen Feb 2, 2010 2:50pm GMT

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..... ?

Add comment - Permalink (#17)

rizvisa1 1898Posts January 28, 2010Registration date September 2, 2010Last seen Feb 2, 2010 3:27pm GMT

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

Support

Hardware(20,166)

Internet(1,766)

Linux/Unix(1,542)

MacOS(2,258)

Network(12,941)

Office software(6,000)

Phones/PDA/GPS(9,734)

Programming(3,339)

Software(20,670)

Video games(7,037)

Viruses/security(4,589)

Webmastering(1,452)

Windows(30,484)

Discussions & Opinions

Member's corner(409)

News(530)

Opinions about reviews(43)

Opinions about the news(14)

Reviews on softwares(7,024)

Reviews on tips(493)

Suggestions(1,618)

Filter The Data In Excel Through Formula [Solved]