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
-
AuthorPosts
-
August 8, 2018 at 5:03 pm #107935
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 #108013
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.
-
AuthorPosts
The topic ‘Finding the ROW value in an Excel WB using Powershell based on the MAX value’ is closed to new replies.