Dividing data into equal parts

Escape from Excel, GSheets, & SQL
#1

I have a list of randomly ordered emails. I need to divide them into equal groups so that I can later use Google Calendar action to invite them into monthly mingle event’s.
Let’s say it’s 299 rows, divided in groups of 6, last one smaller.

1 column = emails
2 column = random() + order ascending

In sheets I would hack something, potentially making frozen column with group number and then just re-ordering emails randomly. I see freezing columns is not supported yet.

What would be “coda-correct” way to do this?

#2

Hi @Landsil

I would create a column that change value based on a custom formula you decide (like if thisrow.rowid is between 0 and 50, “group 1”) that’s not a ready-to-use formula because row id could not be the best choice depending on your case, to be more precise if you delete some of the email sometimes

if you need just 6 groups you can use formulas directly in this column with an if cascade, for 300 email i think it’s not a problem in performance, but if you desire more flexibility you can try using some other way, like another table with 6 rows that “filter” the original table to groups (creating list of rows) based on the number of row, but i’ve never tried this one in particular in your use case.

This remember me that maybe you can choose those column also directly with another formula, for example if you need to send email to this addresses you can create a list directly in the “to who” in send mail buttons, or in a list made in the canvas, just by typing “=thattable.filter(your formula)” :slight_smile:

Hope i’ve been clear, in case just ask :slight_smile:

#3

Hi @Landsil,
this is an interesting question and finding a solution is far from trivial.

Let’s start few requirements to understand where we should go.

  1. Equality: Do you need an equal distribution for all the groups but one?
    e.g in the 299 and 6 group example must be [50, 50, 50, 50, 50, 49] or it can be something like [42, 50, 48, 54, 56, 50]
  2. Cardinality: The number of groups is always the same or it might be dynamic?
  3. Resiliency: once an item (the email) belongs to a group, it must remain there or it can “migrate”? (for instance if you add or remove a group)

There are obviously some strong constraints that need to be understood from your use-case.
At that point the logic might go on the mere quantity; or some intrinsic property should be taken
(e.g. see if the number of vowels in the email can be used to determine a fair distribution).

Please, let me know if I this is clear.

Thanks you.

1 Like
#4

@Mario If I understand correctly this isn’t going to work. RowID stays the same even when they are scuffled and randomness is the whole point.

@Federico_Stefanato

  1. Groups should be equal except last one, please note, 6 people per group, not 6 groups. So we are talking 50 groups.
  2. The number of groups should be dynamic to support changing number of emails but I don’t expect that to change a lot. If it changes too much we may merge last 2 groups and split in half.
  3. Emails are supposed to migrate. That’s why they are ordered via random formula. All the groups should change on every refresh.

I “think” my main issue here is that I can only apply formula to whole column.
What I think I need is.
Count rows from the top (not by rowID)
Mark every row as 1
When counts reaches designated number (6) start marking rows as previous set +1 ( so 2 in this case )

I can make a spreadsheet showing how to make it in sheets.

I can just make it fully in sheets and then push that sheet to coda via google script but that’s kinda pointless :frowning:

#5

Hi @Landsil,
thanks for the explanation, although I’m not sure I fully understood (it’s me, not you, sorry :slight_smile: )

Have a look at this example and tell me if I’m in the correct path:

Otherwise, showing your excel solution would certainly help.
Thank you.

1 Like
#6

It’s quite nice and I copied it for later for managing event’s
But it’s not random.
When I add to it random column to order by it orders only within group but doesn’t move people out.
Even when I remove grouping 1st person is always first.

My sheets example failed :man_facepalming:

#7

Using @Federico_Stefanato template you can easily make it randomic sorted, it’s a 3 minutes job

For each row you put a button that randomize a number, then you sort the rows based on that column and with the same formula you have a working random sortizer

see example here

All credits to Federico as i’ve just added 2 buttons!

#8

so wonderful, thx @Mario and @Federico_Stefanato, the slider is a smart thing in this use case, I learn from that!.

My idea is a bit different it goes in two steps

  • the main table with the email addresses
  • a second table in which the email adresses are stored that you have used.

This is the main idea, below the code:

image
image2108×1573 239 KB

You have a button that moves 6 rows to the next table and aftwards deletes them. The rows are random and this is due to the combi of Random() and Sort (from smaller to larger numbers or the inverse). Each time you take out 6 random rows (they move as you can see in the doc).

The Column finding nummers contains :

Find(thisRow,thisTable)

The checkbox looks for number smaller than 7 in the column finding via:

thisRow.finding < 7

So now we have random 6 rows that we can select and delete aftwards via:

RunActions(
[Main Email Table].Filter(Checkbox=true).email.FormulaMap(AddRow([Email storage],[Email storage].emails,CurrentValue)),DeleteRows(Filter([Main Email Table], Checkbox=true))
)

This is the formula in the button.

The page you find here :slight_smile:

I hope it helps a bit, cheers, Christiaan

#9

I’ve copied his work to my sheet with “random” column. Buttons aren’t really needed as everything changes on every action I think? ( I love button clicking buttons btw.)

But I don’t think anything changes here?
With your implementation clicking “push buttons” just randomises people within groups they are already in. Everyone still stays in the same group, that’s what I have now too.

#10

Interesting approach, this will also force user to make sure emails they are using are still correct instead of re-using last month’s data.

I will try it today.

btw. I’m positively surprised amount and speed of support in this community, so many interesting things.
I may have to come back later with private project I was trying to make more useful.

1 Like
#11

It took me a while before I could play around with Coda, it was like learning a language; all out of a sudden you can ‘speak codian’ and afterwards there is so moch to learn, to improve.

The main idea behind my suggestion is twofold:

  • you have a mother table that is bit by bit emptied while the table itself remains dynamic
  • each time you create a new row in the second table you can link it to an automation, like sending an email. I do this via Zapier and MailJet, but many other ways work as well, like via the Gmail pack.

based on the suggestion of @Federico_Stefanato I added a slider to my doc, it makes it just a more playful.

2 Likes
#12

This is really good to hear!

1 Like
#13

There was a problem in the column that was chosing the group, with this formula it works
Rank(thisRow.[Random number],thisTable.[Random number],true)

And the doc is updated!

:blue_heart:

1 Like
#14

@Landsil one approach would be to use the Remainder(); divide each RowID() by the number of groups you want and you can them separate by the remainder / modulus.

Your [Equal Group] column =

RowID(email.thisrow).Remainder(6)

#15

That now works :tada:
Wow that takes some time with almost 300 rows
It took me aaaages to figure out that for button to paste formula instead of text I have to start with = otherwise it puts text into “”.
First place where I had to do that and then if hides it too, weird UIX.

Now I just have to read those email by group into their own pockets and create events out of them, should be easy :stuck_out_tongue:

Thank you for help everyone.

2 Likes