 # 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

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 > maxvalue(0) THEN
maxValue(2) = maxvalue(1)
maxvalue(1) = maxvalue(0)
maxValue(0) = value
END IF

IF value > 	maxValue(1) AND value < maxvalue(0) THEN
maxValue(2) = maxvalue(1)
maxValue(1) = value
END IF

IF value > maxvalue(2) AND value < maxvalue(1) THEN
maxValue(2) = value
END IF

NEXT col
'DEBUG Print maxvalue(0),maxvalue(1),maxvalue(2)
' Convert the 1st...etc to a string based on the heading lines
FOR col = startCol TO startCol+noCols-1

value 		= cellString(currSheet,col,row)

IF value = maxvalue(0) THEN
ELSE
END IF
END IF

IF value = maxvalue(1) THEN
ELSE
END IF
END IF

IF value = maxvalue(2) THEN
ELSE
END IF
END IF

NEXT col

' Now put the winner, second AND third strings into the target column........the row will be the current row.....
NEXT row
LOOP
END SUB

' Function to get the cell 'content' as a string for a cell position
' Returns the content as a string AND THEN relies on string to number conversion in the main body
' Params In -	currSheet OBJECT
'				col INTEGER
'				row INTEGER
' Params Out -	Value String
Function cellString(p_currSheet AS OBJECT, p_col AS INTEGER, p_row AS INTEGER) AS String
DIM l_cell 		AS OBJECT

l_cell 		= p_currSheet.getCellByPosition(p_col,p_row)

SELECT CASE l_cell.Type
CASE com.sun.star.table.CellContentType.EMPTY
cellString 		= ""
CASE com.sun.star.table.CellContentType.VALUE
cellString		= l_cell.value
CASE com.sun.star.table.CellContentType.TEXT
cellString		= l_cell.string
CASE com.sun.star.table.CellContentType.FORMULA
cellString		= l_cell.value
END SELECT
END Function

' Function to put the winners into cells, starting at the targetCell.
' First will go into targetCol, 2nd into targetCol+1 etc.....
' Params In -	currSheet OBJECT
'				targetCol INTEGER
'				row INTEGER
SUB putCellString(p_currSheet AS OBJECT, p_col AS INTEGER, p_row AS INTEGER,p_maxHead) AS String
DIM l_cell 		AS OBJECT
DIM l_col		AS INTEGER

FOR l_col = p_col to p_col+2

l_cell 			= p_currSheet.getCellByPosition(l_col,p_row)

NEXT l_col

END SUB
``````

I have attached the voting sheet calc file. Three sheets - the 2016 sheet has the old way of trying to get the max from the relevant rows and columns - that didn’t work too well, but was ok’ish. The 2017 sheet has been changed to have 1st, 2nd & 3rd columns - and those arte populated on shift-f7 (I customised the kayboard to assign my macro above to that keystroke) - based on the entries in sheet 3. Sheet 3 (or the last sheet) will be my control sheet. It says which rows and columns are to be involved and which heading line is used to convert the 1st, 2nd and 3rd to names as well as which columns will then carry the output strings. Hope that helps someone else.
Voting Sheet - 20170125.ods

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.