Macro to get TOP X (10/20/50 etc) records based on selection criteria

[nikhil0311]

Excel 2007
	A	B	C	D	E	F	G
1	Raw Data
2	Relevant	Name	Date	Region	Product Mapping	Stage	Revenue
3	Y	Cognizant	3-Dec-17	APAC	CC	Bid	$ 50
4	N	ABC Ltd	3-Oct-17	CAN	Liq	Mandate	$ 65
5	Y	Citi	10-Jul-17	EMEA	FX	Opp	$ 20
6	Y	Barclays	3-Dec-18	LATAM	Lend	Lost	$ 15
7	Y	Emirates	3-Dec-17	APAC	CC	NQO	$ 14
8	Y	Ethihad	3-Oct-17	APAC	Liq	Bid	$ 19
9	Y	Citi	10-Jul-18	LATAM	CC	Mandate	$ 20
10	Y	Citi	3-Dec-27	LATAM	Liq	Opp	$ 65
11	N	Citi	3-Dec-25	LATAM	CC	Lost	$ 47
12	Y	ABC Ltd	3-Dec-17	CAN	FX	Mandate	$ 85
13	Y	Infosys	3-Oct-17	CAN	FX	Closed	$ 63
14	Y	Wipro	3-Dec-17	APAC	CC	NQO	$ 23
15	Y	Sony	3-Oct-17	APAC	Liq	Bid	$ 67
16	Y	LG	10-Jul-18	LATAM	CC	Mandate	$ 27
17	Y	Sony	3-Dec-27	LATAM	Liq	Opp	$ 20
18	N	Micromax	3-Dec-25	LATAM	CC	Lost	$ 22
19	Y	Motorola	3-Dec-17	CAN	FX	Closed	$ 34
20	Y	Samsung	3-Oct-17	CAN	FX	Closed	$ 16
Sheet2

Excel 2007
	I	J
1	Filter Criteria
2	Filters	Selection criteria
3	Relevant	Y
4	Date	All
5	Region	All
6	Product Mapping	All
7	Stage	All
8	Top 10/20/50/100 etc	10
Sheet2

Excel 2007
	L	M	N	O	P	Q
1	Top 10 Output with Subtotals
2	Name	Date	Region	Product Mapping	Stage	Total
3	ABC Ltd	3-Dec-17	CAN	FX	Mandate	85
4	ABC Ltd Total					85
5	Sony	3-Oct-17	APAC	Liq	Bid	67
6	Sony Total					67
7	Citi	3-Dec-27	LATAM	Liq	Opp	65
8	Citi Total					65
9	Infosys	3-Oct-17	CAN	FX	Closed	63
10	Infosys Total					63
11	Cognizant	3-Dec-17	APAC	CC	Bid	50
12	Cognizant Total					50
13	Motorola	3-Dec-17	CAN	FX	Closed	34
14	Motorola Total					34
15	LG	10-Jul-18	LATAM	CC	Mandate	27
16	LG Total					27
17	Wipro	3-Dec-17	APAC	CC	NQO	23
18	Wipro Total					23
19	Citi	10-Jul-18	LATAM	CC	Mandate	20
20	Citi	10-Jul-17	EMEA	FX	Opp	20
21	Citi Total					40
22	Sony	3-Dec-27	LATAM	Liq	Opp	20
23	Sony Total					20
24	Ethihad	3-Oct-17	APAC	Liq	Bid	19
25	Ethihad Total					19
26	Samsung	3-Oct-17	CAN	FX	Closed	16
27	Samsung Total					16
28	Barclays	3-Dec-18	LATAM	Lend	Lost	15
29	Barclays Total					15
30	Emirates	3-Dec-17	APAC	CC	NQO	14
31	Emirates Total					14
32	Grand Total					538
Sheet2

Column A - G is the Raw data
Column I - J is the filter criteria
Column L - Q is the final output based on the filtered criteria
I want to create a Macro which can givbe me the output (Column L - Q) in a new sheet based on filtered criteria (I - J)

What is Top 10?
Aggregate Revenue based on Name
Sorting sahould be Revenue i.e. Column G (Largest to Smallest), Name (A-Z), Stage (A-Z)
Adding subtotals


Please note that right now in the filter criteria I have only selected Relevant = Yes and Top 10
If I select multiple filter criteria i.e.Relevant = Yes and Date = 2017 & beyond and product = CC and Top 15, I should get the desired output based on selection criteria.

let me know if any further information is required.

 

[admiral100]

Hi,

Looks like all you need is a pivot table

 

[nikhil0311]

Yes. This can be achieved with a Pivot table but i was thinking of a user form where i can select the filter criteria AND EXTRACT the data in a new sheet with defined formating.