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 ...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jan 07, 2022

    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.

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 10, 2022

NewelleNielsen8821 That would probably/indeed be something for VBA. Not my 'cup of tea' though. But before you go that way, consider to not delete data at all. Add a column to indicate something like "Cancelled" of "Invalid", whatever and then filter out these rows. Than you will at least have control over the created numbers.

NewelleNielsen8821's avatar
NewelleNielsen8821
Copper Contributor
Jan 10, 2022
Good Suggestion however I'm new to the organization so would have to check with the PMs and above to ensure as they are used to just seeing the active ones and remembering to filter is asking - however could do a pivot or something......will consider that and again appreciate your quick responses!

Resources