Find Pairs of Cells that Net to Zero and Highlight

[toonces]

Hello,

I've been trying to find a solution for this and I'm not sure of the best way to approach it. I have a sheet of transaction data where I need to find pairs of transactions that net to zero, then highlight those. I have the sheet sorted by Bill of Lading Date, then by Buyer Name.

Each pair must have the same Buyer Name and Bill of Lading Date. And some of the pairs will not have a Buyer Name, so I would match on amount only.

Also, as you can see from the data listed, the pairs won't always be consecutive (there may be a cell with a different value between 2 cells that net to zero).

I thought of doing an If AND statement, but I'm not sure how to get it to continue checking all of the cells if it doesn't find a match on the very next line. Also, I would need to make it so that once a pair has been found, neither of those are used again.

I'm open to any solutions including VBA, but I'm just not very experienced with it.

Any help would be greatly appreciated!

Bill Of Lading Date	Net Units	Buyer Name
3/6/2020​	-3984​	BLUE CO INC
3/6/2020​	3984​	BLUE CO INC
3/6/2020​	-3984​	DIAMOND CO INC
7/7/2020​	-5447​	SANTA INC
7/7/2020​	-2971​	SANTA INC
7/7/2020​	2971​	SANTA INC
7/7/2020​	3763​	SANTA INC
7/7/2020​	1684​	TARA INC
9/2/2020​	-5879​	RL INC
9/2/2020​	-2763​	RL INC
9/2/2020​	2763​	RL INC
9/2/2020​	5879​	RL INC
9/2/2020​	-6867​	STAR BAY INC
9/2/2020​	-1473​	STAR BAY INC
9/2/2020​	1473​	STAR BAY INC
9/2/2020​	6867​	STAR BAY INC
9/8/2020​	539​	WELL SPIN INC INC
9/8/2020​	-539​	WARM SERVICES INC
9/12/2020​	-4742​	P COMPANY
9/12/2020​	-4742​	P COMPANY
9/12/2020​	-3636​	P COMPANY
9/12/2020​	-3636​	P COMPANY
9/12/2020​	3636​	P COMPANY
9/12/2020​	3636​	P COMPANY
9/12/2020​	4742​	P COMPANY
9/12/2020​	4742​	P COMPANY
9/14/2020​	-30087​
9/14/2020​	-30087​
9/14/2020​	-30087​
9/14/2020​	-30087​
9/14/2020​	-30087​
9/14/2020​	-30087​
9/14/2020​	30087​
9/14/2020​	30087​
9/14/2020​	30087​
9/14/2020​	30087​
9/14/2020​	30087​
9/14/2020​	30087​
9/14/2020​	-29663​
9/14/2020​	-29663​
9/14/2020​	-29663​
9/14/2020​	-29663​
9/14/2020​	29663​
9/14/2020​	29663​
9/14/2020​	29663​
9/14/2020​	29663​
9/14/2020​	-31098​
9/14/2020​	-31098​
9/14/2020​	-31098​
9/14/2020​	-31098​
9/14/2020​	31098​
9/14/2020​	31098​
9/14/2020​	31098​
9/14/2020​	31098​
9/16/2020​	-4675​	P COMPANY
9/16/2020​	-1868​	P COMPANY
9/16/2020​	1868​	P COMPANY
9/16/2020​	4675​	P COMPANY
9/18/2020​	-4950​	ARTO INC
9/18/2020​	-4950​	ARTO INC
9/18/2020​	-1884​	ARTO INC
9/18/2020​	-1884​	ARTO INC
9/18/2020​	1884​	ARTO INC
9/18/2020​	4950​	ARTO INC

 

[Toadstool]

Hi Toonces,

If I can use worker columns:

Toonces.xlsx
	A	B	C	D	E	F	G
1	Bill Of Lading Date	Net Units	Buyer Name		Concat 1	Matched row	Back to
2	3/6/2020	-3984	BLUE CO INC		-3984BLUE CO INC43896	3
3	3/6/2020	3984	BLUE CO INC		3984BLUE CO INC43896		2
4	3/6/2020	-3984	DIAMOND CO INC		-3984DIAMOND CO INC43896
5	7/7/2020	-5447	SANTA INC		-5447SANTA INC44019
6	7/7/2020	-2971	SANTA INC		-2971SANTA INC44019	7
7	7/7/2020	2971	SANTA INC		2971SANTA INC44019		6
8	7/7/2020	3763	SANTA INC		3763SANTA INC44019
9	7/7/2020	1684	TARA INC		1684TARA INC44019
10	9/2/2020	-5879	RL INC		-5879RL INC44076	13
11	9/2/2020	-2763	RL INC		-2763RL INC44076	12
12	9/2/2020	2763	RL INC		2763RL INC44076		11
13	9/2/2020	5879	RL INC		5879RL INC44076		10
14	9/2/2020	-6867	STAR BAY INC		-6867STAR BAY INC44076	17
15	9/2/2020	-1473	STAR BAY INC		-1473STAR BAY INC44076	16
16	9/2/2020	1473	STAR BAY INC		1473STAR BAY INC44076		15
17	9/2/2020	6867	STAR BAY INC		6867STAR BAY INC44076		14
18	9/8/2020	539	WELL SPIN INC INC		539WELL SPIN INC INC44082
19	9/8/2020	-539	WARM SERVICES INC		-539WARM SERVICES INC44082
20	9/12/2020	-4742	P COMPANY		-4742P COMPANY44086	26
21	9/12/2020	-4742	P COMPANY		-4742P COMPANY44086	27
22	9/12/2020	-3636	P COMPANY		-3636P COMPANY44086	24
23	9/12/2020	-3636	P COMPANY		-3636P COMPANY44086	25
24	9/12/2020	3636	P COMPANY		3636P COMPANY44086		22
25	9/12/2020	3636	P COMPANY		3636P COMPANY44086		23
26	9/12/2020	4742	P COMPANY		4742P COMPANY44086		20
27	9/12/2020	4742	P COMPANY		4742P COMPANY44086		21
28	9/14/2020	-30087			-3008744088	34
29	9/14/2020	-30087			-3008744088	35
30	9/14/2020	-30087			-3008744088	36
31	9/14/2020	-30087			-3008744088	37
32	9/14/2020	-30087			-3008744088	38
33	9/14/2020	-30087			-3008744088	39
34	9/14/2020	30087			3008744088		28
35	9/14/2020	30087			3008744088		29
36	9/14/2020	30087			3008744088		30
37	9/14/2020	30087			3008744088		31
38	9/14/2020	30087			3008744088		32
39	9/14/2020	30087			3008744088		33
40	9/14/2020	-29663			-2966344088	44
41	9/14/2020	-29663			-2966344088	45
42	9/14/2020	-29663			-2966344088	46
43	9/14/2020	-29663			-2966344088	47
44	9/14/2020	29663			2966344088		40
45	9/14/2020	29663			2966344088		41
46	9/14/2020	29663			2966344088		42
47	9/14/2020	29663			2966344088		43
48	9/14/2020	-31098			-3109844088	52
49	9/14/2020	-31098			-3109844088	53
50	9/14/2020	-31098			-3109844088	54
51	9/14/2020	-31098			-3109844088	55
52	9/14/2020	31098			3109844088		48
53	9/14/2020	31098			3109844088		49
54	9/14/2020	31098			3109844088		50
55	9/14/2020	31098			3109844088		51
56	9/16/2020	-4675	P COMPANY		-4675P COMPANY44090	59
57	9/16/2020	-1868	P COMPANY		-1868P COMPANY44090	58
58	9/16/2020	1868	P COMPANY		1868P COMPANY44090		57
59	9/16/2020	4675	P COMPANY		4675P COMPANY44090		56
60	9/18/2020	-4950	ARTO INC		-4950ARTO INC44092	65
61	9/18/2020	-4950	ARTO INC		-4950ARTO INC44092
62	9/18/2020	-1884	ARTO INC		-1884ARTO INC44092	64
63	9/18/2020	-1884	ARTO INC		-1884ARTO INC44092
64	9/18/2020	1884	ARTO INC		1884ARTO INC44092		62
65	9/18/2020	4950	ARTO INC		4950ARTO INC44092		60
Sheet1

Cell Formulas
Range	Formula
E2:E65	E2	=B2&C2&A2
F2:F64	F2	=IFERROR(AGGREGATE(15,6,ROW($E3:$E$65)/($E3:$E$65=B2*-1&C2&A2),COUNTIF($E$1:$E1,E2)+1),"")
G2:G65	G2	=IFERROR(IF(F2<>"","",MATCH(ROW(),$F$2:$F$65,0)+1),"")
F65	F65	=IFERROR(AGGREGATE(15,6,ROW($E$65:$E66)/($E$65:$E66=B65*-1&C65&A65),COUNTIF($E$1:$E64,E65)+1),"")