Return Top 5 Accounts while excluding accounts placed on an "Exclusion" list

[rhogsett]

I have a list of customer accounts with various past due balances. I would like a formula to rank the accounts based on past due values while excluding specific accounts that have been placed on a separate "Do Not Include" list.

I have used LARGE, which works great until I need to exclude certain accounts from the Top 5 List. I am having difficulty finding/constructing a formula that can achieve the exampled Top 5 Highest Past Due Accounts below.

Please see the example below and please do let me know if there are questions I may clarify.

I have the report below...

Aging Report:

	A	B	C
1	Account #	Customer Name	Past Due Amount
2	1A	Alpha	1,000
3	2B	Bravo	15,000
4	3C	Charlie	20,000
5	4D	Delta	5,000
6	5E	Echo	2,500
7	6F	Foxtrot	15,000
8	7G	Golf	17,000

<tbody>
</tbody>

...that needs to evaluate the exclusion list below...

Do Not Include:

	A	B
	Excluded Account #	Excluded Customer Name
10	2B	Bravo
11	3C	Charlie

<tbody>
</tbody>

...in order to produce the results below:

Top 5 Highest Past Due Accounts:

	A	B	C
20	Account #	Customer Name	Past Due Amount
21	7G	Golf	17,000
22	6F	Foxtrot	15,000
23	4D	Delta	5,000
24	5E	Echo	2,500
25	1A	Alpha	1,000

<tbody>
</tbody>

Any assistance is greatly appreciated! Please let me know where I can provide further clarification.

Thank you,

RGH

 

[jorismoerings]

Hi,

try this:

Book1
	A	B	C	D
1	Account #	Customer Name	Past Due Amount
2	1A	Alpha	1	5
3	2B	Bravo	15
4	3C	Charlie	20
5	4D	Delta	5	3
6	5E	Echo	2,5	4
7	6F	Foxtrot	15	2
8	7G	Golf	17	1
9
10	Excluded Account #	Excluded Customer Name
11	2B	Bravo
12	3C	Charlie
Sheet2

Cell Formulas
Range	Formula
D2	=IF(ISNUMBER(MATCH(B2,$B$11:$B$12,0)),"",1+SUMPRODUCT(($B$2:$B$8<>B$11)*($B$2:$B$8<>$B$12)*($C$2:$C$8>C2)))

 

[FlameRetired]

To output what is posted above in row 21:25 first array enter this formula in D23 as in below and fill down.

=LARGE(IF(ISNA(MATCH($B$2:$B$8,$B$15:$B$16,0)),$D$2:$D$8),ROWS($1:1))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE for short.)

Then array enter this one in B23 as in below, fill down and across.

=INDEX(B$2:B$8,SMALL(IF(($D23=$D$2:$D$8)*(ISNA(MATCH($B$2:$B$8,$B$15:$B$16,0))),ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1),COUNTIF($D$23:$D23,$D23)))

Assumes source data is in B2:D8 and exclude data is in B15:C16

Row\Col	A​	B​	C​	D​
22​	20​	Account #	Customer Name	Past Due Amount
23​	21​	7G	Golf	17000​
24​	22​	6F	Foxtrot	15000​
25​	23​	4D	Delta	5000​
26​	24​	5E	Echo	2500​
27​	25​	1A	Alpha	1000​

 

[rhogsett]

Thank you both, both replies have been extremely helpful. Thank you!

 

[FlameRetired]

You are welcome. Glad it helped, and thank you for the feedback.