Ask Your Question

Find max cells in a range

asked 2017-02-03 11:18:40 +0200

steveTu gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-02-22 13:40:32 +0200

steveTu gravatar image

updated 2017-02-22 14:39:54 +0200

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 headRow     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....
DIM maxHead(3)  AS String

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)
    ' 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 = 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)
    headRow     = cellString(currSheet,4,ctrlRow)
    targetCol   = cellString(currSheet,5,ctrlRow)
    'DEBUG print startRow,Startcol,noRows,NoCols,headRow

    ' 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 ...
edit flag offensive delete link more

answered 2017-02-22 15:47:25 +0200

Lupp gravatar image

updated 2017-02-22 15:54:32 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-02-03 11:18:40 +0200

Seen: 648 times

Last updated: Feb 22 '17