Exclude from list, Advance Filter

[srizki]

I have list of account numbers, I need to exclude few account numbers from the list, for example:

[TABLE="width: 260"]
<TBODY>[TR]
[TD="colspan: 3"]Exclude account 9204xxx (should net to zero)</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Exclude cap load account 922xxxx</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Exclude benefits account 926xxxx</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 3"]</SPAN>Exclude benefits acct xxxx008, 047, 048, 050, 052, 054
Exclude allocated rent - 486RENTR, 651AMORTAL
Etc,

Regards
[/TD]
[/TR]
[TR]
[TD="colspan: 3"]</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 3"]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[Akashwani]

Hi Sohail,

I don't know how to do this by using advanced filter, but does this formula solution work for you?...

Excel Workbook
	A	B	C	D	E	F
1	Accounts		Results		Exclude Accounts
2	Test123		Test123		9204xxx
3	9204xxx		Test124		922xxxx
4	Test124		Test125		926xxxx
5	922xxxx		Test126		xxxx008
6	926xxxx		Test127		xxxx047
7	xxxx008		Test128		xxxx048
8	Test125				xxxx050
9	Test126				xxxx052
10	xxxx047				xxxx054
11	xxxx048				489RENTR
12	xxxx050				651AMORTAL
13	Test127
14	xxxx052
15	xxxx054
16	489RENTR
17	651AMORTAL
18	Test128
19
SampleData

The formula in C2 needs to be entered using ctrl shift enter NOT just enter, it can then be copied down.
You will obviously need to change the cell references to suit your layout.

You could also you a Named Range for your (criteria) Exclude Accounts.
So instead of using $E$2:$E$12 in the above formula, you can use a Named Range.

I have used the Named Range, ExAcc in this formula...

=IFERROR(INDEX($A$2:$A$18,SMALL(IF(ISNA(MATCH($A$2:$A$18,ExAcc,0)),ROW($A$2:$A$18)),ROWS(C$2:C2))-ROW($A$2)+1),"")

I hope that helps.

Ak

 

[srizki]

Akaswani,
thank you very much, I have not tried the formula yet, but just by ananlyzing it, it seems this is what I was looking for.
Great, thanks again

Sohail

 

[Marcelo Branco]

Or with Advanced Filter (using data sample provided by Ak)

Put in G2 this formula
=ISNA(MATCH(A2,$E$2:$E$12,0))

In Advanced Filter
pick Copy To
List Range: $A$1:$A$18
Criteria Range: $G$1:$G$2
Copy To:$C$1

M.

 

[srizki]

Thank you Marcelo,
Yes, I wanted to exclude the list from the big list.
But my file has many columns, and the rows that contains these exclusions should be filtered out.

I will try your formula using advance filter, I understood.

Great,

Regards

Sohail