Finding the ROW value in an Excel WB using Powershell based on the MAX value

Welcome Forums General PowerShell Q&A Finding the ROW value in an Excel WB using Powershell based on the MAX value

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
3 months, 1 week ago.

  • Author
    Posts
  • #107935
    Lex

    Participant
    Points: 0
    Rank: Member

    I have an Excel workbook that has values from F13 to F40, these values change every Powershell Job run we do.

    I need to find the Top 3 values in the range of F13 – F40 and I need to get the Row Number of each of the three values

    So far I can get the 3 values with this Code in Powershell

    $EXCEL = New-Object -ComObject Excel.Application
    $EXCEL.visible = $false
    $basefinalwb = $EXCEL.workbooks.open("C:\relax\Book1.xlsx")
    $basefinalws = $basefinalwb.worksheets.Item("Current Results")
    $MAX1 = $basefinalws.cells.item(1,1).Formula = "=LARGE(F13:F40, 1)"
    $MAX2 = $basefinalws.cells.item(2,1).Formula = "=LARGE(F13:F40, 2)"
    $MAX3 = $basefinalws.cells.item(3,1).Formula = "=LARGE(F13:F40, 3)"
    $MAX11 = $basefinalws.cells.item(1,1).value2
    $MAX21 = $basefinalws.cells.item(2,1).value2
    $MAX31 = $basefinalws.cells.item(3,1).value2
    Write-host Max11 is $max11
    Write-host Max21 is $max21
    Write-host Max31 is $max31
    
    

    —————————————————–

    The Results are this:
    Max11 is 0.112570356472795
    Max21 is 0.097560975609756
    Max31 is 0.0881801125703564
    Now I need to figure out which Row each of those Values came from
     
    So I have tried this code and it returns blank

    
    $Cell2 = $basefinalws.range("F1","F40").find($MAX21)
    $Cell2.Value2
    $Cell2.Row
    $Cell2
    
    

    In my testing I even pulled the Value on the Cell the number came from and they match .. I just cannot figure out why "find" isn't working.
     
    Now the only thing I can think of is that all the values in the F column are from Formulas =(D13-C13)/C13 but the above code does grab the value of the cell just fine it just can't re-find it so I can get the Row Number
     
    If anyone knows where I am going wrong, help is appreciated or maybe an easier way to accomplish the task?
     
    Thank you!
     

  • #108013

    Participant
    Points: 4
    Rank: Member

    You could use the ImportExcel module for this.

    https://github.com/dfinke/ImportExcel

    The code would look something like this:

    #Import the Excel Workbook
    $sheet = Import-Excel E:\temp\values.xlsx -NoHeader
    
    #Grab cells 13 - 40, sort and select the last 3 (largest numbers)
    $top3 = $sheet.p6[12..39] | Sort | Select -Last 3
    
    #Find the index of the top 3 values, add 1 to get the cell
    $top3 | foreach {Write-Output "Cell is F$($sheet.p6.IndexOf($_)+1)"}
    

    If your sheet has headers you can omit the -Noheader switch. The range will be 11 to 38 and you'll need to add 2 to the index.

The topic ‘Finding the ROW value in an Excel WB using Powershell based on the MAX value’ is closed to new replies.