Validation Formula or Code

[hooper12345]

Hello Helpers,

I have roughly 8,000-10,000 rows of procedure codes that span from columns A:V. My company would like to see if it is possible to determine how often a set of codes appears if not the individual code itself. We had starting implementing "CountIfs" and a range (ex. countifs(a:v, ">10000", a:v, "<10999")). This however is too time consuming and there are codes that contain letters but can still be drilled into a range set.

I'd be more than happy to supply my workbook once someone states that they have a helpful idea!

Thank you in advance.

 

[Rick Rothstein]

and there are codes that contain letters but can still be drilled into a range set.

I think you will have to explain the above part in a little more detail... we would need to know what your codes could look like before we can know what kind of code could be written. We would not need to see an entire filled sheet, just a listing of the patterns your codes can take (length, where letters can be within the text when the code is not a number, etc.). Also, what a range set would look like for your non-numerical codes. In other words we need to know the "shape" your codes can take without having to pour over 200,000 or so filled cells.

 

[hooper12345]

I believe that encompasses all code formats. I'm not sure if that picture will be view-able, examples: 10123, C9600RC, J1030, 76930RT, 9928525.
Let's assume that all ranges will include everything from the listed examples thousand unit on up (10,000-99,999; 1,000-9,999; 9,992,000-9,992,999)

 

[hooper12345]

examples: 10123, C9600RC, J1030, 76930RT, 9928525.
Let's assume that all ranges will include everything from the listed examples thousand unit on up (10,000-99,999; 1,000-9,999; 9,992,000-9,992,999)

 

[Rick Rothstein]

examples: 10123, C9600RC, J1030, 76930RT, 9928525.
Let's assume that all ranges will include everything from the listed examples thousand unit on up (10,000-99,999; 1,000-9,999; 9,992,000-9,992,999)

Okay, so there is no real limits (minimum or maximum) on the number of digits or on the number of surrounding text characters. If you have codes like this...

12345
AB12346CD
123457

Should they all be included in the range 10000:99999? In other words, for grouping within numerical ranges, the letters should be ignored, correct?

 

[hooper12345]

Okay, so there is no real limits (minimum or maximum) on the number of digits or on the number of surrounding text characters. If you have codes like this...

12345
AB12346CD
123457

Should they all be included in the range 10000:99999? In other words, for grouping within numerical ranges, the letters should be ignored, correct?

I don't believe that letters should be ignored. A-Z aren't present. They are procedure codes, therefore the letters are specific but the numbers vary.

 

[Rick Rothstein]

I don't believe that letters should be ignored.

If you aren't sure then how can we here this forum know what to do?

A-Z aren't present. They are procedure codes, therefore the letters are specific but the numbers vary.

What do you mean "A-Z aren't present"... how can the letters be specific if there aren't any letters?



Earlier you said...

examples: 10123, C9600RC, J1030, 76930RT, 9928525.
Let's assume that all ranges will include everything from the listed examples thousand unit on up (10,000-99,999; 1,000-9,999; 9,992,000-9,992,999)

I don't understand... how did you want the codes with letters to fit in the ranges highlighted above? In essence, that was the question I asked immediately before you gave me your answers above which, unfortunately, have left me somewhat confused.