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.


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


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