Forum Discussion

NewelleNielsen8821's avatar
NewelleNielsen8821
Copper Contributor
Jan 07, 2022
Solved

Enumerating Smartly in Excel

Hi I have an excel question - I need to have the function be smart enough to do two things:

 

1.  Determine the first integer of a value (ex x.xx) using Report Category column (SEE BELOW) based on 1-5 Values  

2.  Find the largest value in the column with the numbers in it and add .01 to it.  This is the hard part as the first integer needs to be taken into consideration first and then look at the trailing 2 integers after the <.> to get the value to start with then add the .01 to it.   

 

Below is an example of what I have so far showing what I need - NOTE IT WILL NOT BE IN PERFECT ORDER BY THE REPORT ID - IS FOR THIS EXAMPLE ONLY:

I used this function to get the first integer   =IF($D4="External Client Regulatory","1.",IF($D4="External Operational Request","2.",IF($D4="Evolent Standard Operational","3.",IF($D4="Evolent Vendor Operational","4.",IF($D4="Evolent Internal Operational","5.",0)))))

But after that I can't figure out what to do to get the correct trailing numbers.    It does not need to be dynamic as we often times add and delete rows from the output so the numbers should stay the same once they are assigned.  

 

 

Report ID  Report Name                                                    Report Category

1.01Anthem & EMBLEM Call Center Stats ReportExternal Operational Request
1.02Anthem & Emblem Care Program Details ReportExternal Operational Request
2.01Anthem & Emblem CMARTExternal Client Regulatory
2.02Anthem Newborn Kick PaymentExternal Client Regulatory
5.01Internal Anthem Weekly Auths FileEvolent Internal Operational
5.02Internal Emblem claim denial and adjustment detailsEvolent Internal Operational


 

  • NewelleNielsen8821 

    =IF(D4="External Client Regulatory","1."&COUNTIF($D$4:D4,"External Client Regulatory"),

    IF(D4="External Operational Request","2."&COUNTIF($D$4:D4,"External Operational Request"),

    IF(D4="Evolent Standard Operational","3."&COUNTIF($D$4:D4,"Evolent Standard Operational"),

    IF(D4="Evolent Vendor Operational","4."&COUNTIF($D$4:D4,"Evolent Vendor Operational"),

    IF(D4="Evolent Internal Operational","5."&COUNTIF($D$4:D4,"Evolent Internal Operational"),0)))))

     

    Maybe with above formula which i applied in the attached file.

     

6 Replies

  • NewelleNielsen8821 

    =IF(D4="External Client Regulatory","1."&COUNTIF($D$4:D4,"External Client Regulatory"),

    IF(D4="External Operational Request","2."&COUNTIF($D$4:D4,"External Operational Request"),

    IF(D4="Evolent Standard Operational","3."&COUNTIF($D$4:D4,"Evolent Standard Operational"),

    IF(D4="Evolent Vendor Operational","4."&COUNTIF($D$4:D4,"Evolent Vendor Operational"),

    IF(D4="Evolent Internal Operational","5."&COUNTIF($D$4:D4,"Evolent Internal Operational"),0)))))

     

    Maybe with above formula which i applied in the attached file.

     

    • NewelleNielsen8821's avatar
      NewelleNielsen8821
      Copper Contributor
      Yes this works perfectly - just need to get the formatting so it will be two digits behind the . so thanks again!

Resources