•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to capture 1st and 2nd place, accounting for ties
Our take
Are legacy spreadsheets making it tough to track real-time results in your board game? You’re not alone. Automating your scoring system can elevate your game nights by accurately capturing first and second places while smoothly handling ties. With a few smart Excel functions, you can seamlessly group tied players and ensure your leaderboard reflects the action. Dive in to discover how to simplify your data organization, allowing for a more enjoyable and engaging experience for everyone at the table!
| A | B | C | D | |
|---|---|---|---|---|
| Mike | 1 | 2 | 4 | 7 |
| Fred | 4 | 1 | 4 | 6 |
| George | 3 | 6 | 4 | 6 |
| Julie | 7 | 3 | 6 |
So I'm trying to "automate" a spreadsheet to accompany a board game. I need to track who's in first and second place, while accounting for ties... so for each of the columns, I'd need the following results:
A:
| A | |
|---|---|
| 1 | Fred |
| 2 | George |
B:
| B | |
|---|---|
| 1 | Julie |
| 2 | George |
C:
| C | |
|---|---|
| 1 | Fred, George, Mike |
| 2 |
D:
| D | |
|---|---|
| 1 | Mike |
| 2 | Julie, George, Fred |
All table formatting by ExcelToReddit
All of my original data is captured in a single grid (like in my example), and I'm breaking out each "column" into a separate grid, currently sorted by that column value... basically, I just need to figure out how to combine "duplicate" values (all of the first place owners together, all of the second place owners together) to account for ties. The order of the names within the placements is immaterial (doesn't necessarily need to be alpha within the specific rank).
In the case of a tie for first, there would be no second place.
There will be another column on the right to show the "payout" for each placement, but I'm pretty sure I can figure out how to get the payout correct once I've got the correct number of names for each place.
Running 365 on Windows, if that changes anything.
Any thoughts?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Working on creating a formula to use information across two cells to determine calculations in other cells(reposted first post got removed) I'm not certain the IF formula(s) are what I need but I'm not sure what else to use. Trying to create a spreadsheet for work: the premise is that if one or two people are the Contact for a project, they will split 5% of the project's earnings, each getting 2.5%; if only one person, they get 5%. The same for if one or two people who are the Winners for the project. I need some way for the spreadsheet to be able to see that if someone's initials are under either Contact or Winner, to then give them either 5% of the net income if they are the only Contact or only Winner, or 2.5% if they share either spot with someone else. The total amount of the net income given out as a bonus will always come to 10%. The first picture shows my 'backend' sheet and a formula I was trying that would calculate 2.5% of the Net Income if someone's initials showed up on the project, but this doesn't work if their initials only show up once because then they would need to get 5%. I would also hope there would be a less clunky way to do this many calculations. The second picture is a section of the main page of the sheet showing the Contact and Winner columns, the Net Income the bonus comes out of, and then the Total C/W amount under everyone's initials that adds up their total bonuses. Backend sheet, 'points' refers to first sheet First sheet of spreadsheet Please let me know if I have not been thorough enough with explaining what I'm trying to do, I'm so deep in this now that I am really really confused and just need help straightening this all out. Using newest version of Excel on a macbook, have also been working on same spreadsheet in Windows. I'm not a beginner at excel but not all that good either. TYSM in advance. submitted by /u/Financial_Device7400 [link] [comments]
- How to organize a sheet based on how many times a certain value in a column is duplicated, and have all other columns follow?Hope the title is descriptive enough... i feel like i always struggle to describe excel stuff efficiently. I am a complete excel beginner! So i have a dataset that is 3000+ rows long. For the sake of ease (and also to not share PHI), I made a shortened 27-row long example; this is what is shown in the screenshots. The data I'm working with is downloaded from a website we use to give people questionnaires. Sometimes, people do not have very straightforward answers to each question, so we type in "comments" in those cases to help clarify the exact answer participants gave. The data I'm working with lists the ID number we gave to each participant; the "variable" AKA the name of the question (tells me exactly which question it is in our questionnaire); which "session" AKA appointment the question is from (we repeat the questionnaire multiple times per participant throughout a year, some in-person, some over the phone); which coworker left the comment (commenter); and finally the actual comment itself. We are trying to see which questions/variables were given comments most often. This is a replica of what I have: https://preview.redd.it/9lvlnwplfgng1.png?width=1025&format=png&auto=webp&s=4716f993d500383af6307b8fb1724fef5b8626e1 This is what i WANT it to sort of look like: https://preview.redd.it/3d4w5a6ofgng1.png?width=980&format=png&auto=webp&s=dd32c658aceadd0afe5c427f0ec19177226d5081 In other words, I want: A count of how many times each type of variable repeats in the excel file. the dataset to be organized from which variable appears the MOST often at the top, and the ones that show up the LEAST at the bottom. to get rid of all rows that were for a "test" participant (notice the "test" rows 8, 18, and 26 in the first screenshot are gone in the second). to be able to find an exact question from the questionnaire website based on this sheet. for example, if I wanted to look more into the context behind row 5's comment, I would know to go to our questionnaire website, go to participant 111's questionnaires from the Lab 1 appointment and specifically look at the alcohol_amount1 question. In other words it is important to keep the participant number and session information. It doesn't need to look exactly like the second picture, that was just the first way to organize it that came to mind. As long as it fills the above requirements that all I need that's what matters. I was trying out pivot tables but I couldn't really get it to look in a way that made sense to me. I really don't know what else to do besides comb through all 3000+ rows one by one... sorry if any of this doesnt fit the exact posting rules. I tried. thanks for any help in advance🥹 submitted by /u/soupysyrup [link] [comments]
- Returning the second result from MATCHI've recently made a hobby of tracking winrates in shows I watch. I'd like to add a rankings table at the end of my spreadsheet, but as seems to have plagued many people, the MATCH function only ever returns the first match in the case of a tie. https://preview.redd.it/33j6k5ac2rhg1.png?width=1366&format=png&auto=webp&s=cdb253e5be0c9559ce9632a185ebd38cf44b8b86 The gold-colored table functions using the retrieved cell addresses in the boxes on the right. As long as those cell addresses are correct, the table will function. I would like to tie-proof #2 and on so that they don't return the same value twice. I've been at this for a couple days now, but all the other solutions I've found have been extremely specific to the spreadsheet that the OP is using. I'm working in Excel 2019. Link to the spreadsheet here. submitted by /u/Xelrog [link] [comments]
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#rows.com#big data management in spreadsheets#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#conversational data analysis#Excel compatibility#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#real-time data collaboration#automation in spreadsheet workflows#intelligent data visualization#no-code spreadsheet solutions#first place#second place