August 8, 2018 at 5:03 pm

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!
 

August 8, 2018 at 9:31 pm

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.