Excel - Filter data through a formula
On February 22,2024 by Tom Routley
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.
Leave a Reply