Quantcast
Excel help request (please) - Beyond.ca - Car Forums
Results 1 to 14 of 14

Thread: Excel help request (please)

  1. #1
    Join Date
    Sep 2016
    Location
    Calgary, Ab
    My Ride
    2021 Zonda CRV
    Posts
    1,008
    Rep Power
    18

    Default Excel help request (please)

    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).
    Name:  Excel help.jpg
Views: 204
Size:  72.1 KB

    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.

  2. #2
    Join Date
    Jul 2008
    Location
    Transnistria
    My Ride
    Exploded.
    Posts
    8,207
    Rep Power
    51

    Default

    Quote Originally Posted by tonytiger55 View Post
    This quote is hidden because you are ignoring this member. Show Quote
    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).
    Name:  Excel help.jpg
Views: 204
Size:  72.1 KB

    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

  3. #3
    Thaco's Avatar
    Thaco is offline sucks off little boys (ya, don't fuck with rage2 bitch!!!)
    Join Date
    Oct 2004
    Location
    Calgary
    My Ride
    Rage2
    Posts
    3,868
    Rep Power
    29

    Default

    probably if's with vlookups and more if's but its not clear what you need.
    User title molested by Rage2.

    Quote Originally Posted by rage2 View Post
    This quote is hidden because you are ignoring this member. Show Quote
    It's not the size that matters, it's the taste it leaves in your mouth.

    Quote Originally Posted by JRSC00LUDE
    This quote is hidden because you are ignoring this member. Show Quote
    I say stupid shit all the time.
    ^^ Fact Checked


    Quote Originally Posted by Misterman View Post
    This quote is hidden because you are ignoring this member. Show Quote
    No logic, thought, input, etc from cult member...

  4. #4
    Join Date
    Sep 2016
    Location
    Calgary, Ab
    My Ride
    2021 Zonda CRV
    Posts
    1,008
    Rep Power
    18

    Default

    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.

  5. #5
    Join Date
    Jul 2008
    Location
    Transnistria
    My Ride
    Exploded.
    Posts
    8,207
    Rep Power
    51

    Default

    Name:  Screen Shot 2020-04-23 at 7.11.06 PM.jpg
Views: 169
Size:  62.7 KB
    Quote Originally Posted by tonytiger55 View Post
    This quote is hidden because you are ignoring this member. Show Quote
    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?
    Last edited by flipstah; 04-23-2020 at 07:18 PM.

  6. #6
    Thaco's Avatar
    Thaco is offline sucks off little boys (ya, don't fuck with rage2 bitch!!!)
    Join Date
    Oct 2004
    Location
    Calgary
    My Ride
    Rage2
    Posts
    3,868
    Rep Power
    29

    Default

    i think it could be done cleanly with a few vlookups
    User title molested by Rage2.

    Quote Originally Posted by rage2 View Post
    This quote is hidden because you are ignoring this member. Show Quote
    It's not the size that matters, it's the taste it leaves in your mouth.

    Quote Originally Posted by JRSC00LUDE
    This quote is hidden because you are ignoring this member. Show Quote
    I say stupid shit all the time.
    ^^ Fact Checked


    Quote Originally Posted by Misterman View Post
    This quote is hidden because you are ignoring this member. Show Quote
    No logic, thought, input, etc from cult member...

  7. #7
    Join Date
    Apr 2004
    Location
    Calgary
    Posts
    2,093
    Rep Power
    43

    Default

    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
    Code:
    =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')

  8. #8
    Join Date
    Sep 2016
    Location
    Calgary, Ab
    My Ride
    2021 Zonda CRV
    Posts
    1,008
    Rep Power
    18

    Default

    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.

  9. #9
    Join Date
    Jul 2008
    Location
    Transnistria
    My Ride
    Exploded.
    Posts
    8,207
    Rep Power
    51

    Default

    [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
    Code:
    =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

  10. #10
    Join Date
    Jul 2008
    Location
    Transnistria
    My Ride
    Exploded.
    Posts
    8,207
    Rep Power
    51

    Default

    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)
    Last edited by flipstah; 04-24-2020 at 10:10 AM.

  11. #11
    Join Date
    Mar 2015
    Location
    Calgary, AB
    My Ride
    Toyota’s
    Posts
    307
    Rep Power
    12

    Default

    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.

  12. #12
    Join Date
    Nov 2003
    Location
    Moo Town
    My Ride
    (0^oo^0)~
    Posts
    746
    Rep Power
    23

    Default

    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.

  13. #13
    Join Date
    Sep 2016
    Location
    Calgary, Ab
    My Ride
    2021 Zonda CRV
    Posts
    1,008
    Rep Power
    18

    Default

    Quote Originally Posted by birdman86 View Post
    This quote is hidden because you are ignoring this member. Show Quote
    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.

  14. #14
    Join Date
    Jan 2008
    Location
    Upstairs
    My Ride
    Natural Gas.
    Posts
    13,332
    Rep Power
    100

    Default

    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".
    Quote Originally Posted by killramos View Post
    This quote is hidden because you are ignoring this member. Show Quote
    You realize you are talking to the guy who made his own furniture out of salad bowls right?

Similar Threads

  1. 400 Bad Request - Request Header Or Cookie Too Large - nginx/1.2.0

    By Grogador in forum Suggestion/Comment Box/Forum Related Stuff
    Replies: 1
    Latest Threads: 07-05-2012, 10:59 PM
  2. photoshop request, please help

    By suprame in forum Photoshop Gallery
    Replies: 7
    Latest Threads: 04-07-2010, 02:01 PM
  3. Excel gurus help please

    By Lexxan in forum Computers, Consoles, and other Electronics
    Replies: 6
    Latest Threads: 02-22-2007, 07:06 PM
  4. PLEASE PLEASE PLEASE HELP. Fuel problem.

    By tiffanymarie81 in forum Mechanical
    Replies: 5
    Latest Threads: 09-09-2004, 09:09 AM
  5. Simple rims chop request. Please help.

    By rc2002 in forum Photoshop Gallery
    Replies: 20
    Latest Threads: 06-06-2004, 10:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •