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
6 months, 2 weeks 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: 41
    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.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort