Using Excel to Sort and display Contest results for different categories

[gakuffs]

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.

 

[sandy666]

use XL2BB to post your example data

 

[RAJESH NATH]

A	B	C	D	E	F	G	H	I	J	K	L
Team	Name	category 1	category 2	category 3	category 4	category 5			category 1
1​	max	1.1​						place	team	name	score
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​	smith							K3	=FILTER($B$3:$B$25,$C$3:$C$25>0)
15​	tacey							L3	=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]

with Power Query and Pivot Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Team", "Name"}, "Category", "Score")
in
    UOC

Source								Pivot
Team	Name	category 1	category 2	category 3	category 4	category 5		Category	Team	Name	Score
1	max	1.1						category 1	1	max	1.1
2	annely			20					3	bruck	0.2
3	bruck	0.2							4	mark	2.1
4	mark	2.1	2.1			2.1			6	fish	3.5
5	todd								10	ashley	100
6	fish	3.5	3.5						11	jim	90
7	ted				10				13	steve	17.1
8	james								16	katy	80.3
9	tom								20	mark	60.3
10	ashley	100							22	harley	10.7
11	jim	90		30	20			category 2	4	mark	2.1
12	otis								6	fish	3.5
13	steve	17.1				17.1			16	katy	71.3
14	smith								20	mark	24.1
15	tacey							category 3	2	annely	20
16	katy	80.3	71.3						11	jim	30
17	hum								23	gunner	25
18	ed							category 4	7	ted	10
19	marc				15				11	jim	20
20	mark	60.3	24.1						19	marc	15
21	dog								23	gunner	30
22	harley	10.7				10.7		category 5	4	mark	2.1
23	gunner			25	30				13	steve	17.1
									22	harley	10.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

 

[gakuffs]

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]

XL365 and higher for mentioned function (I am not sure about XL2019)
XL2016 and higher for Power Query

 

[sandy666]

XL365 only (for today)

XLOOKUP function - Microsoft Support

Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from...

support.microsoft.com

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

 

[RAJESH NATH]

XL365 only (for today)

XLOOKUP function - Microsoft Support

Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from...

support.microsoft.com

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

actually i was not notice what version he is using....!!!!

 

[sandy666]

actually i was not notice what version he is using....!!!!

see OP's profile