Skip to main content

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

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

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