Extract Selected Data from Excel - Advanced Filter

[Admin]

Hi All,

One can extract selected columns of data from a data set either by using VBA or Microsoft query.
But one can achieve the same, with the less explored Excel's in-built tool Advanced filter.

The figure 1 shows the whole data set (List Range)

Sheet1

*	A	B	C	D	E	F	G	H	I	J
1	ID	Order ID	Product	Quantity	Unit Price	Discount	Status ID	Date Allocated	Purchase Order ID	Inventory ID
2	27	30	Northwind Traders Beer	100	$14.00	0.00%	Invoiced	*	96	83
3	28	30	Northwind Traders Dried Plums	30	$3.50	0.00%	Invoiced	*	*	63
4	29	31	Northwind Traders Dried Pears	10	$30.00	0.00%	Invoiced	*	*	64
5	30	31	Northwind Traders Dried Apples	10	$53.00	0.00%	Invoiced	*	*	65
6	31	31	Northwind Traders Dried Plums	10	$3.50	0.00%	Invoiced	*	*	66
7	32	32	Northwind Traders Chai	15	$18.00	0.00%	Invoiced	*	*	67
8	33	32	Northwind Traders Coffee	20	$46.00	0.00%	Invoiced	*	*	68
9	34	33	Northwind Traders Chocolate Biscuits Mix	30	$9.20	0.00%	Invoiced	*	97	81
10	35	34	Northwind Traders Chocolate Biscuits Mix	20	$9.20	0.00%	Invoiced	*	*	69
11	36	35	Northwind Traders Chocolate	10	$12.75	0.00%	Invoiced	*	*	70
12	37	36	Northwind Traders Clam Chowder	200	$9.65	0.00%	Invoiced	*	98	79
13	38	37	Northwind Traders Curry Sauce	17	$40.00	0.00%	Invoiced	*	*	71
14	39	38	Northwind Traders Coffee	300	$46.00	0.00%	Invoiced	*	99	77
15	40	39	Northwind Traders Chocolate	100	$12.75	0.00%	Invoiced	*	100	75
16	41	40	Northwind Traders Green Tea	200	$2.99	0.00%	Invoiced	*	101	73
17	42	41	Northwind Traders Coffee	300	$46.00	0.00%	Allocated	*	102	104
18	43	42	Northwind Traders Boysenberry Spread	10	$25.00	0.00%	Invoiced	*	*	84
19	44	42	Northwind Traders Cajun Seasoning	10	$22.00	0.00%	Invoiced	*	*	85
20	45	42	Northwind Traders Chocolate Biscuits Mix	10	$9.20	0.00%	Invoiced	*	103	110
21	46	43	Northwind Traders Dried Plums	20	$3.50	0.00%	Allocated	*	*	86
22	47	43	Northwind Traders Green Tea	50	$2.99	0.00%	Allocated	*	*	87
23	48	44	Northwind Traders Chai	25	$18.00	0.00%	Allocated	*	*	88
24	49	44	Northwind Traders Coffee	25	$46.00	0.00%	Allocated	*	*	89
25	50	44	Northwind Traders Green Tea	25	$2.99	0.00%	Allocated	*	*	90

Excel tables to the web >> Excel Jeanie HTML 4

... continued..

[Admin]

and here is the Copy to range

Sheet1

*	M	N
1	Order ID	Status ID

Excel tables to the web >> Excel Jeanie HTML 4


Now goto the Advanced filter, select the list range, check on Copy to another location, select M1:N1 as the Copy to range, and it's done.


and the result is

Sheet1

*	M	N
1	Order ID	Status ID
2	30	Invoiced
3	30	Invoiced
4	31	Invoiced
5	31	Invoiced
6	31	Invoiced
7	32	Invoiced
8	32	Invoiced
9	33	Invoiced
10	34	Invoiced
11	35	Invoiced
12	36	Invoiced
13	37	Invoiced
14	38	Invoiced
15	39	Invoiced
16	40	Invoiced
17	41	Allocated
18	42	Invoiced
19	42	Invoiced
20	42	Invoiced
21	43	Allocated
22	43	Allocated
23	44	Allocated
24	44	Allocated
25	44	Allocated

Excel tables to the web >> Excel Jeanie HTML 4

I hope this will find useful.

[littleiitin]

Awesome ---