Finding matching positive and negative numbers

Rambu · Jul 7, 2022

I want to match the data of positive and negative and sort out the residual value on the basis of reference Id's.For e.g
Reference ID and Amount
4567 -$10
4567 $5
4567 $2
4567 $3
4567 $5
6754 $3
6754 $4
6754 -$12
6754 $6
6754 $2

Above two reference ID after calculating in 4567 the residual amount is 5 and
simultaneously in reference ID 6754 residual is 3.So is there any solution that values settingoff to zero should get highlighted or which are not getting offset amount highlighted on the basis of reference ID. Not only the same value offset also by the breakup amount adding up setoff as above example.

Rambu · Jul 8, 2022

Below is the data sample in which I have applied your formula, But highlighted cells which are coming, the amount is not matching with actual total figure. In some of them formulae is not catching.Please look into it.

530279	280.38
530279	-146.95
530279	146.95
530279	-117.56
530279	-280.38
532224	-281.31
532224	387.09
532224	-387.09
532376	-152.82
535274	-594.85
535274	594.85
535274	-187.42
546227	-298.13
547390	-237.85
547390	237.85
547390	-139.89
547390	-112.65
547390	139.89
547634	-276.86
547634	-225.92
547634	276.86
547634	-200.45
547634	225.92
547915	-533.73
548216	428.18
548216	-428.18
548216	-384.08
548216	-318.37
548216	384.08
556381	-152.82
558357	-156.72
559299	-2750.48
559299	2750.48
559299	-2691.39
559299	2691.39
559299	-1234.27
559299	-1212.22
559299	1234.27
559518	-2250.87
559518	2250.87
559518	-2040.87
559518	2040.87
559518	-499.89
559518	854.72
559518	-854.72
559520	-17934.30
559520	-794.90
559520	-794.90
559520	13394.22
559520	-9085.76
559520	-3513.56
559520	17934.30
559520	-17728.30
559520	17728.30
559520	13724.97
559520	-13928.81
559520	-13724.97
559520	13928.81
559737	-1128.47
559737	1128.47
559737	-1104.46
559786	-2631.15
559786	2631.15
559786	-2156.00
560995	1042.53
560995	-1042.53
560995	-879.07
560995	879.07
560995	-566.68
560995	566.68
560995	-509.36
561457	-1457.45
561457	1590.98
561457	-4233.94
561457	-597.17
561457	-431.19
561457	-366.83
561457	4600.78
561457	1457.45
561457	-1590.98
561649	-1674.68
561649	1694.26
561649	-62.68
561649	-768.64
561649	-940.54
561649	1674.68
561649	-903.87
561649	1844.41
561649	-2027.78
561649	2027.78
561649	-1694.26
562169	-115.10
562169	124.66
562169	115.10
562169	-75.92
562169	-148.67
562169	-124.66
562169	148.67
562169	75.92
562169	-58.77
562301	881.71
562301	-881.71
562301	-881.71
563622	-319.37
563622	348.75
563622	-117.52
563622	319.37
563622	-105.81
563622	422.21
563622	-422.21
563622	-348.75
564134	2513.93
564134	-1848.77
564134	-1953.08
564134	-560.84
564134	-2430.17
564134	2430.17
564134	-2385.60
564134	-1895.60
564134	2385.60
564134	1895.60
564134	1980.51
564134	-1980.51
564135	-102.90
564135	408.39
564135	-391.24
564135	-940.59
564135	-33.74
564135	-376.55
564135	391.24
564135	-359.40
564135	376.55
564135	974.34
564135	-119.49
564135	359.40
564135	119.49
564135	-1406.61
564135	1406.61
564135	-408.39
566715	-551.46
566715	551.46
566715	-82.77
566715	-1117.84
566715	1117.84
566715	-574.96
566715	574.96
566715	-72.91
566715	82.77
569078	822.69
569078	-771.24
569078	771.24
569078	-114.03
569078	-989.29
569078	989.29
569078	-822.69
572711	-121.25
578717	-3950.68
578717	3950.68
578717	-3925.21
578717	3925.21
578717	-3880.55
578717	-3863.90
578717	3880.55
578717	3863.90
578717	-3752.13
578999	-682.08
579001	-8052.43
579001	-4897.42
579001	-122.45
579001	5019.87
579001	-4897.42
579001	-5074.56
579001	8052.43
579001	5074.56
579880	-538.46
579880	-440.48
579880	538.46
579880	371.88
579880	-286.13
579880	-371.88
579880	440.48
579880	286.13
579880	-265.92
583184	-453.09
583184	453.09
583184	-440.85
583184	-612.01
583184	-440.85
583184	1052.86
584591	-299.82
584591	299.82
584591	-191.45
584591	163.03
584591	-152.10
584591	-4.87
584591	191.45
584591	-163.03
584591	-6.07
584591	-862.33
584591	873.27
584591	-58.67
584591	152.10
584591	-132.51
584591	132.51
588355	867.24
588355	-867.24
588355	-3075.29
588355	-3066.90
588355	3075.29
588355	3066.90
588355	-325.64
588355	-308.49
588355	325.64
589964	-132.30
593016	-318.36
593016	337.95
593016	-19.59
593016	-19.59
593016	-61.23
593016	61.23
593016	-41.64
593016	41.64
595187	-456.88
595395	-720.06
595395	-125.33
595395	845.40
595395	-278.75
595395	733.67
595395	-628.61
595395	-808.58
595395	808.58
595395	-733.67
595395	628.61
598662	-861.15
598662	2583.89
598662	-1722.74
598662	-44.20
598662	2534.91
598662	-1430.06
598662	-2534.91
598662	1430.06
598662	-1389.64
598662	1389.64
598662	-427.97
598662	427.97
599675	-74.38
599737	-1469.51
599737	3122.57
599737	-1440.12
599737	1469.51
599737	-3122.57
599798	-6906.35
599798	-405.54
599798	6906.35
599989	-499.80
599989	499.80
599989	-482.65
599989	-208.25
599989	482.65
600227	-6421.37
600227	-6227.39
600227	6421.37
600227	6227.39
600227	-4431.97
600545	-2020.19
600545	4203.31
600545	-2183.12
600545	-441.71
600545	-2104.33
600545	-1456.35
600545	2104.33
600545	1456.35
601490	-306.10
601490	2902.75
601490	-2596.65
601490	-195.90
601490	-404.05
601490	404.05
601497	1868.15
601497	-1791.74
601497	33666.72
601497	-1786.23
601497	-31880.49
601497	-1942.30
601497	-1868.15
601497	1942.30
601621	1623.81
601621	-1408.83
601621	-714.42
601621	2123.25
601621	-177.41
601621	-1346.89
601621	-1623.81
601621	1346.89
601621	-1248.94
601621	-1109.78
601621	1248.94
601621	-1095.57
601621	1109.78
601621	1095.57
602985	-1468.38
602985	-2934.49
602985	-35525.19
602985	41919.55
602985	-1104.85
602985	-10.20
602985	41032.92
602985	-40968.19
602985	-41919.55
602985	-40065.37
602985	40968.19
602985	-3424.12
602985	40065.37
602985	3424.12
602985	-2607.98
602985	2618.18
602985	-1628.47
602993	-5170.40
602993	-78.35
602993	19809.24
602993	-14638.84
603071	-573.01
603071	-898.42
603071	-2169.13
603071	-11427.06
603071	22050.00
603071	-6492.37
603071	-490.00
603071	-21070.00
603071	-21021.00
603071	21070.00
609243	-19327.07
609243	-7996.41
609243	27323.48
609243	-350.30
609243	-560.48
609500	-3075.48
609500	4177.98
609500	-1102.50
609500	-593.07
609500	-2854.60
609500	-1020.66
609500	2854.60
609500	1020.66
609502	-1329.08
609502	1329.08
609502	-966.67
609502	-819.72
609502	966.67
609502	819.72
609502	-301.24
609582	-3579.82
609582	3579.82
609582	-2765.13
609582	2765.13
609582	-935.88
609582	935.88
609582	-920.46
610094	-366.47
610094	-318.45
610094	-1019.72
610094	1338.17
610094	-117.54
610094	366.47
610094	-276.42
610094	276.42
610181	-105.78
610184	-1130.90
610184	2442.30
610184	-1311.40
610184	-540.72
610184	-1339.80
610184	1339.80
614215	-2460.94
614215	-2153.04
614215	2460.94
614215	-281.65
614215	2153.04
618742	2196.45
618742	-2142.87
618742	-624.84
618742	2767.71
618742	-2404.89
618742	-2196.45
618742	-2164.92
618742	2164.92
618742	-2699.69
618742	2699.69
618742	-2655.59
618742	2655.59
618742	-2476.30
618742	2476.30
619849	-13938.56
619849	-10684.45
619849	13938.56
619849	-7227.01
619849	10684.45
622356	-966.07
622356	966.07
622356	-707.36
622356	-675.52
622356	707.36
623101	-207.54
623101	207.54
623101	2079.12
623101	-1899.84
623101	-2079.12
623101	1899.84
623101	-1800.66
623151	-88.15
623151	165.99
623151	-156.20
623151	-9.79
624299	-20632.45
624299	20632.45
624299	-987.05
625702	-1680.03
625702	-1190.23
625702	-1469.31
625702	-1591.86
625702	2659.54
625702	-1229.41
625702	1229.41
625702	1680.03
628892	1527.00
628892	1218.30
628892	-1527.00
628892	-1218.30
628892	-1086.58
628892	1086.58
628892	-902.40
628892	-99.40
628892	902.40
628972	3169.06
628972	-115.34
628972	-939.80
628972	-2113.92
628972	-595.35
628972	-609.55
628972	609.55
630194	321.19
630194	-110.25
630194	266.95
630194	312.37
630194	-321.19
630194	-312.37
630194	-266.95
638653	-293.95
639009	1471.91
639009	-1471.91
639009	-1449.38
639009	-685.22
639009	1449.38
639009	-31.84
639009	685.22
639783	-700.60
639786	-420.36
639926	-786.27
639926	786.27
639926	-770.67
640620	-122.45
644443	-620.49
644443	-529.11
644443	620.49
644749	-538.02
644749	538.02
644749	-269.01
645001	-685.93
646816	-553.01
646816	553.01
646816	-29.11
646817	-145.53
646961	-272.87
647073	-19.59
647073	-764.28
647073	783.87
647073	-700.60
647073	-764.28
647073	764.28
649107	-265.42
649126	-48.95
650083	-52.00
650083	-19.59
650083	-96.48
650086	3916.08
650086	-3867.13
650086	-3916.08
650091	-420.32
651377	4681.98
651377	-3917.66
651377	3917.66
651377	-2780.34
651377	2780.34
651377	-2180.57
651377	-1474.97
651377	2180.57
651377	-4681.98

Joe4 · Jul 8, 2022

You still have not answered my questions.
These are very important details I need to know in order to be able to help you, so please answer them!

The data that you posted above, what is the exact range address that they appear in (it should include column and row numbers)?
What is the exact Conditional Formatting formula you are trying to use (after you made edits to my original one)?

If you do that, I should be able to give you step-by-step instructions on exactly what to do.

Joe4 · Jul 8, 2022

Also note, there is a minor error to my original formula I was using C2 as a temporary calculation field, and forgot to replace it with the real formula.
It should read:

Excel Formula:

=AND($B2=SUMIF($A$2:$A$11,$A2,$B$2:$B$11),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,SUMIF($A$2:$A$11,$A2,$B$2:$B$11))=1)

Rambu · Jul 8, 2022

Joe4 said:
Also note, there is a minor error to my original formula I was using C2 as a temporary calculation field, and forgot to replace it with the real formula.
It should read:

Excel Formula:

=AND($B2=SUMIF($A$2:$A$11,$A2,$B$2:$B$11),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,SUMIF($A$2:$A$11,$A2,$B$2:$B$11))=1)

Above code is also not matching exact figure . Below is the given mini sheet data

Need formula.xlsx

A

B

1

Original Invoice

Amount Paid

2

530279

280.38

3

530279

-146.95

4

530279

146.95

5

530279

-117.56

6

530279

-280.38

7

532224

-281.31

8

532224

387.09

9

532224

-387.09

10

532376

-152.82

11

535274

-594.85

12

535274

594.85

13

535274

-187.42

14

546227

-298.13

15

547390

-237.85

16

547390

237.85

17

547390

-139.89

18

547390

-112.65

19

547390

139.89

20

547634

-276.86

21

547634

-225.92

22

547634

276.86

23

547634

-200.45

24

547634

225.92

25

547915

-533.73

26

548216

428.18

27

548216

-428.18

28

548216

-384.08

29

548216

-318.37

30

548216

384.08

31

556381

-152.82

32

558357

-156.72

33

559299

-2750.48

34

559299

2750.48

35

559299

-2691.39

36

559299

2691.39

37

559299

-1234.27

38

559299

-1212.22

39

559299

1234.27

40

559518

-2250.87

41

559518

2250.87

42

559518

-2040.87

43

559518

2040.87

44

559518

-499.89

45

559518

854.72

46

559518

-854.72

47

559520

-17934.30

48

559520

-794.90

49

559520

-794.90

50

559520

13394.22

51

559520

-9085.76

52

559520

-3513.56

53

559520

17934.30

54

559520

-17728.30

55

559520

17728.30

56

559520

13724.97

57

559520

-13928.81

58

559520

-13724.97

59

559520

13928.81

60

559737

-1128.47

61

559737

1128.47

62

559737

-1104.46

63

559786

-2631.15

64

559786

2631.15

65

559786

-2156.00

66

560995

1042.53

67

560995

-1042.53

68

560995

-879.07

69

560995

879.07

70

560995

-566.68

71

560995

566.68

72

560995

-509.36

73

561457

-1457.45

74

561457

1590.98

75

561457

-4233.94

76

561457

-597.17

77

561457

-431.19

78

561457

-366.83

79

561457

4600.78

80

561457

1457.45

81

561457

-1590.98

82

561649

-1674.68

83

561649

1694.26

84

561649

-62.68

85

561649

-768.64

86

561649

-940.54

87

561649

1674.68

88

561649

-903.87

89

561649

1844.41

90

561649

-2027.78

91

561649

2027.78

92

561649

-1694.26

93

562169

-115.10

94

562169

124.66

95

562169

115.10

96

562169

-75.92

97

562169

-148.67

98

562169

-124.66

99

562169

148.67

100

562169

75.92

101

562169

-58.77

102

562301

881.71

103

562301

-881.71

104

562301

-881.71

105

563622

-319.37

106

563622

348.75

107

563622

-117.52

108

563622

319.37

109

563622

-105.81

110

563622

422.21

111

563622

-422.21

112

563622

-348.75

113

564134

2513.93

114

564134

-1848.77

115

564134

-1953.08

116

564134

-560.84

117

564134

-2430.17

118

564134

2430.17

119

564134

-2385.60

120

564134

-1895.60

121

564134

2385.60

122

564134

1895.60

123

564134

1980.51

124

564134

-1980.51

125

564135

-102.90

126

564135

408.39

127

564135

-391.24

128

564135

-940.59

129

564135

-33.74

130

564135

-376.55

131

564135

391.24

132

564135

-359.40

133

564135

376.55

134

564135

974.34

135

564135

-119.49

136

564135

359.40

137

564135

119.49

138

564135

-1406.61

139

564135

1406.61

140

564135

-408.39

141

566715

-551.46

142

566715

551.46

143

566715

-82.77

144

566715

-1117.84

145

566715

1117.84

146

566715

-574.96

147

566715

574.96

148

566715

-72.91

149

566715

82.77

150

569078

822.69

151

569078

-771.24

152

569078

771.24

153

569078

-114.03

154

569078

-989.29

155

569078

989.29

156

569078

-822.69

157

572711

-121.25

158

578717

-3950.68

159

578717

3950.68

160

578717

-3925.21

161

578717

3925.21

162

578717

-3880.55

163

578717

-3863.90

164

578717

3880.55

165

578717

3863.90

166

578717

-3752.13

167

578999

-682.08

168

579001

-8052.43

169

579001

-4897.42

170

579001

-122.45

171

579001

5019.87

172

579001

-4897.42

173

579001

-5074.56

174

579001

8052.43

175

579001

5074.56

176

579880

-538.46

177

579880

-440.48

178

579880

538.46

179

579880

371.88

180

579880

-286.13

181

579880

-371.88

182

579880

440.48

183

579880

286.13

184

579880

-265.92

185

583184

-453.09

186

583184

453.09

187

583184

-440.85

188

583184

-612.01

189

583184

-440.85

190

583184

1052.86

191

584591

-299.82

192

584591

299.82

193

584591

-191.45

194

584591

163.03

195

