Forum Discussion

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

Enumerating.pdf241 KB

OliverScheurich

Gold Contributor

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.

Enumerating.pdf241 KB

NewelleNielsen8821

Copper Contributor

Jan 07, 2022

Yes this works perfectly - just need to get the formatting so it will be two digits behind the . so thanks again!

Riny_van_Eekelen
Platinum Contributor
Jan 08, 2022
NewelleNielsen8821 Perhaps wiser not to hard-code all the category names with nested IF statements. In the attached file, I've demonstrated the use of a lookup table and also resolved the two-digits issue after the point.
nielsen8821.xlsx12 KB
- NewelleNielsen8821
  Copper Contributor
  Jan 10, 2022
  @Riny_Van_Eeklen403176
  
  This is SO GREAT - thank you - my next task is to ensure that if a row gets deleted that the number stays the same - do I have to do that in a Macro to set it to text once identified? It has to be dynamic to a point as new things are added throughout the project and others are moved off but they need to keep their values so it would need to be dynamic for the new entries only and the once established no changes. Hopefully that makes sense.
  - 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.