list of missing numbers within a defined range

[jdaf_2412]

Good day
I have a "consecutive" table where I place a range of [start] and another of [end], to restrict the numbers that users place in a column type number [Label] of another "Registration" table.

I would like to generate a list that tells me the numbers that have not been used within the assigned range, only if they have a greater consecutive, example:
If they are assigned a start-to-end range 1-20, and the user has used:
1,2,3,6,7,9
tell me that the numbers "4,5,8" are missing, or failing that "4,5,8,10,11,12,13....20")

I appreciate your help.

[Joseph_Seddik]

Are [start] and [end] global values? or do they apply on a per-row basis? The solution is simple but the exact expression would depend on your actual data setup. 

Would you please post screenshots of your involved tables? Thanks.

[jdaf_2412]

thanks for your attention.
I attach images of my tables, excuse the changes in the names of the columns and tables with respect to the publication, (I do it because of the language).
Thank you again.

Table 1Table 2Column [label] Table 2

[Joseph_Seddik]

Thank you. Last question: in what table you'll have that column listing the missing numbers?

Don't worry, I understand Spanish.

[jdaf_2412]

Consecutivos

[Joseph_Seddik]

Thank you. Here's what to do:

1. Manually construct a single-column sheet table called Numbers, with a column "Number". This column will list all numbers in order from the lowest possible [start], till the highest possible [end]. 
   
   
2. Have a new virtual column "userLabels" in Consecutivos, type List, with the following App Formula: 
   
   SELECT(Formulario[Label], [ID Usuario] = [_ThisRow].[ID Usuario])
   
   
3. Use this formula to calculate the list of missing Labels per usuario:
   
   SELECT(Numbers[Number], AND(
     [Number] < MAX([_ThisRow].[userLabels]), 
     [Number] >= [_ThisRow].[Start],
     NOT( IN([Number], [_ThisRow].[userLabels]) )
   ))

[jdaf_2412]

I understand, but I would have to list the entire column [number] of my new table "numbers", right?
If so, there are too large ranges, and they are not in order, today a consecutive 1-100 can be delivered, and tomorrow another user 110000-110200.

[Joseph_Seddik]

And why would you have such colossal disparity in user ranges?! If I'm dealing with machines and network elements, not users, I wouldn't end with a disparity in the x100000 order of magnitude. To me this shouts of bad design decisions and you might want to reconsider. 

Anyway:

• It is not that ranges have to be in order, it is simply your Number column: from 1 to 110200
• Yes the solution is based on having this static list. The only two other possibilities I could think of is to use Recursive Behavior Actions within AppSheet, or use Apps Script to do this calculation in your sheet.  

[jdaf_2412]

Yes, basically the numbers are randomly given from another program in a range less than 200,000 and in packages of 100 labels, they are used to mark the fixed assets of a company.
for the moment I will add only the ranges as I receive them.
I thank you too much.

[Joseph_Seddik]

Regarding the design, if the maximum range any user would get is 300 labels for example, but you want to differentiate the ranges, have you thought about adding a prefix that is unique for each user, instead of differentiating them in orders of magnitude?

[jdaf_2412]

Sorry, I don't understand the alternative you propose.
The end of this is:
User A is given the labels 1-100 (unique and unrepeatable), it happens very frequently, that due to printing errors or by the user in the field, they do not put or skip the consecutive ones delivered, and they realize once the work, which is very difficult to correct physically.

[Joseph_Seddik]

OK, I see thank you.

Here's a recommendation for how to deal with this. What you can do is separate the package number from the label number. What you actually have is a package number followed by a label number. The 200K labels are distributed among 2000 packages, each having 100 labels.

If you add a "Package" column in your Formulario, linked to Packages table, you'll be able to easily select, or assign beforehand the right package number to the user, then let the user only select the among the Label numbers available to him. Afterwards you'll be able to have a list of unused label within each package for each user. 

If such solution would respond to your need, please tell me and I will tell you in detail how to implement it. Thanks. 

[jdaf_2412]

Of course yes! It changes the shape a bit, but the result and control would work perfectly, I appreciate your help.

[Joseph_Seddik]

Sure. Would you please give me more details about how the numbers are formed? for example, is it always xxx000 to xxx100? What are possible combinations? Thank you.