584591

-152.10

196

584591

-4.87

197

584591

191.45

198

584591

-163.03

199

584591

-6.07

200

584591

-862.33

201

584591

873.27

202

584591

-58.67

203

584591

152.10

204

584591

-132.51

205

584591

132.51

206

588355

867.24

207

588355

-867.24

208

588355

-3075.29

209

588355

-3066.90

210

588355

3075.29

211

588355

3066.90

212

588355

-325.64

213

588355

-308.49

214

588355

325.64

215

589964

-132.30

216

593016

-318.36

217

593016

337.95

218

593016

-19.59

219

593016

-19.59

220

593016

-61.23

221

593016

61.23

222

593016

-41.64

223

593016

41.64

224

595187

-456.88

225

595395

-720.06

226

595395

-125.33

227

595395

845.40

228

595395

-278.75

229

595395

733.67

230

595395

-628.61

231

595395

-808.58

232

595395

808.58

233

595395

-733.67

234

595395

628.61

235

598662

-861.15

236

598662

2583.89

237

598662

-1722.74

238

598662

-44.20

239

598662

2534.91

240

598662

-1430.06

241

598662

-2534.91

242

598662

1430.06

243

598662

-1389.64

244

598662

1389.64

245

598662

-427.97

246

598662

427.97

247

599675

-74.38

248

599737

-1469.51

249

599737

3122.57

250

599737

-1440.12

251

599737

1469.51

252

599737

-3122.57

253

599798

-6906.35

254

599798

-405.54

255

599798

6906.35

256

599989

-499.80

257

599989

499.80

258

599989

-482.65

259

599989

-208.25

260

599989

482.65

261

600227

-6421.37

262

600227

-6227.39

263

600227

6421.37

264

600227

6227.39

265

600227

-4431.97

266

600545

-2020.19

267

600545

4203.31

268

600545

-2183.12

269

600545

-441.71

270

600545

-2104.33

271

600545

-1456.35

272

600545

2104.33

273

600545

1456.35

274

601490

-306.10

275

601490

2902.75

276

601490

-2596.65

277

601490

-195.90

278

601490

-404.05

279

601490

404.05

280

601497

1868.15

281

601497

-1791.74

282

601497

33666.72

283

601497

-1786.23

284

601497

-31880.49

285

601497

-1942.30

286

601497

-1868.15

287

601497

1942.30

288

601621

1623.81

289

601621

-1408.83

290

601621

-714.42

291

601621

2123.25

292

601621

-177.41

293

601621

-1346.89

294

601621

-1623.81

295

601621

1346.89

296

601621

-1248.94

297

601621

-1109.78

298

601621

1248.94

299

601621

-1095.57

300

601621

1109.78

301

601621

1095.57

302

602985

-1468.38

303

602985

-2934.49

304

602985

-35525.19

305

602985

41919.55

306

602985

-1104.85

307

602985

-10.20

308

602985

41032.92

309

602985

-40968.19

310

602985

-41919.55

311

602985

-40065.37

312

602985

40968.19

313

602985

-3424.12

314

602985

40065.37

315

602985

3424.12

316

602985

-2607.98

317

602985

2618.18

318

602985

-1628.47

319

602993

-5170.40

320

602993

-78.35

321

602993

19809.24

322

602993

-14638.84

323

603071

-573.01

324

603071

-898.42

325

603071

-2169.13

326

603071

-11427.06

327

603071

22050.00

328

603071

-6492.37

329

603071

-490.00

330

603071

-21070.00

331

603071

-21021.00

332

603071

21070.00

333

609243

-19327.07

334

609243

-7996.41

335

609243

27323.48

336

609243

-350.30

337

609243

-560.48

338

609500

-3075.48

339

609500

4177.98

340

609500

-1102.50

341

609500

-593.07

342

609500

-2854.60

343

609500

-1020.66

344

609500

2854.60

345

609500

1020.66

346

609502

-1329.08

347

609502

1329.08

348

609502

-966.67

349

609502

-819.72

350

609502

966.67

351

609502

819.72

352

609502

-301.24

353

609582

-3579.82

354

609582

3579.82

355

609582

-2765.13

356

609582

2765.13

357

609582

-935.88

358

609582

935.88

359

609582

-920.46

360

610094

-366.47

361

610094

-318.45

362

610094

-1019.72

363

610094

1338.17

364

610094

-117.54

365

610094

366.47

366

610094

-276.42

367

610094

276.42

368

610181

-105.78

369

610184

-1130.90

370

610184

2442.30

371

610184

-1311.40

372

610184

-540.72

373

610184

-1339.80

374

610184

1339.80

375

614215

-2460.94

376

614215

-2153.04

377

614215

2460.94

378

614215

-281.65

379

614215

2153.04

380

618742

2196.45

381

618742

-2142.87

382

618742

-624.84

383

618742

2767.71

384

618742

-2404.89

385

618742

-2196.45

386

618742

-2164.92

387

618742

2164.92

388

618742

-2699.69

389

618742

2699.69

390

618742

-2655.59

391

618742

2655.59

392

618742

-2476.30

393

618742

2476.30

394

619849

-13938.56

395

619849

-10684.45

396

619849

13938.56

397

619849

-7227.01

398

619849

10684.45

399

622356

-966.07

400

622356

966.07

401

622356

-707.36

402

622356

-675.52

403

622356

707.36

404

623101

-207.54

405

623101

207.54

406

623101

2079.12

407

623101

-1899.84

408

623101

-2079.12

409

623101

1899.84

410

623101

-1800.66

411

623151

-88.15

412

623151

165.99

413

623151

-156.20

414

623151

-9.79

415

624299

-20632.45

416

624299

20632.45

417

624299

-987.05

418

625702

-1680.03

419

625702

-1190.23

420

625702

-1469.31

421

625702

-1591.86

422

625702

2659.54

423

625702

-1229.41

424

625702

1229.41

425

625702

1680.03

426

628892

1527.00

427

628892

1218.30

428

628892

-1527.00

429

628892

-1218.30

430

628892

-1086.58

431

628892

1086.58

432

628892

-902.40

433

628892

-99.40

434

628892

902.40

435

628972

3169.06

436

628972

-115.34

437

628972

-939.80

438

628972

-2113.92

439

628972

-595.35

440

628972

-609.55

441

628972

609.55

442

630194

321.19

443

630194

-110.25

444

630194

266.95

445

630194

312.37

446

630194

-321.19

447

630194

-312.37

448

630194

-266.95

449

638653

-293.95

450

639009

1471.91

451

639009

-1471.91

452

639009

-1449.38

453

639009

-685.22

454

639009

1449.38

455

639009

-31.84

456

639009

685.22

457

639783

-700.60

458

639786

-420.36

459

639926

-786.27

460

639926

786.27

461

639926

-770.67

462

640620

-122.45

463

644443

-620.49

464

644443

-529.11

465

644443

620.49

466

644749

-538.02

467

644749

538.02

468

644749

-269.01

469

645001

-685.93

470

646816

-553.01

471

646816

553.01

472

646816

-29.11

473

646817

-145.53

474

646961

-272.87

475

647073

-19.59

476

647073

-764.28

477

647073

783.87

478

647073

-700.60

479

647073

-764.28

480

647073

764.28

481

649107

-265.42

482

649126

-48.95

483

650083

-52.00

484

650083

-19.59

485

650083

-96.48

486

650086

3916.08

487

650086

-3867.13

488

650086

-3916.08

489

650091

-420.32

490

651377

4681.98

491

651377

-3917.66

492

651377

3917.66

493

651377

-2780.34

494

651377

2780.34

495

651377

-2180.57

496

651377

-1474.97

497

651377

2180.57

498

651377

-4681.98

499

651533

-208.62

500

651533

-85.68

501

651533

264.86

502

651533

-135.18

503

651533

-179.18

504

651533

208.62

505

652322

-104.66

506

652842

-132.30

507

654647

-1028.90

508

655625

-4235.61

509

655625

-670.32

510

655625

4905.93

511

655625

-3427.76

512

655625

-4779.16

513

655625

4779.16

514

655625

-4551.91

515

655625

4551.91

516

656185

-6253.08

517

656185

6253.08

518

656185

-6228.59

519

656185

6228.59

520

656185

-1584.17

521

656390

-489.51

522

656390

1292.31

523

656390

-802.80

524

656390

-793.01

525

656390

-1086.71

526

656390

1086.71

527

656390

-802.80

528

656390

802.80

529

658817

-731.00

530

658817

913.75

531

658817

-913.75

532

658829

-560.48

533

658895

-290.94

534

658961

-290.74

535

658961

-352.41

536

658961

352.41

537

659201

-716.01

538

659369

-365.09

539

659369

-262.71

540

659369

365.09

541

659864

-1443.99

542

659864

-1427.45

543

659864

1443.99

544

659918

-24548.90

545

659918

24548.90

546

659918

-154.35

547

659944

-127.36

548

659944

-364.32

549

659944

491.69

550

659944

-127.36

551

661499

-350.30

552

661503

-497.01

553

661503

-511.21

554

661503

-24.01

555

661503

535.22

556

661503

-440.21

557

661503

497.01

558

661854

-3370.00

559

661854

3370.00

560

661854

-2459.19

561

661855

-2754.68

562

661855

2754.68

563

661855

-1042.13

564

661855

-1028.90

565

661855

1042.13

566

662374

-1397.96

567

662374

-1381.80

568

662374

1397.96

569

662376

-370.03

570

664795

-553.81

571

664798

-1740.27

572

664798

1740.27

573

664798

-1639.47

574

664798

-78.75

575

665123

-658.43

576

665123

658.43

577

665123

-490.42

578

665130

-244.90

579

665130

-44.08

580

665130

288.98

581

665130

-171.43

582

665176

-1870.49

583

665176

-727.64

584

665176

1870.49

585

665176

727.64

586

665176

-608.23

587

665379

-1033.80

588

665379

1033.80

589

665379

-913.75

590

666410

-2823.52

591

666410

-1457.30

592

666410

4280.82

593

666410

-2459.19

594

667232

-857.49

595

667232

857.49

596

667232

-97.99

597

667232

-783.92

598

667410

-465.84

599

667410

-110.25

600

667410

-432.12

601

668053

-1714.31

602

668053

-734.71

603

668053

1714.31

604

668147

-3212.79

605

668147

3212.79

606

668147

-2415.30

607

668147

-738.72

608

668404

-404.13

609

668404

-1065.38

610

668483

-42.12

611

668483

-22.53

612

668483

42.12

613

668705

-427.10

614

668705

-84.13

615

668705

-146.90

616

668705

511.23

617

670000

-205.75

618

670000

205.75

619

670000

-183.70

620

670000

-95.50

621

670000

183.70

622

670139

-919.95

623

670139

-753.40

624

670139

919.95

625

670139

753.40

626

670139

-312.40

627

671664

-1657.09

628

671664

1657.09

629

671664

-1505.19

630

671664

-1049.49

631

671664

1505.19

632

671687

-2277.03

633

671687

2277.03

634

671687

-910.81

635

671871

-910.81

636

671880

-1959.80

637

672089

-1181.88

638

672089

1181.88

639

672089

-679.58

640

672089

-650.03

641

672089

679.58

642

672091

-3684.69

643

672091

-2771.07

644

672091

3684.69

645

672091

-846.01

646

672093

-696.01

647

672093

696.01

648

672093

-687.20

649

672098

-3121.30

650

672098

3121.30

651

672098

-267.69

652

672098

-1773.16

653

672103

-465.42

654

672103

465.42

655

672103

-218.70

656

672438

-10.84

657

672438

-699.17

658

672706

-1514.09

659

672706

-24.01

660

672706

1538.10

661

672706

-1090.00

662

672706

-1246.72

663

672706

1246.72

664

672708

-1959.63

665

672709

-2489.74

666

672709

-2440.79

667

672709

2489.74

668

672709

-1872.78

669

672709

2440.79

670

672853

-2341.61

671

672853

-648.45

672

673008

-366.74

673

673069

-583.05

674

673069

-116.61

675

673069

583.05

676

673365

-4986.45

677

673365

4986.45

678

673365

-4212.82

679

673365

-4198.62

680

673365

4212.82

681

673365

4198.62

682

673365

-1294.48

683

673365

-1053.08

684

673365

1294.48

685

673474

-2086.78

686

673474

2086.78

687

673474

-2047.59

688

673474

-558.41

689

673474

2047.59

690

673871

-136.70

691

673871

-1093.40

692

673871

1230.10

693

673871

-112.22

694

673871

-1095.45

695

673871

1095.45

696

673871

-1046.49

697

673871

1046.49

698

673995

-245.20

699

674326

-1401.20

700

674326

1401.20

701

674326

-700.60

702

674333

-10677.42

703

674333

10677.42

704

674333

-10599.05

705

674333

10599.05

706

674333

-10158.20

707

674333

10158.20

708

674333

-3741.73

709

674334

-4285.50

710

674334

4285.50

711

674334

-4112.15

712

674334

-4046.00

713

674334

4112.15

714

674334

-1836.76

715

674334

4046.00

716

674336

-31125.65

717

674336

-50138.79

718

674336

-6641.43

719

674336

87905.87

720

674336

-7694.33

721

674336

-87621.81

722

674336

87621.81

723

674336

-87592.42

724

674336

-16647.52

725

674336

87592.42

726

674336

16647.52

727

674337

-8326.51

728

674337

-685.66

729

674337

9012.17

730

674337

-2154.92

731

674337

-8914.22

732

674337

8914.22

733

674339

-23078.04

734

674339

-23058.45

735

674339

-3722.43

736

674339

26780.88

737

674339

-16671.26

738

674339

23078.04

739

674339

-26663.34

740

674339

26663.34

741

674340

-17118.09

742

674340

-15722.03

743

674340

-514.50

744

674340

-13194.53

745

674340

29431.07

746

674340

-26311.32

747

674340

17118.09

748

674340

-29293.93

749

674340

29293.93

750

674340

-29269.44

751

674340

29269.44

752

674341

-16650.69

753

674341

-1778.62

754

674341

-532.80

755

674341

-2920.82

756

674341

-19909.02

757

674341

21882.93

758

674560

-98.09

759

674560

98.09

760

674560

-67.23

761

675052

-134.64

762

675052

-97.92

763

675052

134.64

764

675053

-1038.16

765

675053

1038.16

766

675053

-176.30

767

675858

-6073.10

768

675858

-1579.46

769

675858

7652.56

770

675858

-3359.46

771

675858

-7454.23

772

675858

7454.23

773

675858

-7441.99

774

675858

-7368.50

775

675858

7441.99

776

675858

7368.50

777

675859

-5151.68

778

675859

-2816.29

779

675859

7967.98

780

675859

-7467.32

781

675859

-299.57

782

675859

7766.89

783

675859

-2164.16

784

675859

-7654.82

785

675859

7654.82

786

675860

-3257.17

787

675860

-3080.86

788

675860

-2578.80

789

675860

5659.66

790

675860

-1537.99

791

675860

3257.17

792

675860

-5469.06

793

675860

5469.06

794

675860

-1557.58

795

675860

1557.58

796

675979

-901.15

797

676061

-14134.11

798

676061

-2025.63

799

676061

-934.29

800

676061

17094.03

801

676061

-1137.78

802

676061

-16981.91

803

676061

-16935.37

804

676061

16981.91

805

676061

16935.37

806

676061

-16910.88

807

676061

16910.88

808

676070

-979.51

809

676075

-225.82

810

676075

225.82

811

676075

-193.98

812

676162

-1025.90

813

676162

1025.90

814

676162

-910.81

815

676163

-2882.16

816

676163

-2492.62

817

676163

2882.16

818

676163

2492.62

819

676163

-1177.04

820

676164

-177.75

821

676164

-97.90

822

676164

177.75

823

676201

-1107.62

824

676398

-1561.14

825

676479

-46.53

826

676479

-299.69

827

676479

346.22

828

676479

-327.16

829

676479

-572.07

830

676479

-276.74

831

676479

603.91

832

676479

572.07

833

676479

-515.26

834

676480

-2528.39

835

676480

2528.39

836

676480

-1906.89

837

676480

1906.89

838

676480

-1613.04

839

676480

1613.04

840

676480

-1469.51

841

676594

-2781.35

842

676594

-19.59

843

676594

2800.94

844

676594

-382.07

845

676594

-2575.65

846

676594

2575.65

847

676717

-2045.11

848

676717

2045.11

849

676717

-842.53

850

676717

-78.36

851

676717

842.53

852

676856

-39.15

853

676856

39.15

854

676856

-7.83

855

676979

-489.75

856

677072

-755.56

857

677072

755.56

858

677072

-314.85

859

677073

-860.23

