Randbetween formula with no duplicates

[juanpa]

Hi,

I want to create an expression that assigns a random position based on the total number of subscribers in a competition with no duplicates.

The formula I’m using (virtual column) is the following:

RANDBETWEEN(1,COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID])))

It assigns the position but almost always if I have let’s say 5 subscribers in a competition, some of them have the same position.

Is there any way to assign a different position to each subscriber?

Thanks!

[LeventK]

@juanpa
As already opposed by the function itself, RANDBETWEEN function produces random numbers between a minimum range (i.e. in your example it’s 1) and a maximum range (i.e. in your example it’s 5). As the interval between the min. and the max. range is too close to each other, it’s very likely that it will produce doubles. Provided you can explain how important this number for you, how many digits max. you want to generate/see, I can propose a bit more robust solutions. For example:

NUMBER(
    CONCATENATE(
        RANDEBETWEEN(1000, 9999),
        RANDBETWEEN(1,COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID])))
    )
)

[juanpa]

@LeventK thanks for answering.

I tried the expression but I get the same problem. Here’s the screenshot:

Now, about the number. Yes, the number is very important because as I mentioned, I’m building a competition app so each subscriber should see his position to know when is his turn.

This is what I get now:

And this is what I need:

This example has 5 subscribers but it could be 60. So I need to assign each one a position between 1 to 5 or 1 to 60 depending on the number of subscribers in the competition (Trial).

[LeventK]

Provided your app has multiple users entering data, then I’m afraid there’s no way to provide a sequential numbering in any order or form as 2 users entering data at the same time have the probability of having the same number.

[LeventK]

May I ask how and by whom are those competitor records are created? By competitor themselves or only you?

[juanpa]

The competitor is the person who adds records using an app created for them.

[LeventK]

@juanpa
This could be one option that I can come up with. @Steve is there a better option or is it possible to shorten the expression do you think?

INDEX(
	TOP(
		SORT(
			{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
			FALSE
		),
		COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
	) - 
	SELECT(
		Punctuation[Position],TRUE
	),
	RANDBETWEEN(
		1,
		COUNT(
			TOP(
				SORT(
					{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
					FALSE
				),
				COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
			) - 
			SELECT(
				Punctuation[Position],TRUE
			)
		)
	)
)

[juanpa]

@LeventK I really appreciate your time and help. I tested the expression but it keeps adding duplicates positions (5) . Here’s the screenshot:

…

I’ll keep trying to solve this. I hope @Steve could also help me with this expression …

[LeventK]

@juanpa
I’m afraid there isn’t a robust solution with this sequantiality. You may want to read it here as well. You column is not a key but the overall idea is the same.

help.appsheet.com

Sequential Keys

A technical rationale for why AppSheet can't support sequential keys.

[LeventK]

@juanpa
Can you also try with this?

IF(
	COUNT(SELECT(Punctuation[Position],TRUE)) = 
	COUNT(
		TOP(
			SORT(
				{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
				FALSE
			),
			COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
		)
	) - 1,
	ANY(
		TOP(
			SORT(
				{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
				FALSE
			),
			COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
		) - 
		SELECT(
			Punctuation[Position],TRUE
		)
	),
	INDEX(
		TOP(
			SORT(
				{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
				FALSE
			),
			COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
		) - 
		SELECT(
			Punctuation[Position],TRUE
		),
		RANDBETWEEN(
			1,
			COUNT(
				TOP(
					SORT(
						{1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 75 , 76 , 77 , 78 , 79 , 80 , 81 , 82 , 83 , 84 , 85 , 86 , 87 , 88 , 89 , 90 , 91 , 92 , 93 , 94 , 95 , 96 , 97 , 98 , 99 , 100},
						FALSE
					),
					COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID]))
				) - 
				SELECT(
					Punctuation[Position],TRUE
				)
			)
		)
	)
)

[juanpa]

It says COUNT function is used incorrectly …

[LeventK]

@juanpa
Totally my bad, do apologize. Forgot to enclose the COUNT with SELECT on top. Fixed the expression in my prior post, so please try with copying the new one from there.

[juanpa]

@LeventK tested. It keeps adding duplicates:

[LeventK]

@juanpa
Can I look into your app for this? If so, please add levent@able3ventures.com as a co-author to your app and also please share the back-end gsheet as well with Edit access. Thnx.

[juanpa]

I just shared with you the app and back-end. The table name is punctuation and the name of the virtual column I’m adding the formula is Random position. Please let me know if you need anything else.

[LeventK]

I believe there’s a problem with the app sharing. I cannot see the app under my co-authored apps pane. Email received but when I click the Edit link, it opens AppSheet’s Sample App page. Can you please check from Users pane that the email address I have given has editable access to your app definition? Thnx.

[LeventK]

@juanpa
I can open the app from the browser only, cannot edit app definition. Please check my role from the Users pane it shall denote that I can edit app definition

[juanpa]

Please try now.

[LeventK]

@juanpa I can access now. Shall I be looking to Punctuation table and [Starting Position] column? Please guide me.

[juanpa]

Punctuation table [Random position] at the end

[LeventK]

@juanpa
This is a virtual column and this expression cannot achieve the result. This expression is suitable for the initial value or appformula of a physical column in the sheet i.e. [Starting Position]. Can you please clarify your purpose?

[juanpa]

The starting position column adds the position of the subscriber as soon as he subscribed (no duplicates) but sometimes the user (handler) can subscribe 2 or more times to the competition because he can have more than one dog (competitors) but there is a rule that the same handler can’t have 2 dogs one after the other so the Random position column is to have aleatory positions in case this happens this way I can reorganize all competitors without doing it one by one.

[LeventK]

Does this mean that;
For any Trial ID, a handler name can appear more than once but with different Dog then the Starting Position for this handler shall not be consecutive??

[juanpa]

Exactly

[LeventK]

juanpa:

The starting position column adds the position of the subscriber as soon as he subscribed (no duplicates)

How do you assign the number in [Starting Position] column with the subscription?

[Suvrutt_Gurjar · Accepted solution]

If I may join the discussion thread. Since the requirement is very interesting, tried to work on an approach. Please explore if the below meets the requirement. Even if not in your case, I hope it may be useful in some other requirement.

Create 3 VCs

1. [HelpSetOrder]
   IFS([_thisrow].[Key]=[Key], RANDBETWEEN(MIN(Table Name[_rownumber]), MAX(Table Name[_rownumber])))
   This column creates a different random number for each row. However, this column can have duplicates produced by the RANDBETWEEN() function

2. [OrderBy Random]
   ORDERBY(Table Name[Key Column], [HelpSetOrder], TRUE)
   This column sorts the list of keys by [HelpSetOrder]. The main crucial point is since this is ORDRBY() column, it sorts the keys without duplication, even if the sort order input column has duplicates.

3. [Position]
   INDEX([OrderBy Random][_ROWNUMBER], [_rownumber]-1)
   This column attaches [_RowNumbers] to the randomly ordered key order in step 2 above and assigns one randomly sorted rownumber value to each row through INDEX().

Currents Status: The sort order changes with each sync as VCs are involved. Will need some expression enabling logic through say action etc. to prevent the expressions kicking in during each sync.

In the picture below the column [HelpSetOrder] has duplicates, highlighted in red. However, the final [Position] column is randomly ordered with unique values or no duplicates as per requirement for each row, as highlighted in green.

[juanpa]

Hi @Suvrutt_Gurjar it works!

I just made a change to the last formula:

[Position]
INDEX([OrderBy Random][_ROWNUMBER], [_rownumber])

Here’s the screenshot:

Thank you both @Suvrutt_Gurjar and @LeventK for your help. I really appreciate that.

[Shulet415]

So I was able to generate 1-6 random - non-repeating numbers through a 2-step (2 column) process that was pretty easy.  
In Column B1, I input the formula (=UNIQUE(RANDBETWEEN(1,6)).   I dragged that down to row B150 to ensure that all numbers 1-6 would show up at least once.   Then In Column A1, I input the formula (=UNIQUE(B1:B)).  This produces the numbers 1-6 in Column A in the random order they appear in Column B.