[Solved] Index/match/max

Discuss the spreadsheet application
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

[Solved] Index/match/max

Post by spacecowboy_1127 »

Hi,

I'm fairly new to spreadsheets, but have recently needed to use them more.

I want to find the max value in a row, match it to the particular column header and create a new column. I have used this =INDEX(F3:T3;MATCH(MAX(F5:T5); F5:T5)) I want to use it on successive rows but i do no want duplications. If the corresponding column for the max value has previously used, I want to select the next highest value of the remaining options. repeating until eventually every column header will be used.

If you need more info let me know
Last edited by Hagar Delest on Sat Sep 25, 2021 5:47 pm, edited 1 time in total.
Reason: Tagged [Solved].
OpenOffice 4.1.7 on windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: index/match/max

Post by Zizi64 »

Please upload you ODF type sample file here - with the manually typed-in data what you want to find/index from the original data set.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: index/match/max

Post by spacecowboy_1127 »

example.png
example.png (11.07 KiB) Viewed 11361 times
in row 92 the max value is 84, but player 1 had already won, so in that week, i would like the next highest score (82) player 3 to be selected
in row 94 the winner would be player 4 because 1 2 and3 had already won.

the formula is applied to column B
 Edit:  
the file is in later post
Last edited by spacecowboy_1127 on Sat Sep 25, 2021 1:24 am, edited 2 times in total.
OpenOffice 4.1.7 on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: index/match/max

Post by Villeroy »

spacecowboy_1127 wrote:Hi,

I'm fairly new to spreadsheets, but have recently needed to use them more.
This is a 100% database problem, not at all related to spreadsheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: index/match/max

Post by Lupp »

@spacecowboy:
This is your first question, and by that you are excused, but...

If your question has a reasonable solution by formuals at all, it should be considered rather challenging to find one.
Even an experienced user may need to "play" with real data for the decision concerning the "if", and to get feeling sure about what he wants to suggest. "Trial and error" isn't the first way in the field, but "experimental verification" or "proof of concept" of supposed solutions isn't a silly idea either.

Why should you annoy somebody wanting to help you expecting him (f/m) to waste his time creating data "just for trying"?
You surely already have examples.

Only attach an image, if your question is explicitly concernng the display - and if you doubt reproducibility with a different "system".
Otherwise always attach a real example file.

Also: Concerning solutions for anything by spreadsheet formulas the question if sufficient efficiency can be achieved often depends heavily on the size of your actual use-case. What works well with 100 entities may be completely impractical with 10000. The size may be described by a number of rows here and by something else there... Tell us about such parameters!
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: index/match/max

Post by spacecowboy_1127 »

That is a pic of my actual file.
I have found no examples of what I am looking for.
I have searched forums, internet, and many user guides.
If it is as easy as you suggest, why not answer the question, instead of acting insulting and condescending.
Point me in the direction of an example similar to mine. any examples i see with IF/AND/OR cannot do what I want.
I have been working on this problem for weeks. I specifically joined the forums, because the questions/answers made it seem like a good option. I apologize if I am not as intelligent as you seem to be.

It seemed logical to me, that an experienced user may know how to apply limitations, to a formula.

There would be a total of 15-20 players over 15-20 weeks

P.S. Should i have included the actual file? instead of a simple screen shot?
OpenOffice 4.1.7 on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: index/match/max

Post by Villeroy »

spacecowboy_1127 wrote:That is a pic of my actual file.
Why do you upload a pic instead of the actual file? Makes no sense
spacecowboy_1127 wrote:I have found no examples of what I am looking for.
SInce 30 years people try to misuse spreadsheets as databases and you can't find any working example? What a petty.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: index/match/max

Post by spacecowboy_1127 »

forum.ods
(17.21 KiB) Downloaded 267 times
I have a small league with 40 kids playing once a week.
I'd hardly call it a database. I just wanted to track kids progress and high scores.
A spreadsheet is perfectly acceptable. You sir, are being unreasonable.

And as for the pic, I have never used a forum before, and didn't realize u needed an actual spread sheet files files. My apologies.
Last edited by spacecowboy_1127 on Sat Sep 25, 2021 12:07 am, edited 1 time in total.
OpenOffice 4.1.7 on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: index/match/max

Post by Villeroy »

