Need help to Analyze, 1st 2nd and 3rd top scores in 30 separate Classes

[GearheadFest]

Hey everyone! I am a newb, but I am very frustrated and have decided to come to you all for some help! I have been working on this particular spreadsheet, and evolving it for the last 3 years. I am very basic with formulas, I usually do well when I can read through what has already been done, to apply it to my data. So starting from scratch is a challenge for me. I have watched TONS of you tube videos and googled and swept through a ton of websites, I even figured out a great alternative with macro, but didn't save it properly and lost HOURS of work. Even with the skills I have gained over the last year I am having a lot of trouble applying the knowledge to my personal spreadsheet.

For the sake of my sanity could someone please take a look at my spreadsheet and help me figure out what I need?(i just figured out that i cant attach it.)


I have looked and tried to play with the LARGE, IF, MATCH, INDEX functions, but its not pulling the right info, or I'm not properly routing the functions.
Currently the Master info list and the judging scorecard sheet talk to each other to pull the scores/registration numbers to match so my master list shows the proper scores, which is perfect for that function.

What I need: I need the MASTER INFO LIST to communicate over to the WINNERS sheet. ON the WINNERS sheet I need the top 3 scores to populate in the appropriate class from the MASTER INFO LIST. I also would like the Name, Year, Make, & Model to populate over to the winners list also. Then god forbid there are ties, I'm not sure how to separate those, but I would assume it could be an IF formula?

I used class #19 as a sample on the spreadsheet... so on the WINNERS sheet, in CLASS 19 I would like to be able to see the following data auto populated:
[TABLE="width: 1197"]
<tbody>[TR]
[TD="align: right"]19[/TD]
[TD]HYUNDAI/KIA/SCION[/TD]
[TD]SCORE[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD] Allison Cline 2016 Kia Optima[/TD]
[TD]92.0[/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD]Jessica Hecker 2013 Hyundai Sonata[/TD]
[TD]81.0[/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD]Frank Hecker 2015 Hyundai Genesis[/TD]
[TD]50.0[/TD]
[/TR]
</tbody>[/TABLE]


Any Help would be GREATLY APPRECIATED!!! <3

 

[cerfani]

there are websites that will build tables that you can paste into your posts.

Probably with examples of the data from the sheets you need to work with will help someone help you. You probably won't get a response with your current post.

Actually check out all the options when writing a new reply... go into the advanced view and you can create a table here.

 

[GearheadFest]

http://s33.photobucket.<wbr>com/user/JConover/media/excel%<wbr>20help%202.jpg.html][/URL]
http://s33.photobucket.<wbr>com/user/JConover/media/excel%<wbr>20help%201.jpg.html][/URL]


im sorry-- im trying really hard not to sound/look like an idiot but this is what I could come up with? Let me know if i need to use different links?
I have tried all 4 different url/codes and none of them will populate the images...

 

[GearheadFest]

there are websites that will build tables that you can paste into your posts.

Probably with examples of the data from the sheets you need to work with will help someone help you. You probably won't get a response with your current post.

Actually check out all the options when writing a new reply... go into the advanced view and you can create a table here.

Thank you for letting me know... I guess i was assuming i would be able to attach the document, or maybe some kind soul would have me email it to them lol

 

[cerfani]

actually i just tried making a table with the advanced post editor and it doesnt work well. You can build a table but you cant copy paste from excel. I will find you a good link.

http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

do what Aladin does.

 

[GearheadFest]

http://www.mrexcel.com/forum/test-here/950556-test-here-table-issues.html#post4565698

[TABLE="class: grid, width: 917"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]REG #[/TD]
[TD]FULL NAME:[/TD]
[TD]YEAR:[/TD]
[TD]MAKE: [/TD]
[TD]MODEL:[/TD]
[TD]CLUB AFFILIATION:[/TD]
[TD]CLASS ASSIGNED:[/TD]
[TD]JUDGE[/TD]
[TD]OVERALL SCORE %[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jessica Hecker[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Sonata[/TD]
[TD]gearhead[/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Travis[/TD]
[TD]81.0%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]kyle conover [/TD]
[TD="align: right"]2005[/TD]
[TD]scion [/TD]
[TD]tc[/TD]
[TD] [/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Travis[/TD]
[TD]31.0%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]allison cline[/TD]
[TD="align: right"]2016[/TD]
[TD]kia[/TD]
[TD]optima[/TD]
[TD] [/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Josh G[/TD]
[TD]92.0%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]frank hecker[/TD]
[TD="align: right"]2013[/TD]
[TD]hyundai[/TD]
[TD]genesis[/TD]
[TD] [/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Jason[/TD]
[TD]50.0%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]marilyn conover[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]accent[/TD]
[TD] [/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Jimmy[/TD]
[TD]19.0%[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 1113"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]19[/TD]
[TD="colspan: 4"]HYUNDAI/KIA/SCION[/TD]
[TD]SCORE[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[GearheadFest]

http://www.mrexcel.com/forum/test-here/950556-test-here-table-issues.html#post4565698

[TABLE="class: grid, width: 917"]
<tbody>[TR]
[TD]REG #[/TD]
[TD]FULL NAME:[/TD]
[TD]YEAR:[/TD]
[TD]MAKE:[/TD]
[TD]MODEL:[/TD]
[TD]CLUB AFFILIATION:[/TD]
[TD]CLASS ASSIGNED:[/TD]
[TD]JUDGE[/TD]
[TD]OVERALL SCORE %[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jessica Hecker[/TD]
[TD="align: right"]2013[/TD]
[TD]Hyundai[/TD]
[TD]Sonata[/TD]
[TD]gearhead[/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Travis[/TD]
[TD]81.0%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]kyle conover[/TD]
[TD="align: right"]2005[/TD]
[TD]scion[/TD]
[TD]tc[/TD]
[TD][/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Travis[/TD]
[TD]31.0%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]allison cline[/TD]
[TD="align: right"]2016[/TD]
[TD]kia[/TD]
[TD]optima[/TD]
[TD][/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Josh G[/TD]
[TD]92.0%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]frank hecker[/TD]
[TD="align: right"]2013[/TD]
[TD]hyundai[/TD]
[TD]genesis[/TD]
[TD][/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Jason[/TD]
[TD]50.0%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]marilyn conover[/TD]
[TD="align: right"]2014[/TD]
[TD]Hyundai[/TD]
[TD]accent[/TD]
[TD][/TD]
[TD]19. hyundai/kia/scion[/TD]
[TD]Jimmy[/TD]
[TD]19.0%[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 1113"]
<tbody>[TR]
[TD="align: right"]19[/TD]
[TD="colspan: 4"]HYUNDAI/KIA/SCION[/TD]
[TD]SCORE[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need: I need the MASTER INFO LIST to communicate over to the WINNERS sheet. ON the WINNERS sheet I need the top 3 scores to populate in the appropriate class from the MASTER INFO LIST. I also would like the Name, Year, Make, & Model to populate over to the winners list also. Then god forbid there are ties, I'm not sure how to separate those, but I would assume it could be an IF formula?

 

[cerfani]

You should write a subroutine in VBA. You would loop through each row on the masterlist using vba collection objects to create a unique list of classes and then here is the abstract part... Then you create a collection of collections :D ... so each object in this collection is a collection of scores. And the collections of scores in the collection holding it is in the same order as the list with collection of class names so you can simply pull scores and associated class by the same index value. Alternatively a class with named properties for the collections can be created to make the programming easy.

It sounds tricky but i can help if you need vba code help.

Of course you aren't done but now you will have all your data organized and you use a loop through the collections to write it wherever you want.

Also where you write scores can be offset from the location of the class name on all those tables. So you just have to search for the range where your class names are and the offsets where scores are written is a constant

 

[GearheadFest]

i would love some vba code help! i have been watching more videos and trying a few things and still struggling!!

 

[cerfani]

Here is my advice since i had to learn myself via google...

videos wont help you learn anything... they aren't a waste of time but i would spend my time writing code and referring to places like stackoverflow for any little question or here (any problem you will have somebody already asked)

this is how you learn vba

1. you write code referring to tutorials like you are blind and asked to be led through the dark (google is leading you)
2. you trip over every little step and it takes you 10 hours to navigate something it would take you 5 minutes with the lights on (your code will produce many errors and wrong calcs but you will fix it)
3. When you do this a bunch... your brain will automatically learn through process of repetition even if you dont understand what ur doing, the more you do something the more questions get created and knowledge is created from seeking out the answers.

So the only way to learn vba is turn off the video and start writing this code... AND then when you hit a wall... you start googling

if you have never programmed before then a lot of the concepts i just explained are going to be incredibly foreign to you. VBA is a tough language to use to learn programming because vba is a horribly outdated language and the vba editor is trash compared to other language IDEs. I am waiting for the day when visual studio gets integrated into excel and replaces the vba editor and you can just write .net code. But whatever you are stuck with VBA.

Let me read your question and I will try to help.