Any excel wizzes would like a covid challenge?
I have a tight deadline next week and I need some help. Im really stuck.
I have a mass amount of data with multiple columns.
There are four key columns:
Policy Number, Transaction number, (key variables of 15, 20, 21 ,23 and 25), Form codes and Premium.
There may be one policy number on four or six rows. Some of these have negative premium. That is fine, it means someone is getting their money back. Or amending a policy mid term.
I need the policy numbers with Transaction code 15 highlighted or moved to another cell ( I can do this manually).
Then I need any row with the same policy number WITH transaction number 20, OR, 21, OR, 23 OR 25
From the 20 data.
The FORM code has to be the same.
Then I need the premium totaled up. If the number is Zero. I need to delete this row.
15 means reinstated.
20 means cancelled.
I have attached a image as a example(I have amended policy numbers for privacy reasons).
Rows 2-7 have the same policy number.
The green are fine.
Rows 3 and 4 need to be deleted from the main spreadsheet as the premium amounts to
zero. These are policy's that have been cancelled.
Rows 12-17 have the same issue.
15 means reinstatement. 20, 21, 23 and 25 is cancellation.