860

677073

-52.23

861

677073

912.46

862

677073

-66.09

863

677073

-195.98

864

677073

195.98

865

677074

-369.48

866

677077

-475.59

867

677077

-453.95

868

677077

475.59

869

677077

-587.61

870

677077

1041.55

871

677077

-703.70

872

677097

-220.69

873

677102

-1982.04

874

677102

-1940.01

875

677102

1982.04

876

677102

-455.41

877

677102

1940.01

878

677112

-183.65

879

677189

-2838.46

880

677189

-22.05

881

677189

2860.51

882

677189

-2742.92

883

677189

-2831.12

884

677189

2831.12

885

677334

-326.22

886

677500

-3065.01

887

677500

-3035.62

888

677500

-156.76

889

677500

3192.38

890

677500

-48.98

891

677500

3065.01

892

677500

-3114.51

893

677500

3114.51

894

677502

-2631.80

895

677502

-2053.55

896

677502

4685.35

897

677502

-1205.00

898

677502

-3886.65

899

677502

-3820.50

900

677502

3886.65

901

677502

3820.50

902

677502

-3636.79

903

677502

3636.79

904

677502

-1375.40

905

677502

1375.40

906

677506

-1532.93

907

677506

-2327.48

908

677506

-19.59

909

677506

2347.07

910

677506

-504.33

911

677506

1532.93

912

677506

-1385.97

913

677506

1385.97

914

678084

-1508.66

915

678084

-39.15

916

678084

1508.66

917

678224

-25066.49

918

678224

25066.49

919

678224

-24512.13

920

678224

24512.13

921

678224

-1315.40

922

678224

-1273.36

923

678224

1315.40

924

678225

-2151.29

925

678225

2151.29

926

678225

-489.75

927

678226

-1820.78

928

678226

1820.78

929

678226

-587.71

930

678361

-770.67

931

678382

-242.55

932

678560

-2621.84

933

678560

-134.65

934

678560

-262.11

935

678560

-514.05

936

678560

3532.65

937

678560

-1049.49

938

678560

-3378.52

939

678560

3378.52

940

678560

-3279.58

941

678560

3279.58

942

678587

-51885.93

943

678587

51885.93

944

678587

-3145.93

945

678587

3145.93

946

678587

-132.01

947

679551

-848.29

948

679551

862.46

949

679551

-14.17

950

679551

-391.69

951

679551

-776.78

952

679551

-746.89

953

679551

776.78

954

679551

-734.65

955

679551

746.89

956

679551

734.65

957

679615

-1979.97

958

679615

1979.97

959

679615

-1950.58

960

679615

1950.58

961

679615

-1815.88

962

679615

-1742.43

963

679615

1815.88

964

680903

-1929.83

965

680903

1929.83

966

680903

-1905.34

967

680903

-636.69

968

680903

1905.34

969

680903

636.69

970

680903

-352.68

971

680906

-342.86

972

680906

-293.88

973

680906

342.86

974

680957

-146.95

975

681281

-372.21

976

681281

-313.44

977

681281

372.21

978

681590

-8070.35

979

681590

8070.35

980

681590

-7658.79

981

681590

-6351.59

982

681590

7658.79

983

681590

6351.59

984

681590

-4526.09

985

681937

-79.64

986

681937

-31.32

987

681937

-75.43

988

681937

-533.87

989

681937

720.27

990

681937

-661.50

991

682255

-1283.03

992

682255

1283.03

993

682255

-1220.38

994

682255

1220.38

995

682255

-140.94

996

685121

-764.02

997

685303

-979.88

998

685303

-531.65

999

685303

-2281.11

1000

685303

-1468.92

1001

685303

1511.53

1002

685303

2281.11

1003

685304

-6905.60

1004

685304

-5438.85

1005

685304

6905.60

1006

685304

-8862.56

1007

685304

-293.95

1008

685304

-4575.44

1009

685304

-168.93

1010

685304

13900.89

1011

685304

-253.66

1012

685304

-2944.96

1013

685304

5438.85

1014

685304

2944.96

Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B1727	Expression	=AND($B2=SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727))=1)	text	NO

Rambu · Jul 8, 2022

Rambu said:
Above code is also not matching exact figure . Below is the given mini sheet data

