Excel help request (please)

[tonytiger55]

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).
90949

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.

[flipstah]

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).
90949

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.

1.) To highlight certain rows, use 'Conditional Formatting'; makes it easy

Is your end goal to get a total of premiums that have:

Option A
- The same policy #
AND
- The same form code
AND
- The same Transaction Number

Option B
- The same policy #
AND
- The same transaction number

Your requirements are too muddled and I can't decipher what you really want.

Break it down one requirement at a time and use 'If... Then...'

Ex, If I add up all the policy A with Transaction 53, then the expected premium is 249

[Thaco]

probably if's with vlookups and more if's but its not clear what you need.

[tonytiger55]

Sorry.

I'l break it down what needs to happen first.

There are duplicate policy numbers.
EG.

AA123456
AA123456
AA123456

Each has different transaction code. 10, 12, etc

If there is a transaction code is 15 on any of the policy numbers.

eg. AA123456 | 15

THEN
Find transaction code 20, or 21, or 23 or 25 AND Same policy number.
e.g AA123456| 20

THEN

You will have two rows(im using 20 as a example).
1. AA123456 | 15 | Form Code | 60
2. AA123456 | 20 | Form Code | -60

Now if the Form codes are Equal.
THEN CALCULATE PREMIUMS

eg. $60+-$60

1. AA123456 | 15 | Form Code | 60 | $0
2. AA123456 | 20 | Form Code | -60 | $0

If the value is '0' I need to know. I have to delete this.

There are policy numbers transaction codes of 22.
But there is no Transaction code of 15 with the same policy number. These I ignore.

[flipstah]

90951
Sorry.

I'l break it down what needs to happen first.

There are duplicate policy numbers.
EG.

AA123456
AA123456
AA123456

Each has different transaction code. 10, 12, etc

If there is a transaction code is 15 on any of the policy numbers.

eg. AA123456 | 15

THEN
Find transaction code 20, or 21, or 23 or 25 AND Same policy number.
e.g AA123456| 20

THEN

You will have two rows(im using 20 as a example).
1. AA123456 | 15 | Form Code | 60
2. AA123456 | 20 | Form Code | -60

Now if the Form codes are Equal.
THEN CALCULATE PREMIUMS

eg. $60+-$60

1. AA123456 | 15 | Form Code | 60 | $0
2. AA123456 | 20 | Form Code | -60 | $0

If the value is '0' I need to know. I have to delete this.

There are policy numbers transaction codes of 22.
But there is no Transaction code of 15 with the same policy number. These I ignore.

Does a pivot table work? Or do you need to do data cleanup with all your variables?

[Thaco]

i think it could be done cleanly with a few vlookups

[sabad66]

It was a bit tricky understanding what you want to do but i think i got it. Try this.

1. Sort your table by Policy Number, Transaction Number, Form Code, Premium (all 4 levels all ascending)
2. in column E, add this formula and drag it all the way down

=IF(OR(AND(B2=15,AND(C2=C3,and(D2+D3=0,A2=A3))),AND(OR(B2=20,OR(B2=21,OR(B2=23,B2=25))),AND(B1=15,AND(C2=C1,and(D2+D1=0,A2=A1))))),"delete","nodelete")

note the first row will give an error, but all rows after should give you a value of either "delete" or "nodelete"
3. filter your table on column E to remove the "delete"
4. copy the filtered table into a new sheet. This sheet now has all the data you want.
5. clean up your new sheet by deleting column E (all of the values should say 'nodelete')

[tonytiger55]

Thanks for the input Thaco & Sabad.
Sabad I'l give it a go tomorrow. I also got Flipstah sending me something on excel.
Much appreciated. :clap:

[flipstah]

[QUOTE=sabad66;4867774]It was a bit tricky understanding what you want to do but i think i got it. Try this.

1. Sort your table by Policy Number, Transaction Number, Form Code, Premium (all 4 levels all ascending)
2. in column E, add this formula and drag it all the way down

=IF(OR(AND(B2=15,AND(C2=C3,and(D2+D3=0,A2=A3))),AND(OR(B2=20,OR(B2=21,OR(B2=23,B2=25))),AND(B1=15,AND(C2=C1,and(D2+D1=0,A2=A1))))),"delete","nodelete")