NO, a spreadsheet is inacceptable even for a small database.
My document has 5 sheets which pulls info across sheets
most frequent spreadbase error
I didn't see the need to upload the whole document
farewell
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: index/match/max

Post by spacecowboy_1127 »

I no longer wish to speak with you. The First gentleman was willing to lend a hand. Please refrain from responding if you will not help.
OpenOffice 4.1.7 on windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: index/match/max

Post by Zizi64 »

I want to use it on successive rows but i do no want duplications. If the corresponding column for the max value has previously used, I want to select the next highest value of the remaining options.
It is not possible to achieve directly easily in a Spreadsheet.

You can use
- a very long and ineffective formula (with lots of nested IF() and LARGE() functions), to examine all of the values in a row/culumn.
- your own macros for this process.
- a small database - as Villeroy has suggested. (I neved used databases, therefore I can not help you in this case)
Last edited by Zizi64 on Sat Sep 25, 2021 8:28 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: index/match/max

Post by eeigor »

No, the solution is possible and I will help you.
@spacecowboy_1127,
you can rebuild the table so that the data is stored in a slightly different form, convenient for storage and displayed in a form that is easy to view and analyze. Question: For what period do you want to see the result at the same time (for a month, a year or more)? Or on demand for a given interval, or for the entire period at once.

Spreasheet Database Fields: Date; Player; Score
This is a format for input and storage of raw data according to your example. The rest will come later.

