Excel - Filter data through a formula

August 2017




Issue


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

Solution


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.


Thanks to TrowaD for this tip.

Related


Published by aakai1056. Latest update on February 11, 2012 at 07:12 AM by aakai1056.
This document, titled "Excel - Filter data through a formula," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).