Need formula.xlsx
A B
1 Original Invoice Amount Paid
2 530279 280.38
3 530279 -146.95
4 530279 146.95
5 530279 -117.56
6 530279 -280.38
7 532224 -281.31
8 532224 387.09
9 532224 -387.09
10 532376 -152.82
11 535274 -594.85
12 535274 594.85
13 535274 -187.42
14 546227 -298.13
15 547390 -237.85
16 547390 237.85
17 547390 -139.89
18 547390 -112.65
19 547390 139.89
20 547634 -276.86
21 547634 -225.92
22 547634 276.86
23 547634 -200.45
24 547634 225.92
25 547915 -533.73
26 548216 428.18
27 548216 -428.18
28 548216 -384.08
29 548216 -318.37
30 548216 384.08
31 556381 -152.82
32 558357 -156.72
33 559299 -2750.48
34 559299 2750.48
35 559299 -2691.39
36 559299 2691.39
37 559299 -1234.27
38 559299 -1212.22
39 559299 1234.27
40 559518 -2250.87
41 559518 2250.87
42 559518 -2040.87
43 559518 2040.87
44 559518 -499.89
45 559518 854.72
46 559518 -854.72
47 559520 -17934.30
48 559520 -794.90
49 559520 -794.90
50 559520 13394.22
51 559520 -9085.76
52 559520 -3513.56
53 559520 17934.30
54 559520 -17728.30
55 559520 17728.30
56 559520 13724.97
57 559520 -13928.81
58 559520 -13724.97
59 559520 13928.81
60 559737 -1128.47
61 559737 1128.47
62 559737 -1104.46
63 559786 -2631.15
64 559786 2631.15
65 559786 -2156.00
66 560995 1042.53
67 560995 -1042.53
68 560995 -879.07
69 560995 879.07
70 560995 -566.68
71 560995 566.68
72 560995 -509.36
73 561457 -1457.45
74 561457 1590.98
75 561457 -4233.94
76 561457 -597.17
77 561457 -431.19
78 561457 -366.83
79 561457 4600.78
80 561457 1457.45
81 561457 -1590.98
82 561649 -1674.68
83 561649 1694.26
84 561649 -62.68
85 561649 -768.64
86 561649 -940.54
87 561649 1674.68
88 561649 -903.87
89 561649 1844.41
90 561649 -2027.78
91 561649 2027.78
92 561649 -1694.26
93 562169 -115.10
94 562169 124.66
95 562169 115.10
96 562169 -75.92
97 562169 -148.67
98 562169 -124.66
99 562169 148.67
100 562169 75.92
101 562169 -58.77
102 562301 881.71
103 562301 -881.71
104 562301 -881.71
105 563622 -319.37
106 563622 348.75
107 563622 -117.52
108 563622 319.37
109 563622 -105.81
110 563622 422.21
111 563622 -422.21
112 563622 -348.75
113 564134 2513.93
114 564134 -1848.77
115 564134 -1953.08
116 564134 -560.84
117 564134 -2430.17
118 564134 2430.17
119 564134 -2385.60
120 564134 -1895.60
121 564134 2385.60
122 564134 1895.60
123 564134 1980.51
124 564134 -1980.51
125 564135 -102.90
126 564135 408.39
127 564135 -391.24
128 564135 -940.59
129 564135 -33.74
130 564135 -376.55
131 564135 391.24
132 564135 -359.40
133 564135 376.55
134 564135 974.34
135 564135 -119.49
136 564135 359.40
137 564135 119.49
138 564135 -1406.61
139 564135 1406.61
140 564135 -408.39
141 566715 -551.46
142 566715 551.46
143 566715 -82.77
144 566715 -1117.84
145 566715 1117.84
146 566715 -574.96
147 566715 574.96
148 566715 -72.91
149 566715 82.77
150 569078 822.69
151 569078 -771.24
152 569078 771.24
153 569078 -114.03
154 569078 -989.29
155 569078 989.29
156 569078 -822.69
157 572711 -121.25
158 578717 -3950.68
159 578717 3950.68
160 578717 -3925.21
161 578717 3925.21
162 578717 -3880.55
163 578717 -3863.90
164 578717 3880.55
165 578717 3863.90
166 578717 -3752.13
167 578999 -682.08
168 579001 -8052.43
169 579001 -4897.42
170 579001 -122.45
171 579001 5019.87
172 579001 -4897.42
173 579001 -5074.56
174 579001 8052.43
175 579001 5074.56
176 579880 -538.46
177 579880 -440.48
178 579880 538.46
179 579880 371.88
180 579880 -286.13
181 579880 -371.88
182 579880 440.48
183 579880 286.13
184 579880 -265.92
185 583184 -453.09
186 583184 453.09
187 583184 -440.85
188 583184 -612.01
189 583184 -440.85
190 583184 1052.86
191 584591 -299.82
192 584591 299.82
193 584591 -191.45
194 584591 163.03
195 584591 -152.10
196 584591 -4.87
197 584591 191.45
198 584591 -163.03
199 584591 -6.07
200 584591 -862.33
201 584591 873.27
202 584591 -58.67
203 584591 152.10
204 584591 -132.51
205 584591 132.51
206 588355 867.24
207 588355 -867.24
208 588355 -3075.29
209 588355 -3066.90
210 588355 3075.29
211 588355 3066.90
212 588355 -325.64
213 588355 -308.49
214 588355 325.64
215 589964 -132.30
216 593016 -318.36
217 593016 337.95
218 593016 -19.59
219 593016 -19.59
220 593016 -61.23
221 593016 61.23
222 593016 -41.64
223 593016 41.64
224 595187 -456.88
225 595395 -720.06
226 595395 -125.33
227 595395 845.40
228 595395 -278.75
229 595395 733.67
230 595395 -628.61
231 595395 -808.58
232 595395 808.58
233 595395 -733.67
234 595395 628.61
235 598662 -861.15
236 598662 2583.89
237 598662 -1722.74
238 598662 -44.20
239 598662 2534.91
240 598662 -1430.06
241 598662 -2534.91
242 598662 1430.06
243 598662 -1389.64
244 598662 1389.64
245 598662 -427.97
246 598662 427.97
247 599675 -74.38
248 599737 -1469.51
249 599737 3122.57
250 599737 -1440.12
251 599737 1469.51
252 599737 -3122.57
253 599798 -6906.35
254 599798 -405.54
255 599798 6906.35
256 599989 -499.80
257 599989 499.80
258 599989 -482.65
259 599989 -208.25
260 599989 482.65
261 600227 -6421.37
262 600227 -6227.39
263 600227 6421.37
264 600227 6227.39
265 600227 -4431.97
266 600545 -2020.19
267 600545 4203.31
268 600545 -2183.12
269 600545 -441.71
270 600545 -2104.33
271 600545 -1456.35
272 600545 2104.33
273 600545 1456.35
274 601490 -306.10
275 601490 2902.75
276 601490 -2596.65
277 601490 -195.90
278 601490 -404.05
279 601490 404.05
280 601497 1868.15
281 601497 -1791.74
282 601497 33666.72
283 601497 -1786.23
284 601497 -31880.49
285 601497 -1942.30
286 601497 -1868.15
287 601497 1942.30
288 601621 1623.81
289 601621 -1408.83
290 601621 -714.42
291 601621 2123.25
292 601621 -177.41
293 601621 -1346.89
294 601621 -1623.81
295 601621 1346.89
296 601621 -1248.94
297 601621 -1109.78
298 601621 1248.94
299 601621 -1095.57
300 601621 1109.78
301 601621 1095.57
302 602985 -1468.38
303 602985 -2934.49
304 602985 -35525.19
305 602985 41919.55
306 602985 -1104.85
307 602985 -10.20
308 602985 41032.92
309 602985 -40968.19
310 602985 -41919.55
311 602985 -40065.37
312 602985 40968.19
313 602985 -3424.12
314 602985 40065.37
315 602985 3424.12
316 602985 -2607.98
317 602985 2618.18
318 602985 -1628.47
319 602993 -5170.40
320 602993 -78.35
321 602993 19809.24
322 602993 -14638.84
323 603071 -573.01
324 603071 -898.42
325 603071 -2169.13
326 603071 -11427.06
327 603071 22050.00
328 603071 -6492.37
329 603071 -490.00
330 603071 -21070.00
331 603071 -21021.00
332 603071 21070.00
333 609243 -19327.07
334 609243 -7996.41
335 609243 27323.48
336 609243 -350.30
337 609243 -560.48
338 609500 -3075.48
339 609500 4177.98
340 609500 -1102.50
341 609500 -593.07
342 609500 -2854.60
343 609500 -1020.66
344 609500 2854.60
345 609500 1020.66
346 609502 -1329.08
347 609502 1329.08
348 609502 -966.67
349 609502 -819.72
350 609502 966.67
351 609502 819.72
352 609502 -301.24
353 609582 -3579.82
354 609582 3579.82
355 609582 -2765.13
356 609582 2765.13
357 609582 -935.88
358 609582 935.88
359 609582 -920.46
360 610094 -366.47
361 610094 -318.45
362 610094 -1019.72
363 610094 1338.17
364 610094 -117.54
365 610094 366.47
366 610094 -276.42
367 610094 276.42
368 610181 -105.78
369 610184 -1130.90
370 610184 2442.30
371 610184 -1311.40
372 610184 -540.72
373 610184 -1339.80
374 610184 1339.80
375 614215 -2460.94
376 614215 -2153.04
377 614215 2460.94
378 614215 -281.65
379 614215 2153.04
380 618742 2196.45
381 618742 -2142.87
382 618742 -624.84
383 618742 2767.71
384 618742 -2404.89
385 618742 -2196.45
386 618742 -2164.92
387 618742 2164.92
388 618742 -2699.69
389 618742 2699.69
390 618742 -2655.59
391 618742 2655.59
392 618742 -2476.30
393 618742 2476.30
394 619849 -13938.56
395 619849 -10684.45
396 619849 13938.56
397 619849 -7227.01
398 619849 10684.45
399 622356 -966.07
400 622356 966.07
401 622356 -707.36
402 622356 -675.52
403 622356 707.36
404 623101 -207.54
405 623101 207.54
406 623101 2079.12
407 623101 -1899.84
408 623101 -2079.12
409 623101 1899.84
410 623101 -1800.66
411 623151 -88.15
412 623151 165.99
413 623151 -156.20
414 623151 -9.79
415 624299 -20632.45
416 624299 20632.45
417 624299 -987.05
418 625702 -1680.03
419 625702 -1190.23
420 625702 -1469.31
421 625702 -1591.86
422 625702 2659.54
423 625702 -1229.41
424 625702 1229.41
425 625702 1680.03
426 628892 1527.00
427 628892 1218.30
428 628892 -1527.00
429 628892 -1218.30
430 628892 -1086.58
431 628892 1086.58
432 628892 -902.40
433 628892 -99.40
434 628892 902.40
435 628972 3169.06
436 628972 -115.34
437 628972 -939.80
438 628972 -2113.92
439 628972 -595.35
440 628972 -609.55
441 628972 609.55
442 630194 321.19
443 630194 -110.25
444 630194 266.95
445 630194 312.37
446 630194 -321.19
447 630194 -312.37
448 630194 -266.95
449 638653 -293.95
450 639009 1471.91
451 639009 -1471.91
452 639009 -1449.38
453 639009 -685.22
454 639009 1449.38
455 639009 -31.84
456 639009 685.22
457 639783 -700.60
458 639786 -420.36
459 639926 -786.27
460 639926 786.27
461 639926 -770.67
462 640620 -122.45
463 644443 -620.49
464 644443 -529.11
465 644443 620.49
466 644749 -538.02
467 644749 538.02
468 644749 -269.01
469 645001 -685.93
470 646816 -553.01
471 646816 553.01
472 646816 -29.11
473 646817 -145.53
474 646961 -272.87
475 647073 -19.59
476 647073 -764.28
477 647073 783.87
478 647073 -700.60
479 647073 -764.28
480 647073 764.28
481 649107 -265.42
482 649126 -48.95
483 650083 -52.00
484 650083 -19.59
485 650083 -96.48
486 650086 3916.08
487 650086 -3867.13
488 650086 -3916.08
489 650091 -420.32
490 651377 4681.98
491 651377 -3917.66
492 651377 3917.66
493 651377 -2780.34
494 651377 2780.34
495 651377 -2180.57
496 651377 -1474.97
497 651377 2180.57
498 651377 -4681.98
499 651533 -208.62
500 651533 -85.68
501 651533 264.86
502 651533 -135.18
503 651533 -179.18
504 651533 208.62
505 652322 -104.66
506 652842 -132.30
507 654647 -1028.90
508 655625 -4235.61
509 655625 -670.32
510 655625 4905.93
511 655625 -3427.76
512 655625 -4779.16
513 655625 4779.16
514 655625 -4551.91
515 655625 4551.91
516 656185 -6253.08
517 656185 6253.08
518 656185 -6228.59
519 656185 6228.59
520 656185 -1584.17
521 656390 -489.51
522 656390 1292.31
523 656390 -802.80
524 656390 -793.01
525 656390 -1086.71
526 656390 1086.71
527 656390 -802.80
528 656390 802.80
529 658817 -731.00
530 658817 913.75
531 658817 -913.75
532 658829 -560.48
533 658895 -290.94
534 658961 -290.74
535 658961 -352.41
536 658961 352.41
537 659201 -716.01
538 659369 -365.09
539 659369 -262.71
540 659369 365.09
541 659864 -1443.99
542 659864 -1427.45
543 659864 1443.99
544 659918 -24548.90
545 659918 24548.90
546 659918 -154.35
547 659944 -127.36
548 659944 -364.32
549 659944 491.69
550 659944 -127.36
551 661499 -350.30
552 661503 -497.01
553 661503 -511.21
554 661503 -24.01
555 661503 535.22
556 661503 -440.21
557 661503 497.01
558 661854 -3370.00
559 661854 3370.00
560 661854 -2459.19
561 661855 -2754.68
562 661855 2754.68
563 661855 -1042.13
564 661855 -1028.90
565 661855 1042.13
566 662374 -1397.96
567 662374 -1381.80
568 662374 1397.96
569 662376 -370.03
570 664795 -553.81
571 664798 -1740.27
572 664798 1740.27
573 664798 -1639.47
574 664798 -78.75
575 665123 -658.43
576 665123 658.43
577 665123 -490.42
578 665130 -244.90
579 665130 -44.08
580 665130 288.98
581 665130 -171.43
582 665176 -1870.49
583 665176 -727.64
584 665176 1870.49
585 665176 727.64
586 665176 -608.23
587 665379 -1033.80
588 665379 1033.80
589 665379 -913.75
590 666410 -2823.52
591 666410 -1457.30
592 666410 4280.82
593 666410 -2459.19
594 667232 -857.49
595 667232 857.49
596 667232 -97.99
597 667232 -783.92
598 667410 -465.84
599 667410 -110.25
600 667410 -432.12
601 668053 -1714.31
602 668053 -734.71
603 668053 1714.31
604 668147 -3212.79
605 668147 3212.79
606 668147 -2415.30
607 668147 -738.72
608 668404 -404.13
609 668404 -1065.38
610 668483 -42.12
611 668483 -22.53
612 668483 42.12
613 668705 -427.10
614 668705 -84.13
615 668705 -146.90
616 668705 511.23
617 670000 -205.75
618 670000 205.75
619 670000 -183.70
620 670000 -95.50
621 670000 183.70
622 670139 -919.95
623 670139 -753.40
624 670139 919.95
625 670139 753.40
626 670139 -312.40
627 671664 -1657.09
628 671664 1657.09
629 671664 -1505.19
630 671664 -1049.49
631 671664 1505.19
632 671687 -2277.03
633 671687 2277.03
634 671687 -910.81
635 671871 -910.81
636 671880 -1959.80
637 672089 -1181.88
638 672089 1181.88
639 672089 -679.58
640 672089 -650.03
641 672089 679.58
642 672091 -3684.69
643 672091 -2771.07
644 672091 3684.69
645 672091 -846.01
646 672093 -696.01
647 672093 696.01
648 672093 -687.20
649 672098 -3121.30
650 672098 3121.30
651 672098 -267.69
652 672098 -1773.16
653 672103 -465.42
654 672103 465.42
655 672103 -218.70
656 672438 -10.84
657 672438 -699.17
658 672706 -1514.09
659 672706 -24.01
660 672706 1538.10
661 672706 -1090.00
662 672706 -1246.72
663 672706 1246.72
664 672708 -1959.63
665 672709 -2489.74
666 672709 -2440.79
667 672709 2489.74
668 672709 -1872.78
669 672709 2440.79
670 672853 -2341.61
671 672853 -648.45
672 673008 -366.74
673 673069 -583.05
674 673069 -116.61
675 673069 583.05
676 673365 -4986.45
677 673365 4986.45
678 673365 -4212.82
679 673365 -4198.62
680 673365 4212.82
681 673365 4198.62
682 673365 -1294.48
683 673365 -1053.08
684 673365 1294.48
685 673474 -2086.78
686 673474 2086.78
687 673474 -2047.59
688 673474 -558.41
689 673474 2047.59
690 673871 -136.70
691 673871 -1093.40
692 673871 1230.10
693 673871 -112.22
694 673871 -1095.45
695 673871 1095.45
696 673871 -1046.49
697 673871 1046.49
698 673995 -245.20
699 674326 -1401.20
700 674326 1401.20
701 674326 -700.60
702 674333 -10677.42
703 674333 10677.42
704 674333 -10599.05
705 674333 10599.05
706 674333 -10158.20
707 674333 10158.20
708 674333 -3741.73
709 674334 -4285.50
710 674334 4285.50
711 674334 -4112.15
712 674334 -4046.00
713 674334 4112.15
714 674334 -1836.76
715 674334 4046.00
716 674336 -31125.65
717 674336 -50138.79
718 674336 -6641.43
719 674336 87905.87
720 674336 -7694.33
721 674336 -87621.81
722 674336 87621.81
723 674336 -87592.42
724 674336 -16647.52
725 674336 87592.42
726 674336 16647.52
727 674337 -8326.51
728 674337 -685.66
729 674337 9012.17
730 674337 -2154.92
731 674337 -8914.22
732 674337 8914.22
733 674339 -23078.04
734 674339 -23058.45
735 674339 -3722.43
736 674339 26780.88
737 674339 -16671.26
738 674339 23078.04
739 674339 -26663.34
740 674339 26663.34
741 674340 -17118.09
742 674340 -15722.03
743 674340 -514.50
744 674340 -13194.53
745 674340 29431.07
746 674340 -26311.32
747 674340 17118.09
748 674340 -29293.93
749 674340 29293.93
750 674340 -29269.44
751 674340 29269.44
752 674341 -16650.69
753 674341 -1778.62
754 674341 -532.80
755 674341 -2920.82
756 674341 -19909.02
757 674341 21882.93
758 674560 -98.09
759 674560 98.09
760 674560 -67.23
761 675052 -134.64
762 675052 -97.92
763 675052 134.64
764 675053 -1038.16
765 675053 1038.16
766 675053 -176.30
767 675858 -6073.10
768 675858 -1579.46
769 675858 7652.56
770 675858 -3359.46
771 675858 -7454.23
772 675858 7454.23
773 675858 -7441.99
774 675858 -7368.50
775 675858 7441.99
776 675858 7368.50
777 675859 -5151.68
778 675859 -2816.29
779 675859 7967.98
780 675859 -7467.32
781 675859 -299.57
782 675859 7766.89
783 675859 -2164.16
784 675859 -7654.82
785 675859 7654.82
786 675860 -3257.17
787 675860 -3080.86
788 675860 -2578.80
789 675860 5659.66
790 675860 -1537.99
791 675860 3257.17
792 675860 -5469.06
793 675860 5469.06
794 675860 -1557.58
795 675860 1557.58
796 675979 -901.15
797 676061 -14134.11
798 676061 -2025.63
799 676061 -934.29
800 676061 17094.03
801 676061 -1137.78
802 676061 -16981.91
803 676061 -16935.37
804 676061 16981.91
805 676061 16935.37
806 676061 -16910.88
807 676061 16910.88
808 676070 -979.51
809 676075 -225.82
810 676075 225.82
811 676075 -193.98
812 676162 -1025.90
813 676162 1025.90
814 676162 -910.81
815 676163 -2882.16
816 676163 -2492.62
817 676163 2882.16
818 676163 2492.62
819 676163 -1177.04
820 676164 -177.75
821 676164 -97.90
822 676164 177.75
823 676201 -1107.62
824 676398 -1561.14
825 676479 -46.53
826 676479 -299.69
827 676479 346.22
828 676479 -327.16
829 676479 -572.07
830 676479 -276.74
831 676479 603.91
832 676479 572.07
833 676479 -515.26
834 676480 -2528.39
835 676480 2528.39
836 676480 -1906.89
837 676480 1906.89
838 676480 -1613.04
839 676480 1613.04
840 676480 -1469.51
841 676594 -2781.35
842 676594 -19.59
843 676594 2800.94
844 676594 -382.07
845 676594 -2575.65
846 676594 2575.65
847 676717 -2045.11
848 676717 2045.11
849 676717 -842.53
850 676717 -78.36
851 676717 842.53
852 676856 -39.15
853 676856 39.15
854 676856 -7.83
855 676979 -489.75
856 677072 -755.56
857 677072 755.56
858 677072 -314.85
859 677073 -860.23
860 677073 -52.23
861 677073 912.46
862 677073 -66.09
863 677073 -195.98
864 677073 195.98
865 677074 -369.48
866 677077 -475.59
867 677077 -453.95
868 677077 475.59
869 677077 -587.61
870 677077 1041.55
871 677077 -703.70
872 677097 -220.69
873 677102 -1982.04
874 677102 -1940.01
875 677102 1982.04
876 677102 -455.41
877 677102 1940.01
878 677112 -183.65
879 677189 -2838.46
880 677189 -22.05
881 677189 2860.51
882 677189 -2742.92
883 677189 -2831.12
884 677189 2831.12
885 677334 -326.22
886 677500 -3065.01
887 677500 -3035.62
888 677500 -156.76
889 677500 3192.38
890 677500 -48.98
891 677500 3065.01
892 677500 -3114.51
893 677500 3114.51
894 677502 -2631.80
895 677502 -2053.55
896 677502 4685.35
897 677502 -1205.00
898 677502 -3886.65
899 677502 -3820.50
900 677502 3886.65
901 677502 3820.50
902 677502 -3636.79
903 677502 3636.79
904 677502 -1375.40
905 677502 1375.40
906 677506 -1532.93
907 677506 -2327.48
908 677506 -19.59
909 677506 2347.07
910 677506 -504.33
911 677506 1532.93
912 677506 -1385.97
913 677506 1385.97
914 678084 -1508.66
915 678084 -39.15
916 678084 1508.66
917 678224 -25066.49
918 678224 25066.49
919 678224 -24512.13
920 678224 24512.13
921 678224 -1315.40
922 678224 -1273.36
923 678224 1315.40
924 678225 -2151.29
925 678225 2151.29
926 678225 -489.75
927 678226 -1820.78
928 678226 1820.78
929 678226 -587.71
930 678361 -770.67
931 678382 -242.55
932 678560 -2621.84
933 678560 -134.65
934 678560 -262.11
935 678560 -514.05
936 678560 3532.65
937 678560 -1049.49
938 678560 -3378.52
939 678560 3378.52
940 678560 -3279.58
941 678560 3279.58
942 678587 -51885.93
943 678587 51885.93
944 678587 -3145.93
945 678587 3145.93
946 678587 -132.01
947 679551 -848.29
948 679551 862.46
949 679551 -14.17
950 679551 -391.69
951 679551 -776.78
952 679551 -746.89
953 679551 776.78
954 679551 -734.65
955 679551 746.89
956 679551 734.65
957 679615 -1979.97
958 679615 1979.97
959 679615 -1950.58
960 679615 1950.58
961 679615 -1815.88
962 679615 -1742.43
963 679615 1815.88
964 680903 -1929.83
965 680903 1929.83
966 680903 -1905.34
967 680903 -636.69
968 680903 1905.34
969 680903 636.69
970 680903 -352.68
971 680906 -342.86
972 680906 -293.88
973 680906 342.86
974 680957 -146.95
975 681281 -372.21
976 681281 -313.44
977 681281 372.21
978 681590 -8070.35
979 681590 8070.35
980 681590 -7658.79
981 681590 -6351.59
982 681590 7658.79
983 681590 6351.59
984 681590 -4526.09
985 681937 -79.64
986 681937 -31.32
987 681937 -75.43
988 681937 -533.87
989 681937 720.27
990 681937 -661.50
991 682255 -1283.03
992 682255 1283.03
993 682255 -1220.38
994 682255 1220.38
995 682255 -140.94
996 685121 -764.02
997 685303 -979.88
998 685303 -531.65
999 685303 -2281.11
1000 685303 -1468.92
1001 685303 1511.53
1002 685303 2281.11
1003 685304 -6905.60
1004 685304 -5438.85
1005 685304 6905.60
1006 685304 -8862.56
1007 685304 -293.95
1008 685304 -4575.44
1009 685304 -168.93
1010 685304 13900.89
1011 685304 -253.66
1012 685304 -2944.96
1013 685304 5438.85
1014 685304 2944.96
Sheet1
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
A2:B1727 Expression =AND($B2=SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727))=1) text NO

