Hey Adam thanks for the amazing content this is fantastic! Did you ever make the part 2 for this? I want to be able to rank the athletes on a specific date (so I don’t have my fastest athlete take up the top 10 spots for example). Thanks in advance!!
Hi Colin. I cover how to do this in a more advanced tutorial on my website. It is accessible to members and can be found by navigating this page (if you click the link to view the tutorial and you are not signed in and/or are a member, you will not be able to access it): adamvirgile.com/members-paradise/
Adam, these tutorials are awesome. Keep up the great work. Question for you: What if the column you are pulling data from contains text? Is there a way to modify SORTN() to ignore cells with text values? Thanks
Hi Zach. That's a great question. You can specify the columns you want to bring back by using the curly brackets { } and separating the columns of interest using commas. For example, if you wanted to just bring back columns A and C, you would write: =SORTN({A:A, C:C}.....[rest of formula goes here]). If you have a column that contains text and numeric values, you have two options: 1. Change the text values to numeric values and/or remove the text (e.g., leave blank). 2. Add a FILTER function inside of the SORTN function to only include values >0 (if you do this, the rows of data with text will not be included in your sorted list). I hope this helps! Thank you.
Hi! Thank you for your kind words. There certainly is. There are a few steps: 1. Have a cell whereby you enter the age of the athletes of interest. You can also enter the specific age of interest directly into the formula below - this will make the leaderboard more dynamic. Let's say the cell you choose for the age entry is cell A1. 2. Use the FILTER function inside of the SORTN function to filter the data for only what you type into cell A1. Let's say that your ages are stored in column A. - Cell for age entry = A1 - Ages stored in column A Using my example at 7:24 in the video: =SORTN(FILTER({A:A,B:B,C:C},A:A=A1),10,FALSE,3,FALSE) I hope this helps! Thank you.
Awesome Adam, thanks so much! Wouldn't it be better to format the data sheet as a tabel so it takes in everything you enter later, reflecting itself in the leaderboard?
Hi Filip. That's a great observation. If I was in MS Excel, I would format the data source as a table. Google Sheets does not support table functionality. The way that we created this leaderboard accommodates for additional entries in the data source (e.g., the "Data" sheet). I hope this helps. Thank you!
I was serious, could use the help. I know how to drag a formula all the way down. But for the life of me can't figure out how to do it quickly, and in baseball with sometimes 1000 or more makes it can take awhile
Hi Adam, Love your stuff! I just had two questions regarding the leaderboards that I've run into: 1.) Is it possible to do a tie? Where instead of having athletes that have the same testing value pop up as 9 & 10, they pop up as T-9, T-9? 2.) When recording broad jumps into the chart, I have had issues with the double digit inches values not sorting accurately. Seems to prioritize the 1st number ( 6'2" would be greater than 6'11" because 2 is greater than 1). Have you ever run into this? Is there a solution? Thanks!
Hi Alex. Thank you for your kind words, and it is great to e-meet you! 1) Yes, there are a few ways to do this. I have leaderboard tutorials on my website where we go through this, here: adamvirgile.com/members-paradise/. 2) This is because Google Sheets doesn't treat those values as numbers. The simples solution would be to calculate inches and display those. If you wanted to display the ft'in", you could add an extra column inside the { } within the SORTN function. You would display the inches and the ft'in" values, sort by the inches value, and hide the column with the inches values (so it is not seen). That's the simplest solution I can think of right now. Thank you! Great work.
Does this not work if the data is calculated from a formula as well? For example, I have 3 jump columns with another column taking the MAX, but when I reference the MAX column, it pulls the names and dates but not the value.
It certainly will work with calculated fields. It is difficult for me to diagnose from afar. What happens when you flip the sort around? I did not integrate the FILTER function in this video (which you would likely want to integrate if you're using a calculated field to remove blank values from the leaderboard). If you see the lowest values when you flip the sort around, this is the likely culprit. You would want to change the formula I wrote: =SORTN({Data!$A$2:$A,Data!$B$2:$B,Data!$C$2:$C},B5,FALSE,3,D5) to: =SORTN(FILTER({Data!$A:$A,Data!$B:$B,Data!$C:$C},Data!$A:$AData!$A$1,Data!$C:$C""),B5,FALSE,3,D5) What we're doing is we're saying that we want to sort columns A, B, and C from our Data tab, but want to exclude cell A1 (e.g., the header row) and when row C is blank. I'm not sure if this will solve your issue, but let me know if it does. If it doesn't please share your sheet with me (adam.virgile@gmail.com) and I'll let you know why it isn't working.
copy and past doesnt work. The reverse sort doesnt work. rank numbers are not in ascending or descending order. I changed the data ranges. used true or false or nothing. only my first set of metrics works.
There are various ways. The way that I would do it is as follows: 1. Let's assume that you have a column in your database that includes "M" for Males and "F" for Females. Let's say this is column B in Sheet1. 2. Create a dropdown list (Data Validation) and include "M", and "F." Let's say this dropdown is in cell C1. 3. Add FILTER criteria to say Sheet1!B:B = C1. When you change "M" to "F", only athletes with those designations should show up. 4. EXTRA.... you can add an IF statement within the FILTER criteria to say "if cell C1 is blank, give me all athletes, but otherwise, give me only the athletes whose letter in Column B in Sheet1 align with the letter in cell C1." I'm not going to get into semantics here, but I frequently use this strategy in case I want to see all athletes at once. I hope this helps! Happy Holidays.
