Finding rows with common cell values

54 views
Skip to first unread message

Ross

unread,
Sep 3, 2020, 1:39:17 PM9/3/20
to google-apps-sc...@googlegroups.com

sample.png


Hey all,

I've created a spreadsheet with data input from Google Forms, with the purpose to find members with matching schedules.
Members have their course of study listed in Column A, and their schedule marked by √-marks in Columns C-I (days of the week).
I would like to have the functionality of pairing members with schedules that match 2 or more times.

I'm having a struggle wrapping my head around how best to go about this, any help would be appreciated.

My initial thought is when a new member is entered, get all previous members with matching course of study in Column A.
The new member's Columns C-I would then be compared to the previous course-matched members, but what would be the most efficient way to compare them? And what would be the best way to track having 2 or more values in Columns C-I in common?

The end goal is to have the members in common set to a temporary sheet with all cells in their row present, as the list of matches will be formatted to be emailed to the new member.

Too crazy?


Jean-Luc Vanhulst

unread,
Sep 3, 2020, 1:45:46 PM9/3/20
to google-apps-sc...@googlegroups.com
If you share a demo /sample sheet that is usually much easier to visualize what you're trying to do and then helping you?

One way to go about it could be creating Match function(person a, person b) that returns the number of matches.  (compare getrange(<c i>).getvalues() with same values from other person )
You could then go through and create your 'matrix spreadsheet' that has all the matches per person. 
(rows -> every person / cols -> every person )  cell = match(person a, b)


--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1c2fa8c6-3f89-46ff-8a3f-76c5ae790647o%40googlegroups.com.

cbmserv...@gmail.com

unread,
Sep 3, 2020, 4:18:49 PM9/3/20
to google-apps-sc...@googlegroups.com

Ross,

 

It is hard to give you advice if we do not know what the info looks like. Are these yes/no for timeslots or is it a date/time range or something else?

 

Perhaps it would be good to make a copy of your spreadsheet (anonymized) and share the link here for others to see then we can give you some pointers on how best to approach this.

 

Do specify:

 

  • How many matches are you looking for? If 4 students have the same schedule, are they set up as 2 teams or 1 team of 4?
  • If someone does not have a perfect match with another, how do you treat them?
  • Are there other factors to the matching? Like interests, gender, age, or whatever?
  • What is the result when a match occurs? Is it just to send the 2 persons an email and telling them they have been matched? Or what other action?
  • When someone has been matched, are they now excluded from being matched again when new students are added?

 

Am sure there are others with questions on this as well. Will let them chime in.

--

Serge HAROUTUNIAN

unread,
Sep 4, 2020, 3:27:25 AM9/4/20
to Google Apps Script Community
To testing matching weeks beetwin two students, I would use byte to byte operations.
The weeks columns could be transformed in interger with bytes encoding.

For exemple :
student 1 : sunday no, monday yes, tuesday yes, wednesday no, thursday yes, friday no, saturday no -> 0110100 (bin) -> 52 (dec)
student 2 :  sunday no, monday yes, tuesday yes, wednesday yes, thursday no, friday no, saturday no -> 0111000 (bin) -> 56 (dec)
student 3 :  sunday no, monday no, tuesday yes, wednesday yes, thursday no, friday no, saturday no -> 0011000 (bin) ->  24 (dec)

using binary operator "&" for comparisons :
student 1 vs student 2 : 52 & 56 = 48
1 vs 3 : 52 & 24 = 16

The forbidden results values are : 0 (no common day), 1 (only saturday), 2 (only friday), 4 (only thursday), 8, 16, 32, and 64.
Then student 1 match at least 2 days a week with student 2, but not with student 3 because of only 1 common day (tuesday).

To convert your weeks :
function getWeekCode (sunday, monday, tuesday, wednesday, thursday, friday, saturday) {
    // parameters values are 1 or 0 values following that columns C-I are checked or not
    return (sunday + (monday*2) + (tuesday*4) + (wednesday*8) + (thursday*16) + (friday*32) + (saturday*64));

Elisa B

unread,
Sep 6, 2020, 2:28:53 AM9/6/20
to google-apps-sc...@googlegroups.com

In response, I offer you my special Ross board

1) I used the checkboxes to simplify the data entry in the planning and also to simplify the sorting.
2) The 'TRI' tab allows you to sort by Course AND by Day 
3) All Formulas and MEFC are on the 'Formule_Mec' tab
4) This table is read, you can make a copy via the 'File' menu.

By Elisa B🌷  le 06 sept. 2020



Emeric HOCHART

unread,
Sep 7, 2020, 1:22:06 AM9/7/20
to google-apps-sc...@googlegroups.com
Impossible à ouvrir

image.png

Emeric Hochart
Ingénieur S.I.


JETLANE SAS - AML - DECATHLON
Site du B'twin Village
4 rue du Professeur Langevin 
59000 - LILLE



Le dim. 6 sept. 2020 à 08:28, Elisa B <7me...@gmail.com> a écrit :
In response, I offer you my special Ross board
🆘 Can someone test this link and tell me if you can open it only for reading? before I post it to the other group. Thank you. 🆘

1) I used the checkboxes to simplify the data entry in the planning and also to simplify the sorting.
2) The 'TRI' tab allows you to sort by Course AND by Day 
3) All Formulas and MEFC are on the 'Formule_Mec' tab
4) This table is read, you can make a copy via the 'File' menu.

By Elisa B🌷  le 06 sept. 2020




Le jeudi 3 septembre 2020 19:39:17 UTC+2, Ross a écrit :

sample.png


Hey all,

I've created a spreadsheet with data input from Google Forms, with the purpose to find members with matching schedules.
Members have their course of study listed in Column A, and their schedule marked by √-marks in Columns C-I (days of the week).
I would like to have the functionality of pairing members with schedules that match 2 or more times.

I'm having a struggle wrapping my head around how best to go about this, any help would be appreciated.

My initial thought is when a new member is entered, get all previous members with matching course of study in Column A.
The new member's Columns C-I would then be compared to the previous course-matched members, but what would be the most efficient way to compare them? And what would be the best way to track having 2 or more values in Columns C-I in common?

The end goal is to have the members in common set to a temporary sheet with all cells in their row present, as the list of matches will be formatted to be emailed to the new member.

Too crazy?


--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ed61c28c-d94a-45a0-829e-fc81b9e8bd6fo%40googlegroups.com.

Elisa B

unread,
Sep 7, 2020, 8:34:45 AM9/7/20
to google-apps-sc...@googlegroups.com
Hi Emeric,  I modified the link yesterday and ALL internet users can read the sheet
Elisa B
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Jean-Luc Vanhulst

unread,
Sep 7, 2020, 9:04:45 AM9/7/20
to google-apps-sc...@googlegroups.com
Will every PERSON have only one line (or can a person be in there more than once? Like for Course A and Course B?)
On Mon, Sep 7, 2020 at 8:34 AM Elisa B <7me...@gmail.com> wrote:
Hi Emeric,  I modified the link yesterday and ALL internet users can read the sheet
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2a8f7521-22d9-4b01-84e5-6fe46354a372o%40googlegroups.com.

Elisa B

unread,
Sep 7, 2020, 9:17:32 AM9/7/20
to google-apps-sc...@googlegroups.com
Hi Jean-Luc,
Si c'est un tri selon le jour, comme sur mon tableau TRI,  chaque personne n'apparaîtra qu'une fois,  à moins qu'elle ne soit dans les DEUX groupe .. 
Mais on voit quand même si elle est sur d'autres jours, sur une seule fois, donc pas en MEFC
If it's a sort by day, like on my TRI table, each person will appear only once, unless they are in BOTH groups .. 
But we still see if they are on other days, but only once, so not in MEFC
Elisa

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Elisa B

unread,
Sep 7, 2020, 9:43:02 AM9/7/20
to Google Apps Script Community
J'ai ajouté une feuille TRI2 pour faire un tri sur 2 jours en même temps
> En L1 et L2, tu choisis deux jours
> en A1 tu choisis entre Course, A, B 
> Les personnes doublées sur UN de ces deux jours sont affichées

I added a TRI2 sheet to sort on 2 days at the same time
> In L1 and L2, you choose two days
> in A1 you choose between Course, A, B
> The people doubled on ONE of these two days are displayed

Elisa B

Le lundi 7 septembre 2020 15:04:45 UTC+2, Jean-Luc Vanhulst a écrit :
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages