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)
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)
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 > 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
maxHead(0) = ""
maxHead(1) = ""
maxHead(2) = ""
FOR col = startCol TO startCol+noCols-1
value = cellString(currSheet,col,row)
headValue = cellString(currSheet,col,headRow)
'DEBUG print headValue
IF value = maxvalue(0) THEN
IF maxHead(0) = "" THEN
maxhead(0) = headValue
ELSE
maxHead(0) = maxHead(0) + "," + headValue
END IF
END IF
IF value = maxvalue(1) THEN
IF maxHead(1) = "" THEN
maxhead(1) = headValue
ELSE
maxHead(1) = maxHead(1) + "," + headValue
END IF
END IF
IF value = maxvalue(2) THEN
IF maxHead(2) = "" THEN
maxhead(2) = headValue
ELSE
maxHead(2) = maxHead(2) + "," + headValue
END IF
END IF
NEXT col
'DEBUG Print maxHead(0),maxHead(1),maxHead(2)
' Now put the winner, second AND third strings into the target column........the row will be the current row.....
putCellString(currSheet,targetCol,row,maxHead)
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)
l_cell.string = p_maxHead(l_col-p_col)
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
