Part 7 – Getting rid of duplicates – thanks AI

Catching up

I finally figured this out . . . sort of. I had quite a bit of help with ChatGPT but more on that later. First, let’s catch up. My school holds a field day we call it the Olympics. Students are gathered into teams (countries) and there are usually 12-15 stations they rotate through. At those stations they perform tasks and either earn points or their time is recorded and then points are assigned to the top 3 countries.

First – if you want to read how I built this thing, then head over to our IT Babble page dedicated just to that: https://itbabble.com/olympic-scoring-with-google-forms-sheets/

Our PE department runs this event and do a damn fine job at it. Before, people would record the results on paper, then turn the papers into the teachers who would calculate it. The problem, you can guess, is that it leads little room for them to do anything else. They are constantly calculating and then at the end of the day they announce the winning teams.

I came up with a system where the person running the station completes a Google Form and that data then feeds into a Google Sheet where it calculates the winners for each event, assigns that country points (based on their status) and then tabulates each countries points and declares a winner.

Problem

That is a mouthful – it for the most part it worked pretty well, but there was room for improvement. The biggest issue I had was with the =vlookup function. Take a look at the sample data below.

Country	Free throws made
USA	10
Canada	8
Spain	8

Obviously there are more teams, but for this example – I’ve gone pretty simple. I would sort the points highest to lowest and then I would need to use =vlookup at some point to bring that data to another worksheet. When I would do that – it would show up like this in Google Sheets

Country	Free throws made
USA	10
Canada	8
Canada	8

It is just a limitation of Google Sheets (or any spreadsheet program for that matter). It couldn’t realize that Canada was already accounted for and it should go to the next country with the same or next highest value. If Spain had made 7 free throws (for example) it would work flawlessly, but this did lead to some small miscalculations, but most of all, the PE department would need to dig through the data to find out who the other team was. It takes a little time and is one more item they need to hold in their heads on an already demanding day.

I’ve mentioned this in other posts under this

Solution

The solution was to abandon the =vlookup formula and switch to the =sortn formula. No, that is not a typo – that is the actual formula and if you’ve never seen it before – don’t feel bad. Apparently this formula is exclusive to Google Sheets and does not work in any other spreadsheet program. If I’m wrong about this please leave a comment below.

Time for some screenshots so you can better see what I’m talking about how this thing works. Here is sample data I was using to test. Now, in another column I would like it to list the top 3 countries based on their points.

With just a quick glance you can see that Zimbabwe, Refugee and Saudi Arabia are the top three. In cell D1 I typed the header Country. Then in D2 I typed this formula:

=sortn(A2:A17,3,1,B2:B17,false)

OK – here is what that all means:

• sortn – this is the formula. It takes data from one column and sorts based on values from another column. In this case it will sort the country names based on their point values
• A2:A17 – This is the range of data that contains the country names. We use a comma in spreadsheet formulas to move to the next part of the formula
• 3 – This tells the formula to report the top 3 scores
• 1 – This says to order those top 3 from most to least
• B2:B17 – This is the range with the point value
• false – This tells the formula to order the numbers from highest to lowest

I just typed that formula in D2 as I mentioned earlier and it will fill in the top three, so no need to write three formulas for each place.

So it is working! Now let’s test it when there some of the results are the same. Here I have already typed the formula in. You will notice in column F (Points) that there is a 5, 3 and 1 value inputed. This is how many points are awarded to first, second and third place.

You may notice that there are 4 countries listed in column E. You may also be asking yourself “Why did Patrick do this?” That is a very valid question and the answer is . . . I don’t know. The formula on the sheet is the same as the other but it is returning 4 countries. I even tried deleting the formula and typing it again – still lists 4 countries. Oh well, it doesn’t affect anything so rather than worry about something inconsequential I’ll just move on.

The important part is that Guyana and Aruba have the same points (80 apiece) and they are listed separately. With vlookup I would get Guyana twice.

Another question I have is why is Guyana before Aruba? I don’t know. Maybe I entered Guyana’s data first – I am not sure and have no answer. At any point – our hard working PE teachers can look at this and know that there was a tie and can then do whatever they want with that knowledge.

It also is working on the final totals page.

We can see that Indonesia and Tonga are tied with 7 points each. Nice.

AI helping

So I have tried to figure this out many times before. I got close, usually utilizing index and match but it never quite worked so I would just give up for the time. Now this time I reached out to our friend AI friends. First I tried Google’s Gemini. I figured who would know Google Sheets better than a Google AI? Turns out a lot of people.

I first gave it some raw data to work with. It proceeded to apply weird calculations to the data. Why?

I had to instruct it not to do that. Then it gave me a Google Apps script to implement.

No.

Eventually it started to suggest the index and match formula. At this point I jumped ship to ChatGPT. I gave it the same data and explained what I wanted to do. It’s first response – index and match. After going back and forth with it for a while I remind that I was working in Google Sheets and then it returned this:

I gave it a try and knew it was exactly what I had been looking for.

So I was genuinely surprised and grateful for ChatGPT in this case. It was truly a help – Gemini, not so much. At any rate – this is project is now more accurate and I like that.

Source: IT Babble Blog and Podcast