Can someone please explain this formula to me

[Martin sherk]

Hello Everyone,

so I am using the below formula for my reconciliation between ERP and bank but I don't quite understand it.

Formulas are in G2 and F2

Copy of GL CLEAEING_1 (3).XLSX
	A	B	C	D	E	F	G	H
1	Account	Assignment	Document Header Text	Document Number	Amount in local currency			Local Currency
2	2612945	5422002132022		542200213	(251.55)	251.55-1		AED
3	2612945	5422002152022		542200215	(284.17)	284.17-1		AED
4	2612945	5422002172022		542200217	(1,175.00)	1175-1		AED
5	2612945	5422002212022		542200221	(389.07)	389.07-1	x	AED
6	2612945	5422002222022		542200222	(5,434.85)	5434.85-1		AED
CTS

Cell Formulas
Range	Formula
F2:F6	F2	=IF(E2<0,-E2&"-"&COUNTIF(E$2:E2,E2),E2&"-"&COUNTIF(E$2:E2,E2))
G2:G6	G2	=IF(COUNTIF($F$2:$F$244,F2)=2,"x","")

 

[Dave Patton]

G2 inserts an X if there is a positive number such as 389.07 as well as negative number 389.07.

The alternative that I show considers the Document Number as well as counting the related positive and negative numbers.

T202211.xlsm
	A	B	C	D	E	F
13	Account			Document Number	Amount in local currency
14	2612945			542200213	-251.55	FALSE
15	2612945			542200215	-284.17	FALSE
16	2612945			542200217	-1175	FALSE
17	2612945			542200221	-389.07	TRUE
18	2612945			542200222	-5434.85	FALSE
19	2612945			542200221	389.07	TRUE
1f

Cell Formulas
Range	Formula
F14:F19	F14	=COUNTIFS($D$14:$D$19,D14,$E$14:$E$19,E14)=COUNTIFS($D$14:$D$19,D14,$E$14:$E$19,-E14)

 

[Dave Patton]

If you want a single entry that will compete the calculations and prepare the report, review the following.
Formula is in H14
The formula sums the amounts (a positive + a negative such as 389.07 will yield 0 i.e. Reconciled)
The reconciled show with 0
The unreconciled numbers show with Document number and amount.

