Multiple payments and multiple invoices formula

[DavidHilbert]

Hi,
I have search thru the web and this site for two days to no avail. The problem is my excel is formatted in a different way compared to the solution that was presented. Could someone please help me please? Here is what I want excel to do: first see if the sum of the payment across a row is greater than the open balance/invoice balance display zero in the open balance. second if the first is true I want excel to find identical supplier's name excluding the current row the formula is in, third if excel finds an identical supplier's name the remaining over-payment from the first identical supplier and apply it to the second identical supplier and the third identical supplier and so on. But I want all this to happen in one sheet, it is not efficient to do this two sheets.

No.	due	Suppliers (input)	Open Balance2	May-9	May-10	May-11	May-12	May-13
119	due	ABB	-$879.20					$14,000.00​
120	due	ABB	$12,865.20
1	due	adaptive energy	$29,945.00		$3,327.00​
2	due	adaptive energy	$15,472.51		$1,500.00​
3	due	Adder	$8,043.00		$1,500.00​
4	due	Adorama	$10,293.63		$1,500.00​		$1,500.00​
6	due	Ametek	$28,196.00		$2,000.00​			$1,000.00​
7	due	Anaconda	$29,150.00	$750.00​	$850.00​
8	due	Anasys Phoneix Intregation	$96,750.00	$750.00​	$1,500.00​	$23,000.00​	$2,500.00​
9	due	API_Advanced Programs, Inc	$13,310.00	$750.00​			$850.00​
11	due	Bahfed	$9,284.80
12	due	Bahfed	$2,567.52
13	due	BrowserStack	$26,856.00	$1,000.00​	$1,000.00​		$1,000.00​
14	due	Chroma System Solutions	$0.00					$695.67​
15	due	Chronicle LLC, VirusTotal	$49,368.00	$3,500.00​	$1,500.00​		$1,500.00​
16	due	ClearShark	$116,353.08
18	due	Component source	$3,408.16
19	due	concurrent Real-Time	$25,192.00	$2,500.00​			$1,000.00​
20	due	concurrent Real-Time	$54,999.94
21	due	Data security Telesis	$20,001.00
22	due	DBISP,LLC Team	$5,550.00					$750.00​
23	due	D-N-A Integrator	$4,604.45
24	due	Dynatouch (harris computer)	$43,730.00
25	due	Dynatouch (harris computer)	$7,826.00
26	due	Eaton	$4,425.87
27	due	Flyboys	$19,965.57
28	due	ForeFlight LLC	$54,000.00
30	due	GHS_Green Hills Software	$13,814.00
31	due	Gordian R.S. Means Company LLC	$36,000.00
32	due	Hack the box	$12,600.00
33	due	Helpsystem	$61,460.00	$2,500.00​	$1,500.00​		$1,500.00​
34	due	ICAM Technologies	$18,430.00
35	due	IMTRON CORP	$4,554.87
36	due	IMTRON CORP	$7,482.92
37	due	IMTRON CORP	$5,298.91
38	due	InnovMetric Software Inc.	$10,700.00
39	due	International Datacasting	$147,061.38
41	due	L3Harris	$11,370.00
42	due	LDRA Technology, Inc.	$20,000.00
43	due	LEAD Technologies/Leadtools	$6,196.25
44	due	MAK Technologies	$19,975.00
115	due	MCA Safety's	$85.15
116	due	MCA Safety's	$443.35
117	due	MCA Safety's	$170.58
118	due	MCA Safety's	$762.52
119	due	MCA Safety's	$148.46
120	due	MCA Safety's	$69.54
121	due	MCA Safety's	$148.46
122	due	MCA Safety's	$114.26
123	due	MCA Safety's	$38.59
124	due	MCA Safety's	$38.59
45	due	Merlin International	$20,823.37
46	due	Milyli	$29,000.00
47	due	Modern requirement	$1,440.00
48	due	MOOG	$79,163.07			$3,000.00​	$1,500.00​
49	due	Network Technologies Inc	$13,090.00					$1,500.00​
50	due	Nintex	$50,436.50
51	due	Nuaware	$18,066.00
52	due	Owl Cyber Defense	$90,207.78
54	due	PolicyPak Software	$14,218.75
56	due	Progress	$25,632.60	$2,000.00​			$1,000.00​
57	due	QSR International	$17,814.15
58	due	QSR International	$19,815.00
59	due	rockitek	$84,900.00	$750.00​	$850.00​
60	due	Route1	$25,910.00	$750.00​			$750.00​
61	due	Sandpiper CA	$131,540.00	$1,500.00​	$1,500.00​		$1,000.00​
62	due	SAPIEN Technologies	$19,808.00
63	due	Satcom Resources	$13,020.00
64	due	Sayari Labs	$49,000.00
65	due	Singlewire	$3,978.00
67	due	Sonar Source	$4,000.00
68	due	Specops Software	$787.97
69	due	testout corp	$2,748.00
70	due	Thermwood	$2,100.00
71	due	ThunderCat Technology	$37,500.00
72	due	Turn-key	$1,666.00	$1,500.00​	$1,500.00​		$1,500.00​
73	due	Uila	$47,330.00	$1,000.00​	$1,000.00​		$1,000.00​
74	due	VARJO	$6,093.00
76	due	Zona Technology	$36,000.00
77	no due date	AGI	$30,000.00
78	no due date	Blackfish Software, LLC	$22,200.00
79	no due date	BUSINESS FURNITURE	$42,736.29
80	no due date	COMSOL	$35,980.00
81	no due date	Dell	$226,812.00	$1,500.00​	$1,500.00​	$3,000.00​	$500.00​
82	no due date	Docpoint	$194,962.60	$1,000.00​	$1,000.00​		$1,000.00​
83	no due date	Exertis	$96,872.00
84	no due date	GLOBAL INDUSTRIAL	$22,206.75
123	no due date	GSA audit	-$1,000.00				$1,000.00​
85	no due date	HAMILTON	$15,654.21			$2,000.00​
86	no due date	immix	$196,475.32		$7,500.00​		$1,000.00​
87	no due date	LUXOR	$21,030.05					$750.00​
88	no due date	LUXOR	$16,175.60
90	no due date	MMG Investments II, LLC	-$4,500.00	$1,000.00​			$1,000.00​
91	no due date	Mouser Electronics	$1,790.67
121	no due date	Napcloud India	-$8,000.00			$8,000.00​
92	no due date	orsnasco (Essendant)	-$2,138.20				$2,500.00​
93	no due date	orsnasco (Essendant)	$9,617.50
94	no due date	Simpson	$139,041.84		$1,500.00​		$500.00​
95	no due date	TD synnex	$0.00
96	no due date	Testforce	$195,472.04	$7,000.00​	$5,000.00​	$4,000.00​	$4,500.00​
97	no due date	Ultra	$119,288.00	$5,000.00​	$5,000.00​	$2,000.00​	$1,000.00​
98	not due	ActivePDF, Inc	$8,596.00
99	not due	Adorama	$107.33
100	not due	Antigen Plus	$1,350.00
101	not due	Automated Solution, Inc	$5,726.98
102	not due	eLearning brothers	$4,491.92
103	not due	Human Culture Realisation Pty Ltd	$10,293.00
104	not due	IMTRON CORP	$7,511.18
122	not due	Mark Smallwood	$195.00
105	not due	Mouser Electronics	$12,332.31
106	not due	NATIONAL AZON	$5,442.00
107	not due	PTC	$15,960.00
108	not due	QT Company	$3,950.00
109	not due	redgate	$11,926.05
110	not due	SSH Communications Security, Inc	$1,456.90
111	not due	Surveillance Video	$0.00					$48.52​
112	not due	Tandesa LLC	$3,497.27
113	not due	T-Plan limited	$13,860.00
		Rstudio	$0.00

 

[DavidHilbert]

 

[DavidHilbert]

The invoice amount/open balance is in the Open balance while I want the formula in the open balance2 column