Menu
0
Thanks

A few words of thanks would be greatly appreciated.

# Excel - Filter data through a formula

## 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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

## Related

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

## 0 Comments

Recommended

DON'T MISS

TRENDING GAMES & APPS
• Internet

• Video games

• Video games

• Video games

• Internet

• Internet

• Internet

• Health

• Video Calls

• Audio