filter, sort and remove extra name

[ExcelNewbie2020]

I have a current formula that works in filter and sort but it also gives duplicate name. Now, I would like to remove the extra or duplicate name. Is there any way to modify the formula?..thanks

H10 =SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1)

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NAME	DATE	AMOUNT	PAYMENT	BALANCE
2	NAME1	18-01-23	5000	1000	1000
3	NAME1	18-01-23		3000
4	NAME3	25-01-23	10000	3000	2000
5	NAME4	12-12-22	5000	2000	0
6	NAME4	12-12-22		1000
7	NAME6	05-02-23	10000		7000
8	NAME7							CURRENT FORMULA					EXPECTED RESULT
9	NAME8							NAME	DATE	BALANCE			NAME	DATE	BALANCE
10	NAME3	25-01-23		5000				NAME1	18-01-23	1000			NAME1	01-18-23	1000
11	NAME10							NAME1	02-02-23	5000				02-02-23	5000
12	NAME6	05-02-23		3000				NAME3	25-01-23	2000			NAME3	01-25-23	2000
13	NAME12							NAME6	05-02-23	7000			NAME6	02-05-23	7000
14	NAME1	02-02-23	10000	3000	5000			NAME6	13-02-23	10000				02-13-23	10000
15	NAME1	02-02-23		2000
16	NAME15
17	NAME6	13-02-23	10000		10000
18	NAME17
19	NAME4	12-12-22		2000
20	NAME19
21	NAME20
Sheet21

Cell Formulas
Range	Formula
H10:J14	H10	=SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1)
E2:E21	E2	=IF(C2="","",SUMIFS(C:C,B:B,B2,A:A,A2)-SUMIFS(D:D,B:B,B2,A:A,A2))
Dynamic array formulas.

 

[ISY]

Hi

=LET(K,SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1),HSTACK(SCAN(,CHOOSECOLS(K,1),LAMBDA(x,y,IF(x=y,"",y))),DROP(K,,1)))

 

[ISY]

Hi

otherwise
=LET(K,SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1),CHOOSE({1,2,2},SCAN(,SCEGLI.COL(K,1),LAMBDA(x,y,IF(x=y,"",y))),K))

 

[Geo Jul]

I have a current formula that works in filter and sort but it also gives duplicate name. Now, I would like to remove the extra or duplicate name. Is there any way to modify the formula?..thanks

H10 =SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1)

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NAME	DATE	AMOUNT	PAYMENT	BALANCE
2	NAME1	18-01-23	5000	1000	1000
3	NAME1	18-01-23		3000
4	NAME3	25-01-23	10000	3000	2000
5	NAME4	12-12-22	5000	2000	0
6	NAME4	12-12-22		1000
7	NAME6	05-02-23	10000		7000
8	NAME7							CURRENT FORMULA					EXPECTED RESULT
9	NAME8							NAME	DATE	BALANCE			NAME	DATE	BALANCE
10	NAME3	25-01-23		5000				NAME1	18-01-23	1000			NAME1	01-18-23	1000
11	NAME10							NAME1	02-02-23	5000				02-02-23	5000
12	NAME6	05-02-23		3000				NAME3	25-01-23	2000			NAME3	01-25-23	2000
13	NAME12							NAME6	05-02-23	7000			NAME6	02-05-23	7000
14	NAME1	02-02-23	10000	3000	5000			NAME6	13-02-23	10000				02-13-23	10000
15	NAME1	02-02-23		2000
16	NAME15
17	NAME6	13-02-23	10000		10000
18	NAME17
19	NAME4	12-12-22		2000
20	NAME19
21	NAME20
Sheet21

Cell Formulas
Range	Formula
H10:J14	H10	=SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1)
E2:E21	E2	=IF(C2="","",SUMIFS(C:C,B:B,B2,A:A,A2)-SUMIFS(D:D,B:B,B2,A:A,A2))
Dynamic array formulas.

Please how to amend your formula if like name1 made payment in different date​

and balance should read 1000​

 

[Fluff]

@George Jululian Please do not "hijack" other peoples threads. You need to start a thread of your own.

 

[ExcelNewbie2020]

Hi

otherwise
=LET(K,SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1),CHOOSE({1,2,2},SCAN(,SCEGLI.COL(K,1),LAMBDA(x,y,IF(x=y,"",y))),K))

thanks man, appreciate the effort.. but both the formula gives different result..

 

[Geo Jul]

@George Jululian Please do not "hijack" other peoples threads. You need to start a thread of your own.

i am Sorry for That

 

[ExcelNewbie2020]

Hi

=LET(K,SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1),HSTACK(SCAN(,CHOOSECOLS(K,1),LAMBDA(x,y,IF(x=y,"",y))),DROP(K,,1)))

hi ISY, when i refresh this formula "=LET(K,SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1),HSTACK(SCAN(,CHOOSECOLS(K,1),LAMBDA(x,y,IF(x=y,"",y))),DROP(K,,1)))" it actually works.. however, when i add additional "name1 (with amount)" it duplicate.

 

[ISY]

Hi

Support column required Cell V2
=CHOOSECOLS(SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1),1)

New formula
=CHOOSE({1,2,2},BYROW(V2#,LAMBDA(x,IF(COUNTIF(V2:x,x)=1,x,""))),SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1))

 

[ExcelNewbie2020]

Hi

Support column required Cell V2
=CHOOSECOLS(SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1),1)

New formula
=CHOOSE({1,2,2},BYROW(V2#,LAMBDA(x,IF(COUNTIF(V2:x,x)=1,x,""))),SORT(FILTER(FILTER(A2:E21,(E2:E21>0)*(E2:E21<>"")),{1,1,0,0,1}),1,1))

thanks man.. it works..