Macro to extract data based on specific criteria

[nikhil0311]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Selection Criteria			Raw Data
2	Region	APAC		Deal ID	Client	Segment	Product	Region	Country	Revenue	Stage	Date	Banker
3	Country	All		6678	Konami	Energy	CC	APAC	China	$50	Closed	1-Mar-18	Sam
4	Client	All		1234567	JCB	Midwest	FX	APAC	India	$100	Closed	3-Mar-18	Arsene
5	Stage	Closed		1234567	JCB	Midwest	CC	APAC	India	$75	Closed	2-Mar-18	Jose
6	Banker	All		6678	Konami	Energy	FX	APAC	India	$50	Closed	1-Mar-18	Arsene
7	Segment	All
8	Product	All
9	Top X	2
top X

Book1
	B	C	D	E	F	G	H	I	J	K	L
1	Final Output
2	#	Client	Segment	Product	Banker	Region	Country	Stage	Date	Deal ID	Revenue
3		JCB	Midwest	FX; CC	Arsene; Jose	APAC	India	Closed	3-Mar-18	1234567	$175
4	1	JCB Total									$175
5		Konami	Energy	FX; CC	Arsene; sam	APAC	India;China	Closed	1-Mar-18	6678	$100
6	2	Konami Total									$100
7		Grand Total									$275
8
Output

Sheet Top X - A2:B9 is the selection criteria. D2:M12 is the Raw data. Sheet Output B1 : B7 is the final output
I know this can be done by formulas and pivot but I need a macro. Can someone please provide a code for below criteria?
1 - arrange the columns as per row 2 order
2 - column B should have sr. no.
3 - Column C & L should have subtotals
4 - Product to be concatenated based on Deal ID (refer cell E3). For ex Client JCB have 2 products i.e. FX & CC (refer raw data)
5 - Banker to be concatenated based on Deal ID (refer column F) For ex Client JCB have 2 Bankers i.e. Arsene & Jose
6 - Country to be concatenated based on deal ID (refer column H) for ex Client Konami appears in 2 countries China & India.
7 - If product appear multiple time then the code should take only distinct value. For example if 1 deal id have FX, FX, CC then final output should be (FX; CC) and not (FX; FX; CC). Similar logic is applicable for banker and Country
8 - Sort order 1 - Revenue (Highest to Lowest) 2 - Date (oldest to newest)
9 - data should change and retrive the result based on below selection criteria. For Example - here I have selected Region = APAC, Stage = Closed and Top = 2 (based on Revenue)

Book1
	C	D
24	Region	APAC
25	Country	All
26	Client	All
27	Stage	Closed
28	Banker	All
29	Segment	All
30	Product	All
31	Top X	2
Output

 

[nikhil0311]

hello, please can anyone look into this? let me know if this is possible or not.

 

[nikhil0311]

hello friends, can anyone like to take a shot on this?