[

T202211.xlsm
	A	B	C	D	E		G	H	I
13	Account	B	C	Document Text ID	Amount			Documents	Amount
14	2612945			542200213	-251.55			542200213	0.00
15	2612945			542200215	-284.17			542200221	0.00
16	2612945			542200217	-1175			542200215	-284.17
17	2612945			542200221	-389.07			542200217	-1,175.00
18	2612945			542200222	-5434.85			542200222	-5,434.85
19	2612945			542200213	251.55
20	2612945			542200221	389.07
1f

Cell Formulas
Range	Formula
H14:I18	H14	=LET(u,UNIQUE(FILTER(rN,rN>"")),SORT(HSTACK(u,SUMIFS(rAmt,rN,u)),2,-1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rAmt	='1f'!$E$14:$E$20	H14
rN	='1f'!$D$14:$D$20	H14

 

[Martin sherk]

If you want a single entry that will compete the calculations and prepare the report, review the following.
Formula is in H14
The formula sums the amounts (a positive + a negative such as 389.07 will yield 0 i.e. Reconciled)
The reconciled show with 0
The unreconciled numbers show with Document number and amount.

[

T202211.xlsm
	A	B	C	D	E		G	H	I
13	Account	B	C	Document Text ID	Amount			Documents	Amount
14	2612945			542200213	-251.55			542200213	0.00
15	2612945			542200215	-284.17			542200221	0.00
16	2612945			542200217	-1175			542200215	-284.17
17	2612945			542200221	-389.07			542200217	-1,175.00
18	2612945			542200222	-5434.85			542200222	-5,434.85
19	2612945			542200213	251.55
20	2612945			542200221	389.07
1f

Cell Formulas
Range	Formula
H14:I18	H14	=LET(u,UNIQUE(FILTER(rN,rN>"")),SORT(HSTACK(u,SUMIFS(rAmt,rN,u)),2,-1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rAmt	='1f'!$E$14:$E$20	H14
rN	='1f'!$D$14:$D$20	H14

Unfortunately Hstach formula is not yet working in my office 365.

 

[Martin sherk]

G2 inserts an X if there is a positive number such as 389.07 as well as negative number 389.07.

The alternative that I show considers the Document Number as well as counting the related positive and negative numbers.

T202211.xlsm
	A	B	C	D	E	F
13	Account			Document Number	Amount in local currency
14	2612945			542200213	-251.55	FALSE
15	2612945			542200215	-284.17	FALSE
16	2612945			542200217	-1175	FALSE
17	2612945			542200221	-389.07	TRUE
18	2612945			542200222	-5434.85	FALSE
19	2612945			542200221	389.07	TRUE
1f

Cell Formulas
Range	Formula
F14:F19	F14	=COUNTIFS($D$14:$D$19,D14,$E$14:$E$19,E14)=COUNTIFS($D$14:$D$19,D14,$E$14:$E$19,-E14)

perfect! that's something to learn from you. Thank you for your time, sir!

 

[Martin sherk]

G2 inserts an X if there is a positive number such as 389.07 as well as negative number 389.07.

The alternative that I show considers the Document Number as well as counting the related positive and negative numbers.

T202211.xlsm
	A	B	C	D	E	F
13	Account			Document Number	Amount in local currency
14	2612945			542200213	-251.55	FALSE
15	2612945			542200215	-284.17	FALSE
16	2612945			542200217	-1175	FALSE
17	2612945			542200221	-389.07	TRUE
18	2612945			542200222	-5434.85	FALSE
19	2612945			542200221	389.07	TRUE
1f

Cell Formulas
Range	Formula
F14:F19	F14	=COUNTIFS($D$14:$D$19,D14,$E$14:$E$19,E14)=COUNTIFS($D$14:$D$19,D14,$E$14:$E$19,-E14)

what if I want to reconcile just the Amounts in column E and ignore the Documents no. in column D?

 

[Dave Patton]

"what if I want to reconcile just the Amounts in column E and ignore the Documents no. in column D?"

I used a Table and with choice False it shows the unreconciled amounts.

T202211x.xlsm
	A	B	C	D	E	F
12				Sum	-6,894.02
13	Account	B	C	Document Text ID	Amount	Result
15	2612945			542200215	-284.17	FALSE
16	2612945			542200217	-1175	FALSE
18	2612945			542200222	-5434.85	FALSE
21
22
1ff

Cell Formulas
Range	Formula
E12	E12	=SUBTOTAL(109,rAmt)
F15:F16,F18	F15	=COUNTIFS($E$14:$E$20,E15)=COUNTIFS($E$14:$E$20,-E15)

Named Ranges
Name	Refers To	Cells
'1ff'!rAmt	='1ff'!$E$14:$E$20	E12, F18, F15:F16

T202211x.xlsm
	A	B	C	D	E	F
12				Sum	0.00
13	Account	B	C	Document Text ID	Amount	Result
14	2612945			542200213	-251.55	TRUE
17	2612945			542200221	-389.07	TRUE
19	2612945			542200213	251.55	TRUE
20	2612945			542200221	389.07	TRUE
21
22
1ff

Cell Formulas
Range	Formula
E12	E12	=SUBTOTAL(109,rAmt)
F14,F17,F19:F20	F14	=COUNTIFS($E$14:$E$20,E14)=COUNTIFS($E$14:$E$20,-E14)

Named Ranges
Name	Refers To	Cells
'1ff'!rAmt	='1ff'!$E$14:$E$20	E12, F19:F20, F17, F14

 

[Martin sherk]

"what if I want to reconcile just the Amounts in column E and ignore the Documents no. in column D?"

I used a Table and with choice False it shows the unreconciled amounts.

T202211x.xlsm
	A	B	C	D	E	F
12				Sum	-6,894.02
13	Account	B	C	Document Text ID	Amount	Result
15	2612945			542200215	-284.17	FALSE
16	2612945			542200217	-1175	FALSE
18	2612945			542200222	-5434.85	FALSE
21
22
1ff

Cell Formulas
Range	Formula
E12	E12	=SUBTOTAL(109,rAmt)
F15:F16,F18	F15	=COUNTIFS($E$14:$E$20,E15)=COUNTIFS($E$14:$E$20,-E15)

Named Ranges
Name	Refers To	Cells
'1ff'!rAmt	='1ff'!$E$14:$E$20	E12, F18, F15:F16

T202211x.xlsm
	A	B	C	D	E	F
12				Sum	0.00
13	Account	B	C	Document Text ID	Amount	Result
14	2612945			542200213	-251.55	TRUE
17	2612945			542200221	-389.07	TRUE
19	2612945			542200213	251.55	TRUE
20	2612945			542200221	389.07	TRUE
21
22
1ff

Cell Formulas
Range	Formula
E12	E12	=SUBTOTAL(109,rAmt)
F14,F17,F19:F20	F14	=COUNTIFS($E$14:$E$20,E14)=COUNTIFS($E$14:$E$20,-E14)

Named Ranges
Name	Refers To	Cells
'1ff'!rAmt	='1ff'!$E$14:$E$20	E12, F19:F20, F17, F14

perfect! Worked smoothly. Thanks.