Advanced Filter?

[rfinnegan]

Hi All:

I've never used Advanced Filter and I'm having trouble getting the results I want.

Here's my problem -

I have a list of about 12,000 SKUs along with their price and description in columns D,E,F. I need to remove about 2,000 of those SKUs from the original list and I have those SKUs on a separate tab.

Can I use Advanced Filter for this? If so how? The info I've found online hasn't helped.
And if not, is there a clean way to do this? I'm going to need to repeat this process for several months of data (excluding different SKUs each time of course).

Thanks in advance.

 

[pgc01]

Hi

You can do it with the Advanced Filter, but also a very simple way to do it is to use an auxiliary column.

For ex. use column G and a simple:

=ISNUMBER(MATCH())

to check if the SKU appears in the other tab.

Then use autofilter by G

- either filter by TRUE and delete the visible rows

- or, if you want to keep the original table, filter by FALSE and copy the visible rows to somewhere else to get the result table

 

[markmzz]

Hi!

You can try this too (with Advanced Filter):

1) Create the sheets Sheet 1 and Sheet 2 with the layouts below.

	C	D	E	F	G	H	I	J	K	L	M	N			C	D	E	F	G
1		Data Area				Criteria Area			Extract Area		Sheet 1			1		Extract Area			Sheet 2
2														2
3						Start	End							3
4						200	600							4
5		SKU	Price	Description		CalcField			SKU	Price	Description			5		SKU	Price	Description
6		X900001	521	Descrip001		1								6		X900001	521	Descrip001
7		X900002	150	Descrip002										7		X900004	233	Descrip004
8		X900003	729	Descrip003										8		X900007	509	Descrip007
9		X900004	233	Descrip004										9		X900009	223	Descrip009
10		X900005	856	Descrip005										10		X900010	214	Descrip010
11		X900006	144	Descrip006										11		X900012	385	Descrip012
12		X900007	509	Descrip007										12		X900013	369	Descrip013
13		X900008	755	Descrip008										13		X900015	420	Descrip015
14		X900009	223	Descrip009										14
15		X900010	214	Descrip010										15
16		X900011	138	Descrip011										16
17		X900012	385	Descrip012										17
18		X900013	369	Descrip013										18
19		X900014	898	Descrip014										19
20		X900015	420	Descrip015										20
21														21
***	**	*********	******	**********	**	***********	***********	**	***********	*******	***********	**		***	**	***********	********	**********	********

<tbody>
</tbody>

2) After that, in H6, enter the formula (if you want, you can use another formula with the result TRUE or FALSE / 0 or 1 and a cell in row 6 of the Data Area) below:

=(E6>=$H$4)*(E6<=$I$4)

3) Now, enter the values in H4 and I4.

4) After that, in the Advanced Filter dialog box, enter the ranges below:

List range: $D$5:$F$20
Criteria range: $H$5:$H$6
Copy to: $K$5:$M$5

5) Finally, click OK.

Ps: To copy to another sheet (Sheet 1 to Sheet 2), in the step 4, do this:

4) After that, with Sheet 2 active (with a Extract Area $D$5:$F$5 with the labels SKU, Price and Description), in the Advanced Filter dialog box, enter the ranges below:

List range: 'Sheet 1'!$D$5:$F$20
Criteria range: 'Sheet 1'!$H$5:$H$6
Copy to: 'Sheet 2'!$D$5:$F$5

I hope that this helps.

Markmzz