Using Excel to Sort and display Contest results for different categories

gakuffs

New Member
Joined
Aug 15, 2020
Messages
2
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
I am extremely new to any complex workings or Excel. I know how to create a simple spreadsheet and how to manually sort the columns while looking at that spread sheet. I am looking for assistance in how to create a document for contest results. The goal is to be able to enter the data on one sheet and on a separate screen have it autamatically sort the differnt categories buy the top X scores in each with the Team number, Entrants Name as well as the score. I am also unsure if I have to have two separate files or if I can display a sheet within the original file on a separate computer screen.

This is at least my hopes as it is for a volunteer Fire Deparment and a contest they hold yearly.

If anyone has any suggestions or can help I would greatly appreciate it. Some examples of the original spreadsheet and then the data I am hoping to display (and have it update automatically when new data is entered on original sheet) are attached. Also open to ideas for alternative data entry as the scores for the competition are entered all at once for each team, not sure if there are any easier formats to enter the data.
basic original spreadsheet.jpg
Data to be sorted and automatically displayed.jpg
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
ABCDEFGHIJKL
TeamNamecategory 1category 2category 3category 4category 5category 1
1​
max
1.1​
placeteamnamescore
2​
annely
20​
1​
1​
max
1.1​
3​
bruck
0.2​
2​
3​
bruck
0.2​
4​
mark
2.1​
2.1​
2.1​
3​
4​
mark
2.1​
5​
todd
4​
6​
fish
3.5​
6​
fish
3.5​
3.5​
5​
10​
ashley
100​
7​
ted
10​
6​
11​
jim
90​
8​
james
8​
13​
steve
17.1​
9​
tom
9​
16​
katy
80.3​
10​
ashley
100​
10​
4​
mark
2.1​
11​
jim
90​
30​
20​
11​
22​
harley
10.7​
12​
otis
13​
steve
17.1​
17.1​
J3=XLOOKUP(K4,$B$3:$B$25,$A$3:$A$25,,0)
14​
smithK3=FILTER($B$3:$B$25,$C$3:$C$25>0)
15​
taceyL3=XLOOKUP(K4,$B$3:$B$25,$C$3:$C$25,,0)
16​
katy
80.3​
71.3​
17​
hum
18​
ed
19​
marc
15​
20​
mark
60.3​
24.1​
21​
dog
22​
harley
10.7​
10.7​
23​
gunner
25​
30​
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
with Power Query and Pivot Table
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Team", "Name"}, "Category", "Score")
in
    UOC

SourcePivot
TeamNamecategory 1category 2category 3category 4category 5CategoryTeamNameScore
1max1.1category 11max1.1
2annely203bruck0.2
3bruck0.24mark2.1
4mark2.12.12.16fish3.5
5todd10ashley100
6fish3.53.511jim90
7ted1013steve17.1
8james16katy80.3
9tom20mark60.3
10ashley10022harley10.7
11jim903020category 24mark2.1
12otis6fish3.5
13steve17.117.116katy71.3
14smith20mark24.1
15taceycategory 32annely20
16katy80.371.311jim30
17hum23gunner25
18edcategory 47ted10
19marc1511jim20
20mark60.324.119marc15
21dog23gunner30
22harley10.710.7category 54mark2.1
23gunner253013steve17.1
22harley10.7

if anything will be changed in source data table just refresh Pivot Table
Note: XL 2007 doesn't support Power Query and newest worksheet functions
 
Last edited:

gakuffs

New Member
Joined
Aug 15, 2020
Messages
2
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

with Power Query and Pivot Table

Note: XL 2007 doesn't support Power Query and newest worksheet functions

Which version would support those functions? I have other versions on other devices, I need to update my laptop though.

I am having difficulty with XL2BB , I downloaded it but can not seem to find it in list of add ons in excel.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
XL365 and higher for mentioned function (I am not sure about XL2019)
XL2016 and higher for Power Query
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

XL365 only (for today)
 

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
XL365 only (for today)
actually i was not notice what version he is using....!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top