# Find max cells in a range

I have a spreadsheet of 'scores' by user and I want to know which user has the max score. Users can 'tie' - ie have the same score, so I want to select the 'tied' users.

I am using: =IF(MAX(B8:M8)=0,"No Winner",CONCATENATE(INDEX($B$4:$M$4,MATCH(LARGE(B8:M8,1),B8:M8,0)),",",INDEX($B$4:$M$4,MATCH(LARGE(B8:M8,2),B8:M8,0))))

BUT that has problems as it just repeats the same user. I think I want to be able to adjust the range in the second check - ie if the 'winner' is I8 to then start the search for the tied account over the range I8+1:M8 - but I can't see how to do that. Can someone give me a pointer?

Thanks, Steve

edit retag close merge delete

Sort by » oldest newest most voted

For anyone else with a similar issue, I have created a macro to scan a range of cells (controlled by entries on a control sheet in the calc spreadsheet) and to find the first, second and third (including ties) by value - and to convert the first, second and third to a name in a heading row.

I'm not a macro writer (this is my first attempt) - so apologies if the code is a bit naff and there are shorter ways of doing this:

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

SUB Main
DIM doc         AS OBJECT
DIM sheets      AS OBJECT
DIM currSheet   AS OBJECT

' The control sheet - which columns/rows to look at
DIM ctrlSheet   AS INTEGER
DIM ctrlRow     AS INTEGER

' Control fields - which rows AND columns will be traversed
DIM startRow    AS INTEGER
DIM startCol    AS INTEGER
DIM noRows      AS INTEGER
DIM noCols      AS INTEGER
DIM targetCol   AS INTEGER

' Any cell, any row, any column, any cell value
DIM cell        AS OBJECT
DIM col         AS INTEGER
DIM row         AS INTEGER
DIM value       AS INTEGER

' Max array - 0 is first, 1 is 2nd AND 2 is third
DIM maxValue(3) AS INTEGER

' String Array - FOR 1st, 2nd etc converted to heading strings....

doc         = ThisComponent
sheets      = doc.Sheets

' Get the start row AND start columns AND number of rows AND columns....
ctrlSheet   = sheets.count() - 1
ctrlRow     = 0
'DEBUG print ctrlsheet

' In this case I have three blocks that I need to eavluate...the three blocks relate to three photo competitions -
' the first competition is based on the months (so 12 rows AND 6 competitors), 12 random phrases, AND a wild card (a single row)
DO WHILE 1=1
' Set the current sheet to the control sheet
currSheet   = doc.Sheets(ctrlSheet)
ctrlRow     = ctrlRow + 1

' EXIT the loop IF no more control blocks........So stop when first startRow is empty
cell = currSheet.getCellByPosition(0,ctrlRow)
IF cell.type = com.sun.star.table.CellContentType.EMPTY THEN
EXIT DO
END IF

' Get the control fields - which rows AND columns to check AND which is the heading row to convert the max's to strings
startRow    = cellString(currSheet,0,ctrlRow)
startCol    = cellString(currSheet,1,ctrlRow)
noRows      = cellString(currSheet,2,ctrlRow)
noCols      = cellString(currSheet,3,ctrlRow)
targetCol   = cellString(currSheet,5,ctrlRow)

' OK - so we have the basic control columns - now loop over the data ranges AND get 1st, 2nd AND 3rd values...
' The issue is that we can THEN have 'joint' values for 1st, 2nd, 3rd - so once we know the values that are first, 2nd etc
' THEN loop back over the cells in the row to find that value AND build string from their heading row values
currSheet = doc.sheets(ctrlSheet-1)

FOR row = startRow TO startRow+noRows-1
maxValue(0)     = 0
maxValue(1)     = 0
maxvalue(2)     = 0
FOR col = startCol TO startCol+noCols-1

'cell = doc.sheets(0).getCellByPosition(col,row)
'value = cell.getValue()
value       = cellString(currSheet,col,row)

IF value ...
more

To understand the following remarks you should study the example ask86821CollectEquallyRanked_1.ods attached here. Start with making sure that the macro code is not malicious and try it out then.

If writing user code I prefer functions much over subs where applicable. I can easily pass parameters, and I often can use a group of functions to solve a lot of very different tasks.

A function being able to concatenate in an accumulating way is useful now and then. So is a function doing the reverse. The attached example is demonstarting how both kinds of functions can cooperate in a case similar to what the OQ described.

Since there is a rumor that recent Excel has implemented new functions for accumulating concatenation, I reworked older (very raw) functions for the purpose and introduced some new features.

I did not hear of a corresponding function for splitting of texts in Excel. And I heard another rumor that the concatenation is buggy in Excel when applied with array-evaluation. Thus also Excel users can take advantage of the more powerful functions I supply.

Functions coded in BASIC cannot be expected to be extremely efficient. I also did not yet implement a few ideas to enhance efficiency sticking to BASIC.

more