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:[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Account #[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Past Due Amount[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1A[/TD]
[TD="align: center"]Alpha[/TD]
[TD="align: center"]1,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]Bravo[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3C[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: center"]20,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4D[/TD]
[TD="align: center"]Delta[/TD]
[TD="align: center"]5,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]5E[/TD]
[TD="align: center"]Echo[/TD]
[TD="align: center"]2,500[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]6F[/TD]
[TD="align: center"]Foxtrot[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]7G[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]17,000[/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]
[/TR]
</tbody>[/TABLE]

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

Do Not Include:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Excluded Account #[/TD]
[TD="align: center"]Excluded Customer Name[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]2B[/TD]
[TD="align: center"]Bravo[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]3C[/TD]
[TD="align: center"]Charlie[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

...in order to produce the results below:

Top 5 Highest Past Due Accounts:
[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]Account #[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Past Due Amount[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]7G[/TD]
[TD="align: center"]Golf[/TD]
[TD="align: center"]17,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"]6F[/TD]
[TD="align: center"]Foxtrot[/TD]
[TD="align: center"]15,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]4D[/TD]
[TD="align: center"]Delta[/TD]
[TD="align: center"]5,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]5E[/TD]
[TD="align: center"]Echo[/TD]
[TD="align: center"]2,500[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]1A[/TD]
[TD="align: center"]Alpha[/TD]
[TD="align: center"]1,000[/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]
[/TR]
</tbody>[/TABLE]

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


[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

22​

[/td][td]

20​

[/td][td]Account #[/td][td]Customer Name[/td][td]Past Due Amount[/td][/tr]
[tr][td]

23​

[/td][td]

21​

[/td][td]7G[/td][td]Golf[/td][td]

17000​

[/td][/tr]
[tr][td]

24​

[/td][td]

22​

[/td][td]6F[/td][td]Foxtrot[/td][td]

15000​

[/td][/tr]
[tr][td]

25​

[/td][td]

23​

[/td][td]4D[/td][td]Delta[/td][td]

5000​

[/td][/tr]
[tr][td]

26​

[/td][td]

24​

[/td][td]5E[/td][td]Echo[/td][td]

2500​

[/td][/tr]
[tr][td]

27​

[/td][td]

25​

[/td][td]1A[/td][td]Alpha[/td][td]

1000​

[/td][/tr]
[/table]

 

[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.