EDIT: Although I am already beginning to understand something: there is a child and his achievements for the entire period and in comparison with a certain child and against the background of a better result.
And @Villeroy chided you for working with a sheet of a spreadsheet as if you were working with a sheet of paper. Ineffective.
There are other reporting mechanisms: pivot tables, multiple operations (perhaps you can't understand this by the name)

EDIT 2: On the other hand, your example is also quite workable, only you need to correct the formulas so that it is easier to copy them to other cells. Explain again what you dislike about your example.
spacecowboy_1127 wrote:I want to use it on successive rows but i do no want duplications. If the corresponding column for the max value has previously used, I want to select the next highest value of the remaining options. repeating until eventually every column header will be used.
I don't understand what you want (not english speaking). Show by example the result you want to get.
The data is repeated. What should be displayed? Want to rank all kids based on their results in one day? When storing data in a list (a simple database), this can be achieved by sorting. Otherwise, through the function LARGE(). See AGGREGATE() 14
Attachments
Снимок экрана от 2021-09-25 09-47-06.png
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: index/match/max

Post by Villeroy »

With the right data layout (a database or a database-like cell range), it could be a matter of minutes to create one or two pivot tables. For my part, I have wasted too much time begging for concrete data. I'm out.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: index/match/max

Post by spacecowboy_1127 »

Thank you eeigor,

In the example I posed, {week 3 (oct 9)(row 5)}, the winner is listed as player 1. since player 1 had already won in the previous week, i wanted it to show me the next highest player, in this case player 3.
then in week 4, (row 7) player 2 is listed as the winner, but had already won in week 1. the next highest is player 1, but he had already one, the next player has also previously won. so i would like the winner to be player 4.

I see now it is a little more complex than i had first thought, but essentially every week, the pool of players eligible to win, decreases.
There would be about 15-20 weeks/winners

i manually entered the answers as i would like them to appear.
forum2.ods
(17.75 KiB) Downloaded 267 times
OpenOffice 4.1.7 on windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: index/match/max

Post by Lupp »

I felt unhappy with parts of the discussion, but would nonetheless state, that I personally wouldn't try to design a DB-based solution for such a volatile task, and that I also am not really happy with solutions requiring repeated interaction concerning pivot tables or the like. All this is very valuable, but surely also limited,and you can't pass the solution simply to somebody only getting a briefing concerning the usage.

If it was my own task, I would probably decide to write a little specialized program in a higher language.

I also felt unhappy with the sheet the OQ attached and with the lack of precise statements concerning the eligibilty of a player as a week's winner, and the decision in case of ambiguities (selection among equals), handling of retirements and/or disqualifications...
A matured solution -by what means ever- would require a complete specification.

AND: Formatting last!

Since I finally created my own test data despite my criticism, and designed a solution by spreadsheet formulas (leaving very few to user interaction), I now wouldn't take additional time to study the suggestions by the appreciated colleagues. I simply attach a document showing my approach to such a rather complicated iterative selection in a sheet. Recent LibreOffice comes with some useful newer functions, but the second sheet of my example shows what's feasible with AOO (4.1.7).
aoo106181ComplicatedSelection_2.ods
(59.25 KiB) Downloaded 285 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: index/match/max

Post by Villeroy »

All this hardcore complexity for 20 weeks of data? Now you should write down another 5 pages of documentation on how to edit this specific spreadsheet with relative, absolute and mixed references, with expanding references, conditional formatting etc. You need all the expertise just to handle this document. And you can be sure that all this is still not good enough for some reason or another. Then you start writing macro code and more macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: index/match/max

Post by spacecowboy_1127 »

I wasn't expecting it to be this complicated. I just thought there was a formula I could use. I apologize.

Thank you lupp. What you did is quite impressive to me. I never in a million years expected it to be that complicated. It Gives me exactly what I wanted! I appreciate the time and effort you put into it.

How would my mark my original query as solved?
OpenOffice 4.1.7 on windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: index/match/max

Post by Lupp »

@Villeroy.
Despite the differences concerning our views and ways of judging, you surely expect me capable of having the mentioned objections, doubts and qualms in mind. Some of them I explicated anyway.

However, the given task is complicated in itself, and I can't believe that a solution based on means you would prefer can be simple. The choice of the appropriate means per problem "simply isn't simple", and it may ressemble a medical diagnosis which also can't be reliably valid if based on a few lines of writing or on a phone call.

Seen this way we havent' the choice to suggest a solution by DB or one by sheets or one by custom programming as "the" solution, but either to actually sketch a preliminary, but basically working solution with the means of our own choice, or to refuse an answer because the question is too complicated to be answered by a few hints, and therefore not appropriate in a "user forum" at all.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Index/match/max

Post by Villeroy »

A database would be even more complex to develop, but foolproof to use for the years, weeks and all the players to come. It does not matter if you record 1, 2, or 50 years with 10, 20 or thousand competitors. No need to insert new cells. No need to adjust formula ranges. No harm done when sorting or filtering. Just enter the competitors of a year (no duplicates accepted). Then enter the points for each player every week into an input form (no duplicates accepted of course).
Watch intermediate result tables directly on the form as you enter new points.
Drag data or query results into spreadsheets and do what you want without doing any harm to your data.
Spreadsheets (including Excel) are not well prepared to do anything like simple list keeping. Of course it is very easy to build up an ad-hoc pseudo-database on a sheet BUT:
spacecowboy_1127 wrote:I'm fairly new to spreadsheets, ...
Such ad-hoc database is difficult to maintain because you need to know some technical details and pitfalls even if you do not have to understand the formulas themselves. It is easy to get some result from a sheet. However, it is difficult to always get the correct result.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Index/match/max

Post by eeigor »

Withdrawn by @eeigor
Last edited by eeigor on Sun Sep 26, 2021 8:47 am, edited 3 times in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Index/match/max

Post by eeigor »

Macros used
There may be mistakes. Test it.
The first two fields are an array formula as the UDF returns an array: value & player.
A3:B3 {=GETNEXTWINNER(Players;$D3:$R3;$B$2:$B2)}

If there are fewer players than records (games), then the max value is returned for such records. But you have more: 40 players and 15-20 games.
NOTE: Any formula from the first two columns can be copied to other rows (see screenshot: copy & paste), but you can't drag it with the mouse (references in array formula are not updated - why?).

Code: Select all

REM  *****  BASIC  *****


Function GetNextWinner(Players, Values, Winners)
	Dim DataArray()
	Dim i%, j%
	Dim done As Boolean

	ReDim DataArray(1 To UBound(Players, 2), 1 To 2)
	For i = 1 To UBound(Players, 2)  'Players(1 To 1, 1 To ItemCount)
		DataArray(i, 1) = Players(1, i)
		DataArray(i, 2) = Values(1, i)
	Next

	DataArray = SortDataArrayDesc(DataArray)

	For i = 1 To UBound(DataArray)
		If Not WinnerExists(Winners, DataArray(i, 1)) Then  
			GetNextWinner = Array(DataArray(i, 2), DataArray(i, 1))
			done = True
			Exit For
		End If
	Next
	If Not done Then
		GetNextWinner = Array(DataArray(1, 2), DataArray(1, 1))  'returns 1st pair (maximum)
	End If

'	Dim s$
'	For i = 1 To UBound(DataArray)
'		s = s & Chr(10) & DataArray(i, 1) & " = " & DataArray(i, 2)
'	Next i
'	MsgBox s
End Function

Function WinnerExists(Winners, Player) As Boolean
	Dim i%
	' Skip 1st empty cell (anchor).
	For i = 2 To UBound(Winners, 1)  'Winners(1 To ItemCount, 1 To 1)
		If Winners(i, 1) = Player Then
			WinnerExists = True
			Exit For
		End If
	Next
End Function

Function SortDataArrayDesc(ByVal DataArray())
	Dim i%, j%
	Dim player$, value%
	Dim n%: n = UBound(DataArray)

	For i = 2 To n - 1
		For j = 1 To n - i + 1
			If DataArray(j, 2) < DataArray(j + 1, 2) Then
				player = DataArray(j, 1)
				value = DataArray(j, 2)

				DataArray(j, 1) = DataArray(j + 1, 1)
				DataArray(j, 2) = DataArray(j + 1, 2)

				DataArray(j + 1, 1) = player
				DataArray(j + 1, 2) = value
			End If
		Next j
	Next i

	SortDataArrayDesc = DataArray()
End Function
EDIT: Written in a hurry for the sake of achieving results. The code can be optimized. The bubble sort may be replaced with a faster sort (merge sort, quick sort, etc.). The OP marked the task as "solved". Is it so?
By using macros, it was possible to completely preserve your table layout and greatly simplify its use.
Attachments
Снимок экрана от 2021-09-26 09-38-05.png
Снимок экрана от 2021-09-26 09-38-05.png (14.21 KiB) Viewed 10885 times
ex-player-values.ods
(27.74 KiB) Downloaded 250 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Index/match/max

Post by eeigor »

However, without array formulas it works more stable. Consider this method. Formulas can now be dragged with the mouse.
A3: =IFERROR(INDEX($D3:$R3;;MATCH($B3;Players;0));"")
B3: =GETNEXTWINNER(Players;$D3:$R3;$B$2:$B2)

The layout can be made completely identical to yours. It doesn't matter now.
Attachments
Снимок экрана от 2021-09-26 16-28-24.png
ex-player-values (2).ods
(29.21 KiB) Downloaded 243 times
Last edited by eeigor on Sun Sep 26, 2021 3:28 pm, edited 2 times in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Index/match/max

Post by Villeroy »

The best I can do within an hour of development time using Lupp's sheet data.
In the unlikely case that this quick draft meets your requirements, you may add players and points of the past 20 years and you may use this document for the players and points of the next 20 years without ever having to edit and save anything at all. You just enter data which are stored directly in the database when you click a save button or when you move to another record.

User interface:
1. Maintainance/Persons where you enter the names of all players.
2. Maintainance/Competitors where you enter the competitors by year.
3a. Weekly results where you enter a week number and then append the players and points. Some ranking appears on the right side.
3b. A quick entry form for the weekly results.
4. A quick and simple report summing up player's points for each year.
------------
Tables store data entered into the entry forms or imported from clipboard.
Queries are like formulas. Spreadsheet formulas return single values. A query returns a result table derived from the storage tables. The pivot table is the only spreadsheet feature which is a little bit like a query (more precisely: aggregation query).

download download/file.php?id=44089
Last edited by Villeroy on Fri Oct 01, 2021 6:08 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Index/match/max

Post by eeigor »

eeigor wrote: A3: =IFERROR(INDEX($D3:$R3;;MATCH($B3;Players;0));"")
B3: =GETNEXTWINNER(Players;$D3:$R3;$B$2:$B2)
No, gentlemen @Lupp & @Villeroy. I must admit that my method is the most suitable for this task. Plus, the code I wrote each of you would write even faster.
Where has our poor OP gone :?:
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Index/match/max

Post by Villeroy »

eeigor wrote:Where has our poor OP gone? :roll:
He gave up because he had a misconception that a spreadsheet program provides ready made solutions for whatever data you throw at it. A spreadsheet as development tool is completely out of scope for computer users in the 21st century.
I have to admit that I do not understand your solution (or Lupp's). What are you calculating here? If I knew, I could calculate the same in my database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Index/match/max

Post by eeigor »

Villeroy wrote:I do not understand your solution (or Lupp's). What are you calculating here?
eeigor wrote:However, without array formulas it works more stable. Consider this method. Formulas can now be dragged with the mouse.
A3: =IFERROR(INDEX($D3:$R3;;MATCH($B3;Players;0));"")
B3: =GETNEXTWINNER(Players;$D3:$R3;$B$2:$B2)
@Villeroy, solution above (ex-player-values (2).ods).
I find the next winner of the week (GetNextWinner) who has the best score (SortDataArrayDesc) and he - the player - wins for the first time (Not WinnerExists, skips the winners even if they have more points, it doesn't matter).

Function GetNextWinner(Players, Values, Winners)
Function SortDataArrayDesc(DataArray())
Function WinnerExists(Winners, Player) As Boolean

In my example, there are 15 players, and in the first 15 games the players are not repeated. Then the player with the maximum result is simply taken. However, according to the author's condition, the number of players (40 or 15-20) is equal to or greater than the number of games (15-20).
So in my example, look at the first 15 games where the winners are unique. If the number of points is the same for several players, then the first unique player is displayed in the order of their sequence.
And as I said, bubble sort can be replaced with more efficient sort.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: [Solved] Index/match/max

Post by spacecowboy_1127 »

Wow, I didn't realize this discussion had continued.

I have never used MACROS. This solution seems very concise. I will give it a try. Thank you eeigor! This is EXACTLY what I need.

While the first example worked quite well, it was complex. I fear I may in advertently delete a calculation somewhere and mess it up.
OpenOffice 4.1.7 on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Index/match/max

Post by Villeroy »

Removed the "table of winners" from the results form in favour of a column for extra points in case of a draw. Unhide[*] the extra column in the "Weekly Results" form when you need to add a secondary ranking between competitors with equal points. Hit the [OK] button or the toolbar's refresh button to apply the correct sort order by points and by extra points.
The extra points take values between 0 (no correction) until 255.

[*] right-click header-->Show all
select week #9 in my example and see how the ranking of the first 4 with equal points is achieved

Download: download/file.php?id=44089
Last edited by Villeroy on Fri Oct 01, 2021 6:08 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
spacecowboy_1127
Posts: 9
Joined: Fri Sep 24, 2021 3:02 am

Re: [Solved] Index/match/max

Post by spacecowboy_1127 »

@ Villroy,

I appreciate the time you put into creating a solution, but I have no idea how to use databases. I don't know how to pull information into it, or retrieve information from it.

@ eeigor

I've tried using the MACRO's you gave me (example 2 doesn't work as Open Office doesn't have the IFERROR function).
Is it possible to use the table more than once in a file? I have 5 age groups I would like to use it for. For example, when I entered the information for my bantam division, Some the results pulled into the table for my senior division. would I need a separate spreadsheet for all divisions?
OpenOffice 4.1.7 on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Index/match/max

Post by Villeroy »

spacecowboy_1127 wrote: I appreciate the time you put into creating a solution, but I have no idea how to use databases. I don't know how to pull information into it, or retrieve information from it.
You find help text in the input forms. As a user you don't need to understand anything at all.
You open my quick entry input form, enter year, week, click OK and enter persons and points for the preselected weed into the two prepared boxes. One is a listbox from where you pick a person name, the other one is a numeric entry box where you can enter an integer number >0. Hit Enter or click [OK]. That's all.

The "bigger" form shows the current week's data in a table grid on the left. The row below the last row takes new data to that grid. There is only one toolbar for navigation, filtering and sorting the grid. Toolbar button [ >* ] jumps to the new entry row below the last one. There is also a refresh button which reloads the form in its default sort order (descending by points). The grey [OK] button refreshes the form as well.
When you click the save button or leave a modified row, the data are written into the database. The right table grid automatically shows the ranking table with the sums of points and the and the max. score for each player.

In order to solve the problem with equal points (draws), I added a hidden column where you can enter "extra points" to distinguish between players with equal results. A right-click on the grid's column header section lets you show and hide that hidden column.

The report is a printable medium listing a rank table for each year.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply