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!
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 |