awhite0320
New Member
- Joined
- Sep 20, 2018
- Messages
- 5
I have a approximately 35,000 rows of general ledger transactions. The transactions contain both debit and credit amounts and various policy numbers. I need to evaluate the rows to identify the rows, by policy number, that do not net to zero. I would like for the rows that do not zero out return a value of FALSE and the rows that zero out return a value of TRUE. For example:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Policy Number[/TD]
[TD]AMOUNT[/TD]
[TD]MATCH[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]40.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]-40.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]100.00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]1250.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]-1250.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]3000.00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The false rows are my not matched items. I have a countif formula that i can use but I have to update the range at each change in policy number, which is very tedious when you have 35,000 rows. Can I accomplish my need with an Excel formula or VBA code?
Thanks so much!!
Amy
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Policy Number[/TD]
[TD]AMOUNT[/TD]
[TD]MATCH[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]40.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]-40.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]100.00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]1250.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]-1250.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]3000.00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The false rows are my not matched items. I have a countif formula that i can use but I have to update the range at each change in policy number, which is very tedious when you have 35,000 rows. Can I accomplish my need with an Excel formula or VBA code?
Thanks so much!!
Amy