Need help to find ranges of unused keycodes :-)

[calihiker]

Hi, I have a table that has a keycode field which is 4 characters long and is alphanumeric. The keycodes in the table are the ones that are already used and I have to find out what the longest contiguous range of unused keycodes is. Keycodes look like this for example "A1W9" and the sequence for each position would start with 0 to 9 then A to Z. Is this also possible in a query? I haven't searched for ranges before so any help would be very appreciated. Thanks!

Andre

 

[SkipVought]

Hi,

I think that the easiest way would be to build a table of key codes starting with the first key code in your list and ending with the last key code in your list and a sequence number.

Then it's just a matter of a lookup of the key code sequence number to determine the contiguous blocks.

It could all be done on a sheet without any coding.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[calihiker]

Thanks for the reponse! Could you explain a little more on this part "lookup of the key code sequence number to determine the contiguous blocks."? Would the sequence number be an independent index number starting from 1 and incrementing my 1 every record? I'm not sure how to go about it. Would that give me the number of unused keycode instances between the range? Or what kind of data would I be retrieving?

Thanks,
Andre

 

[calihiker]

Ok, now I have a table that contains all the keycode combinations with an addtional field for a sequential numeric id field. The ID field increment by one and follows the order of the keycodes. What would be the next step to determine the size gap between two used keycodes? The problem I am confronting is how determine the largest of the gaps rather than all of them....

 

[SkipVought]

You have your master contiguous list of codes, and a corresponding sequence number. If this is the same list that was generated using Base 36, then the sequence number and the Key Code represent the same value.

Now you have 2 key code values that you want to evaluate how far apart they are. You lookup the key code value and return the sequence number for each. The difference in sequence numbers tells you how far apart they are.

So if A1 & A2 have your 2 key code values, the formula might look like this if the Key Code List is named KeyCodeList...

=ABS(VLOOKUP(A1,KeyCodeList,2,False)-VLOOKUP(A2,KeyCodeList,2,False))

Now if you list all your key codes that you are looking up in col A and copy that formula down, you'll have numbers that show the distance between each pair.

If you copy/paste special - values that list and sort it by highest number, you have your answer, I think



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com