I had just copied your formulae and changed the data range in the formulae from 11 to 1727 rows as I have 1727 line items. And put the formulae in conditional formatting.
Simply just want all the figures which turns to zero after setting off in calculation by a particularly dedicated on the basis of invoice ID's . Digit making zero should be highlighted or vice versa.

Joe4 · Jul 8, 2022

Things appear to be working (I see highlighted values).
Can you identify a specific Invoice Number from the data you posted that is not working properly?

Rambu · Jul 8, 2022

Joe4 said:
Things appear to be working (I see highlighted values).
Can you identify a specific Invoice Number from the data you posted that is not working properly?

There is a problem. Just total the column B all highlighted & Non highlighted one. Then total the highlighted one, both the figures are different. Non highlighted one should be zero because it plus minus doing in each other altogether.

Joe4 · Jul 8, 2022

Rambu said:
There is a problem. Just total the column B all highlighted & Non highlighted one. Then total the highlighted one, both the figures are different. Non highlighted one should be zero because it plus minus doing in each other altogether.

Let's look at the first one:

There are 5 values for that Invoice Number.
The sum of those 5 values in the Amount Paid column is -117.56.
The value in cell B5 is exactly equal to this amount, so it is being identified as the outlier and is highlighted.

So that appears to be working.
Can you point out to me a specific example (please give me an Invoice Number from your posting) that is not working correctly?

Please do not expect me to hunt through all your data to find one that does not work.
You obviously have identified one, so please provide it to me if you want my help.

Rambu · Jul 8, 2022

Joe4 said:
Let's look at the first one:

View attachment 68918

There are 5 values for that Invoice Number.
The sum of those 5 values in the Amount Paid column is -117.56.
The value in cell B5 is exactly equal to this amount, so it is being identified as the outlier and is highlighted.

So that appears to be working.
Can you point out to me a specific example (please give me an Invoice Number from your posting) that is not working correctly?

Please do not expect me to hunt through all your data to find one that does not work.
You obviously have identified one, so please provide it to me if you want my help.

I completely understand it. But highlighted ones total are not matching with net total .
Some invoices has not been highlighted as you have asked for example invoice no-559520, invoice no-561457 .

Joe4 · Jul 8, 2022

OK, there appear to be a few things going on here. The first one, I think is due to the famous Excel "floating arithmetic error". It has to do with how Excel stores numbers, and sometimes there is a tiny amount (a number like 7.34 might really be stored as 7.34000000001) and that can wreak havoc with computations, especially when trying to verify if two things are equal (see: Floating-point arithmetic may give inaccurate result in Excel - Office).

One way around that issue is to use the ROUND function in calculations, to cut it off at two decimals.
So if we update the formula like this:

Excel Formula:

=AND(ROUND($B2,2)=ROUND(SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727),2),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,ROUND(SUMIF($A$2:$A$1727,$A2,$B$2:$B$1727),2))=1)

I think that is what is going on with the 559520 Invoice, as after I made that update, that one seemed to work like it was supposed to:

However, regarding the 561457 invoice, if you total all the numbers for that invoice, it totals -1028.35. And if you look at all the entries for that invoice, you will see that none of them equal that amount. I mentioned a few times that my solution will only work for a single outlier (if there was a value exactly equal to the sum), and will NOT work for a combination of records adding up to that difference.

Finding matching positive and negative numbers

Rambu

New Member

Rambu

New Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Joe4

MrExcel MVP, Junior Admin

Rambu

New Member

Rambu

New Member

Joe4

MrExcel MVP, Junior Admin

Rambu

New Member

Joe4

MrExcel MVP, Junior Admin

Rambu

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

Finding matching positive and negative numbers

New Member

New Member

Excel Facts

MrExcel MVP, Junior Admin

MrExcel MVP, Junior Admin

New Member

New Member

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock