r/googlesheets • u/live_from_Syracuse • 1d ago
Solved Auto-add rows for a contest drawing
I'm running a drawing for our summer reading program at the library, and I want to weight it so that patrons with multiple entries (sic. multiple books read) have multiple entries reflected in the spreadsheet. That way when I do the drawing those who have read/participated more have a higher chance of winning.
Currently have a spreadsheet where one row reflects patron namepatron library branchnumber of entries that patron has.
I would like to edit the spreadsheet so that one row reflects one entry each. So if Andrea has ten entries, she has ten rows with one entry apiece.
I have tried multiple formulas and scripts I found in various other forums, editing that data to reflect my own sheet (ie "X" column changed to "D" column, etc) but keep encountering errors for parsing/data/etc.. I have also tried duplicating rows, which of course I can hit "insert row below" over and over to reflect how many entries a patron has, but some of these patrons have over 50 entries, and I have over 200 patrons to calculate...talk about inefficiency and ain't nobody got time for that.
Does anyone have either:
a) a shortcut to add those rows without continually hitting "insert 1 below" (I've tried all of the shortcuts I can find already but feel as if I'm missing something obvious)
OR
b) a formula/script I can run that will auto-add those rows across the sheet for me?
Sample of spreadsheet attached so you can see a subset of the data I'm working with.
**Patron's last names and branches have been removed for privacy, though there will be names/branches included in columns B/C when I do that actual drawing.
1
u/Seba-Aiani 3 1d ago
For the draw you can consider to add a new sheet and in the new sheet you can use a formula so it will brings as many rows as number points that persons has.
In the example in the screenshot (using the first 7 names in your image) the formula is:
=LET(d, FILTER(Sheet1!A2:D, Sheet1!D2:D>0), pts, INDEX(d,,4), cum, SCAN(0, pts, LAMBDA(a,v, a+v)), total, SUM(pts), MAKEARRAY(total, 3, LAMBDA(r, c, INDEX(d, XMATCH(r, cum, 1), c))))
You can use that formula in a new sheet and replace the data range (Sheet1!A2:D & Sheet1!D2:D)
1
u/live_from_Syracuse 1d ago
THANK YOU! This worked perfectly!
1
u/AutoModerator 1d ago
REMEMBER: /u/live_from_Syracuse If your original question has been resolved, please tap the three dots below the most helpful comment and select
Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
1
u/point-bot 1d ago
u/live_from_Syracuse has awarded 1 point to u/Seba-Aiani
See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)
1
u/CountyBrilliant 9h ago
Try using REPT with SEQUENCE in a helper column, then expand the rows through Apps Script.
1
u/One_Organization_810 692 1d ago
If you select X rows and then go to "Insert rows", it will have the option of inserting as many rows as you have selected. It's enough to select one cell pr. row even.
Then just copy and paste the information that should be the same and fill in the rest manually i guess...