filter after deduction

[ExcelNewbie2020]

sirs,

from the given table.. i would like to deduct payment from the loan amount in which the result will be filtered by those with balances only (ignoring zero balance). If possible with no helper column.

testing.xlsx
	A	B	C	D	E	F
1	NAME	LOAN AMOUNT	PAYMENT
2	Lara	10000	5000
3	Gayle	10000			EXPECTED RESULT
4	Ponting	10000	2000		NAME	BALANCE
5	Welles	10000			Lara	10000
6	Lara		3000		Gayle	10000
7	Murdoch	10000			Welles	10000
8	Ponting		3000		Murdoch	10000
9	Coolidge	10000	1000		Porter	10000
10	Porter	10000			Coolidge	9000
11	Lara	10000	2000		Ponting	5000
12	Perot	10000	10000
13	Kipling	10000	10000
14
Sheet18

 

[Skybluekid]

Try the below

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		NAME	LOAN AMOUNT	PAYMENT		EXPECTED RESULT
3						NAME	BALANCE		Name	Balance
4		Lara	10000	5000		Lara	10000		Lara	0
5		Gayle	10000			Gayle	10000		Gayle	10000
6		Ponting	10000	2000		Welles	10000		Ponting	5000
7		Welles	10000			Murdoch	10000		Welles	10000
8		Lara		3000		Porter	10000		Murdoch	10000
9		Murdoch	10000			Coolidge	9000		Coolidge	9000
10		Ponting		3000		Ponting	5000		Porter	10000
11		Coolidge	10000	1000					Perot	0
12		Porter	10000						Kipling	0
13		Lara	10000	2000
14		Perot	10000	10000
15		Kipling	10000	10000
16
17
Sheet5

Cell Formulas
Range	Formula
I4:I12	I4	=UNIQUE(FILTER(B4:B15,C4:C15>0))
J4:J12	J4	=UNIQUE(FILTER(C4:C15,C4:C15>0))-SUMIF(B4:B15,I4#,D4:D15)
Dynamic array formulas.

 

[ExcelNewbie2020]

Try the below

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		NAME	LOAN AMOUNT	PAYMENT		EXPECTED RESULT
3						NAME	BALANCE		Name	Balance
4		Lara	10000	5000		Lara	10000		Lara	0
5		Gayle	10000			Gayle	10000		Gayle	10000
6		Ponting	10000	2000		Welles	10000		Ponting	5000
7		Welles	10000			Murdoch	10000		Welles	10000
8		Lara		3000		Porter	10000		Murdoch	10000
9		Murdoch	10000			Coolidge	9000		Coolidge	9000
10		Ponting		3000		Ponting	5000		Porter	10000
11		Coolidge	10000	1000					Perot	0
12		Porter	10000						Kipling	0
13		Lara	10000	2000
14		Perot	10000	10000
15		Kipling	10000	10000
16
17
Sheet5

Cell Formulas
Range	Formula
I4:I12	I4	=UNIQUE(FILTER(B4:B15,C4:C15>0))
J4:J12	J4	=UNIQUE(FILTER(C4:C15,C4:C15>0))-SUMIF(B4:B15,I4#,D4:D15)
Dynamic array formulas.

thank you.. but "Lara" should have a balance of 10,000..

 

[Skybluekid]

oops see below

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		NAME	LOAN AMOUNT	PAYMENT		EXPECTED RESULT
3						NAME	BALANCE		Name	Balance
4		Lara	10000	5000		Lara	10000		Lara	10000
5		Gayle	10000			Gayle	10000		Gayle	10000
6		Ponting	10000	2000		Welles	10000		Ponting	5000
7		Welles	10000			Murdoch	10000		Welles	10000
8		Lara		3000		Porter	10000		Murdoch	10000
9		Murdoch	10000			Coolidge	9000		Coolidge	9000
10		Ponting		3000		Ponting	5000		Porter	10000
11		Coolidge	10000	1000
12		Porter	10000
13		Lara	10000	2000
14		Perot	10000	10000
15		Kipling	10000	10000
16
Sheet5

Cell Formulas
Range	Formula
I4:I10	I4	=UNIQUE(FILTER(B4:B15,SUMIF(B4:B15,B4:B15,C4:C15)-SUMIF(B4:B15,B4:B15,D4:D15)>0))
J4:J10	J4	=SUMIF(B4:B15,I4#,C4:C15)-SUMIF(B4:B15,I4#,D4:D15)
Dynamic array formulas.

 

[Skybluekid]

To get it sorted decending, I have had create another column.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		NAME	LOAN AMOUNT	PAYMENT		EXPECTED RESULT
3						NAME	BALANCE		Name	Balance
4		Lara	10000	5000		Lara	10000		Lara	10000		Lara	10000
5		Gayle	10000			Gayle	10000		Gayle	10000		Gayle	10000
6		Ponting	10000	2000		Welles	10000		Welles	10000		Ponting	5000
7		Welles	10000			Murdoch	10000		Murdoch	10000		Welles	10000
8		Lara		3000		Porter	10000		Porter	10000		Murdoch	10000
9		Murdoch	10000			Coolidge	9000		Coolidge	9000		Coolidge	9000
10		Ponting		3000		Ponting	5000		Ponting	5000		Porter	10000
11		Coolidge	10000	1000
12		Porter	10000
13		Lara	10000	2000
14		Perot	10000	10000
15		Kipling	10000	10000
16
17
Sheet5

Cell Formulas
Range	Formula
I4:J10	I4	=SORTBY(L4:M10,M4#,-1)
L4:L10	L4	=UNIQUE(FILTER(B4:B15,SUMIF(B4:B15,B4:B15,C4:C15)-SUMIF(B4:B15,B4:B15,D4:D15)>0))
M4:M10	M4	=SUMIF(B4:B15,L4#,C4:C15)-SUMIF(B4:B15,L4#,D4:D15)
Dynamic array formulas.

 

[ExcelNewbie2020]

oops see below

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		NAME	LOAN AMOUNT	PAYMENT		EXPECTED RESULT
3						NAME	BALANCE		Name	Balance
4		Lara	10000	5000		Lara	10000		Lara	10000
5		Gayle	10000			Gayle	10000		Gayle	10000
6		Ponting	10000	2000		Welles	10000		Ponting	5000
7		Welles	10000			Murdoch	10000		Welles	10000
8		Lara		3000		Porter	10000		Murdoch	10000
9		Murdoch	10000			Coolidge	9000		Coolidge	9000
10		Ponting		3000		Ponting	5000		Porter	10000
11		Coolidge	10000	1000
12		Porter	10000
13		Lara	10000	2000
14		Perot	10000	10000
15		Kipling	10000	10000
16
Sheet5

Cell Formulas
Range	Formula
I4:I10	I4	=UNIQUE(FILTER(B4:B15,SUMIF(B4:B15,B4:B15,C4:C15)-SUMIF(B4:B15,B4:B15,D4:D15)>0))
J4:J10	J4	=SUMIF(B4:B15,I4#,C4:C15)-SUMIF(B4:B15,I4#,D4:D15)
Dynamic array formulas.

this worx.. thank you

 

[Skybluekid]

You are very welcome