Question

Highest value in excel column

  • 8 September 2023
  • 2 replies
  • 80 views
B
Badge +2

Hi,

I am trying to get the highest value from an excel column. I know the following options exist

  1. Apply filter and sort in descending order and get the first value. but this would mean I need to convert the content in to a proper Table with a Table name to use the sort package.
  2. Execute the sorting using a macro.

Both the above are not possible in this case because, I will get this excel in a mail, and would like to automate from there upon downloading. So, I cant convert it to a Table with Table name in excel or have a macro in it without manually working on it, which I would like to avoid.

Is there a way to get it done from within Automation anywhere itself? 

Regards,

Bala

2 replies

J
Userlevel 3
Badge +12

It’s ugly, but you could do something like the following (assuming the column your numbers are in is A):

  • Create variable $iFinalNum = 0
  • Create variable $iFinalRow = 0
  • Excel Advanced Open
    • Get Number of Rows, output to $iRowCount
    • Loop for $iRowCount times, assign current row to $iCounter
      • Get single cell A$iCounter.NumberToString$, output to $sRowNum
      • If $sRowNum.StringToNumber > $iFinalNum
        • # Assign $sRowNum.StringToNumber to $iFinalNum
        • # Assign $iCounter to $iFinalRow
  • MsgBox Largest number: $iFinalNum.Number:toString$ on row $iFinalRow.Number:toString$
  • Excel Close
B
Badge +2

It’s ugly, but you could do something like the following (assuming the column your numbers are in is A):

  • Create variable $iFinalNum = 0
  • Create variable $iFinalRow = 0
  • Excel Advanced Open
    • Get Number of Rows, output to $iRowCount
    • Loop for $iRowCount times, assign current row to $iCounter
      • Get single cell A$iCounter.NumberToString$, output to $sRowNum
      • If $sRowNum.StringToNumber > $iFinalNum
        • # Assign $sRowNum.StringToNumber to $iFinalNum
        • # Assign $iCounter to $iFinalRow
  • MsgBox Largest number: $iFinalNum.Number:toString$ on row $iFinalRow.Number:toString$
  • Excel Close

Hi @J.Logan ,

 

Thanks for your reply. With some modification this works. 

 

 

 

Regards,

Bala

 

Reply


Terms & ConditionsCookie settings