note the first row will give an error, but all rows after should give you a value of either "delete" or "nodelete"
3. filter your table on column E to remove the "delete"
4. copy the filtered table into a new sheet. This sheet now has all the data you want.
5. clean up your new sheet by deleting column E (all of the values should say 'nodelete')[/QUOTE
Daaamn way better than mine hahaha

[flipstah]

So I tried to do a couple of things. Personally, I don't like nested formulas because it's hard to troubleshoot if something breaks and I don't do VBA, so I broke it down into clusters as separate columns.

Column A = Policy #
Column B = Transaction #
Column C = Form Code
Column D = Premium Amount (+ or -)

Requirements:

1.) A policy premium can only be calculated if a policy number has Transaction code #15 in any row
2.) If a policy is deemed to contain a transaction # 15, then it must look for transaction #20, 21, 23, and 25 within the same policy
3.) If (1) and (2) are true, it must calculate premiums within the same policy and related transaction codes
3a.) You can only add premium values within the same Form Code
4.) If the sum of (3) equals to zero, the associated rows that satisfies (1) and (2) must be highlighted

So I started with this...




=IF(COUNTIFS(B:B,15,A:A,A2)>0,"YES","NO")



That first one I created in column E hunts for any policy # in Column A if it has a transaction number 15 in Column B, which starts the first condition:

Policy Has Code 15?
Y = Proceed
N = Do Nothing




=AND(B2=15,B2=20,B2=21,B2=23,B2=25)



Second formula in column F is an independent formula that hunts for associated transaction codes 15, 20, 21, 23, 25.

So if E is 'YES', and F is 'TRUE', I wanted to do a SUMIFS but this is where it broke.


=SUMIFS(D: D,E:E,"YES",F:F,"TRUE",A:A,A2,C:C,C2)

Once the SUMIFS work, you can do a conditional formatting highlight rule of


=AND(E2="YES',F2="TRUE",G2=0)

[birdman86]

So you're looking for any policies with matching form codes that have transaction 15 AND one of 20, 21, 23 or 25 - if the premiums sum to zero, then delete?

Honestly if there's deleting involved, I'd just use data filters... Filter all the policies with 15's, copy that list to a separate spot. Then in a new column on the main data, do a match() - if that rows policy is found on the list, then return 1, else 0. Filter to show the 1's only (aka policies with 15's), then filter to show only 20, 21, 23, and 25. Subtotal the premiums on each of the remaining policies and oila you can delete or whatever as needed and no need for crazy complex formula's.

[rx7boi]

He's only got 19 rows in his sample but if his actual spreadsheet contains a significant amount of policies, having a robust formula would help automate things if this is not an ad-hoc request.

[tonytiger55]

So you're looking for any policies with matching form codes that have transaction 15 AND one of 20, 21, 23 or 25 - if the premiums sum to zero, then delete?

Honestly if there's deleting involved, I'd just use data filters... Filter all the policies with 15's, copy that list to a separate spot. Then in a new column on the main data, do a match() - if that rows policy is found on the list, then return 1, else 0. Filter to show the 1's only (aka policies with 15's), then filter to show only 20, 21, 23, and 25. Subtotal the premiums on each of the remaining policies and oila you can delete or whatever as needed and no need for crazy complex formula's.

I kinda did that. Its not just code 15. The policy numbers have to match. I moved the 15 codes on a separate sheet and vlookuped the data to match with 20 codes along with the policy number. Then the Form codes have to match. You can't compare premium with form code 53 with 54.

I manually checked the policy numbers with the matching data. The problem is. There is over 4000 rows of data.
This was not too bad. About 50 police needed to be amended.

So after this was cleaned up, I found another issue thats waaay more complicated involving other codes. Im still trying to work out the logic on that one. :facepalm:

[ExtraSlow]

Little tip for this, or anyone who's not really experienced in programming or writing logic functions.
Try writing it out in a block format with plain English "pseudocode" and make sure you are happy with the logic in that format before you turn it into VBA, or real excel functions.

I find that helps me anyway. Let's me separate the "what" from the "how".