patelbg2001
asked on
Excel vlookup using Powershell
Hi, I'm writing a report script in excel, using powershell. I have sucessfully gathered my data into one excel worksheet and wanted to preform lookups based on the following format
Sheet1 is Report Summery
Sheet2 is Report Data
In Sheet2 I am using colums from A to K. The first row has colums headers.
From Excel or Powershell / Excel how can I preform the following
Sheet2,
In colum B, if the cell contains the word "boom" and Colum I contains the word "dynamite" count the number of instances and report into Sheet1 colum D cell3?
Thanks.
Sheet1 is Report Summery
Sheet2 is Report Data
In Sheet2 I am using colums from A to K. The first row has colums headers.
From Excel or Powershell / Excel how can I preform the following
Sheet2,
In colum B, if the cell contains the word "boom" and Colum I contains the word "dynamite" count the number of instances and report into Sheet1 colum D cell3?
Thanks.
For more clarity, can you post sample file please.
TRY THIS:
$filename="C:\test\lookupp owershell. xlsx"
$xlCellTypeLastCell = 11
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open($filena me)
$ws = $wb.worksheets | where {$_.name -eq "sheet2" }
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCell TypeLastCe ll)
$row = $lastCell.row
$count=0
for ($i = 2; $i -le $row; $i++)
{
$bvalue=$ws.Cells.Item($i, 2).Value()
$ivalue=$ws.Cells.Item($i, 9).Value()
if($bvalue -eq "boom" -and $ivalue -eq "dynamite")
{
$count+=1
}
}
$sws=$wb.worksheets | where {$_.name -eq "sheet1" }
$sws.cells.item(3,4).Formu laLocal=$c ount
#$strServer
#Your Code Here
$ws.SaveAs($filename)
$sws.SaveAs($filename)
$xl.Quit()
$filename="C:\test\lookupp
$xlCellTypeLastCell = 11
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open($filena
$ws = $wb.worksheets | where {$_.name -eq "sheet2" }
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCell
$row = $lastCell.row
$count=0
for ($i = 2; $i -le $row; $i++)
{
$bvalue=$ws.Cells.Item($i,
$ivalue=$ws.Cells.Item($i,
if($bvalue -eq "boom" -and $ivalue -eq "dynamite")
{
$count+=1
}
}
$sws=$wb.worksheets | where {$_.name -eq "sheet1" }
$sws.cells.item(3,4).Formu
#$strServer
#Your Code Here
$ws.SaveAs($filename)
$sws.SaveAs($filename)
$xl.Quit()
ASKER
Write-Verbose "Adding Exchange PS Snapin"
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010 -erroraction:silentlycontinue
######### FORMAT TIMESTAMP TO APPEND REPORT FILENAMES WITH ########
$Timestamp = get-date -format g
$Timestamp = $Timestamp -replace(" ", "")
$Timestamp = $Timestamp -replace("/", "")
$Timestamp = $Timestamp -replace(":", "")
##### 3 OUTPUT FILES TO BE CREATED, 1 SUMMARY AND 1 DETAILED EACH IN HTML AND XLSX FORMAT ######
$XlxsFile = [String] $CurrentLocation + "\MSExchangeReport" + $Timestamp + ".xlsx"
Write-Verbose "Expanding PS to view the AD Forest"
$SRVSettings = Get-ADServerSettings
if ($SRVSettings.ViewEntireForest -eq "False")
{
Set-ADServerSettings -ViewEntireForest $true
}
$role = @{
2 = "MB"
4 = "CAS"
16 = "UM"
32 = "HT"
64 = "ET"
}
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.add()
$workbook.worksheets.add()
$workbook.worksheets.add()
$workbook.worksheets.add()
$sheet1 = $workbook.worksheets.Item(1)
$sheet1.name = "ReportSummary"
$sheet2 = $workbook.worksheets.Item(2)
$sheet2.name = "Exchange2010"
$sheet3 = $workbook.worksheets.Item(3)
$sheet3.name = "Exchange2007"
$sheet4 = $workbook.worksheets.Item(4)
$sheet4.name = "Count2010Mbx"
$sheet5 = $workbook.worksheets.Item(5)
$sheet5.name = "Count2007Mbx"
$sheet6 = $workbook.worksheets.Item(6)
$sheet6.name = "SpareSheet"
$Sheet2.Cells.Item(1,1) = "Name"
$Sheet2.Cells.Item(1,2) = "ADDomain"
$Sheet2.Cells.Item(1,3) = "ADSite"
$Sheet2.Cells.Item(1,4) = "IsHubTransportServer"
$Sheet2.Cells.Item(1,5) = "IsClientAccessServer"
$Sheet2.Cells.Item(1,6) = "IsEdgeServer"
$Sheet2.Cells.Item(1,7) = "IsMailboxServer"
$Sheet2.Cells.Item(1,8) = "IsUnifiedMessagingServer"
$Sheet2.Cells.Item(1,9) = "ServerRole"
$Sheet2.Cells.Item(1,10) = "AdminDisplayVersion"
# $Sheet.Cells.Item(1,11) = "MbxCount"
$Sheet2.activate()
#
#
$intRow = 2
$WorkBook = $Sheet2.UsedRange
$WorkBook.Interior.ColorIndex = 11
$WorkBook.Font.ColorIndex = 19
$WorkBook.Font.Bold = $True
#
## My Exchange Server 2010 Report
#
$e2k10 = Get-ExchangeServer | ?{$_.AdminDisplayVersion -like "Version 14.*"} | Select Name, Domain, Site, IsHubTransportServer, IsClientAccessServer, IsEdgeServer, IsMailboxServer, IsUnifiedMessagingServer, ServerRole, AdminDisplayVersion
Foreach($x in $e2k10){
$Sheet2.Cells.Item($intRow, 1) = $x.Name
$Sheet2.Cells.Item($intRow, 2) = $x.Domain
$a = $x.Site
$b = $a.tostring()
$c = $b | %{$_.Replace('x.com/Configuration/Sites/','')}
$Sheet2.Cells.Item($intRow, 3) = $c
$Sheet2.Cells.Item($intRow, 4) = $x.IsHubTransportServer
$Sheet2.Cells.Item($intRow, 5) = $x.IsClientAccessServer
$Sheet2.Cells.Item($intRow, 6) = $x.IsEdgeServer
$Sheet2.Cells.Item($intRow, 7) = $x.IsMailboxServer
$Sheet2.Cells.Item($intRow, 8) = $x.IsUnifiedMessagingServer
$roles = ($role.keys | ?{$_ -band $x.ServerRole} | %{$role.Get_Item($_)}) -join ", "
$Sheet2.Cells.Item($intRow, 9) = $roles
$ing2 = [STRING] $x.AdminDisplayVersion
$Sheet2.Cells.Item($intRow, 10) = $ing2
$intRow = $intRow + 1
}
####### EMPTY VARIABLES BEFORE MOVING ON TO NEXT SHEET #######
$e2k10 = $Null
#
##Configuring 3rd sheet of the Excel workbook
#
$Sheet3.Cells.Item(1,1) = "Name"
$Sheet3.Cells.Item(1,2) = "ADDomain"
$Sheet3.Cells.Item(1,3) = "ADSite"
$Sheet3.Cells.Item(1,4) = "IsHubTransportServer"
$Sheet3.Cells.Item(1,5) = "IsClientAccessServer"
$Sheet3.Cells.Item(1,6) = "IsEdgeServer"
$Sheet3.Cells.Item(1,7) = "IsMailboxServer"
$Sheet3.Cells.Item(1,8) = "IsUnifiedMessagingServer"
$Sheet3.Cells.Item(1,9) = "ServerRole"
$Sheet3.Cells.Item(1,10) = "AdminDisplayVersion"
$Sheet3.activate()
$intRow = 2
$WorkBook = $Sheet3.UsedRange
$WorkBook.Interior.ColorIndex = 11
$WorkBook.Font.ColorIndex = 19
$WorkBook.Font.Bold = $True
#
## My Exchange Server 2007 Report
#
$e2k7 = Get-ExchangeServer | ?{$_.AdminDisplayVersion -like "Version 8.*"} | Select Name, Domain, Site, IsHubTransportServer, IsClientAccessServer, IsEdgeServer, IsMailboxServer, IsUnifiedMessagingServer, ServerRole, AdminDisplayVersion
Foreach($ab in $e2k7){
$Sheet3.Cells.Item($intRow, 1) = $ab.Name
$Sheet3.Cells.Item($intRow, 2) = $ab.Domain
$a = $ab.Site
$b = $a.tostring()
$c = $b | %{$_.Replace('x.com/Configuration/Sites/','')}
$Sheet3.Cells.Item($intRow, 3) = $c
$Sheet3.Cells.Item($intRow, 4) = $ab.IsHubTransportServer
$Sheet3.Cells.Item($intRow, 5) = $ab.IsClientAccessServer
$Sheet3.Cells.Item($intRow, 6) = $ab.IsEdgeServer
$Sheet3.Cells.Item($intRow, 7) = $ab.IsMailboxServer
$Sheet3.Cells.Item($intRow, 8) = $ab.IsUnifiedMessagingServer
$roles = ($role.keys | ?{$_ -band $ab.ServerRole} | %{$role.Get_Item($_)}) -join ", "
$Sheet3.Cells.Item($intRow, 9) = $roles
$ing2 = [STRING] $ab.AdminDisplayVersion
$Sheet3.Cells.Item($intRow, 10) = $ing2
$intRow = $intRow + 1
}
####### EMPTY VARIABLES BEFORE MOVING ON TO NEXT SHEET #######
$e2k7 = $Null
#
#
$Sheet1.Cells.Item(1,1) = "Region"
$Sheet1.Cells.Item(1,2) = "Country"
$Sheet1.Cells.Item(1,3) = "Users"
$Sheet1.Cells.Item(1,4) = "DataCenter Location"
$Sheet1.Cells.Item(1,5) = "Client Access"
$Sheet1.Cells.Item(1,6) = "Hub Transport"
$Sheet1.Cells.Item(1,7) = "Mailbox Server"
$Sheet1.Cells.Item(1,8) = "Public Folder"
#
$Sheet1.activate()
#
$intRow = 2
$WorkBook = $Sheet1.UsedRange
$WorkBook.Interior.ColorIndex = 11
$WorkBook.Font.ColorIndex = 19
$WorkBook.Font.Bold = $True
#
$Excel.SaveAs($XlxsFile)
# $Excel.Close()
Start-Sleep -Sec 5
$Excel.Quit()
$Excel = $Null
ASKER
What I'm looking to do is summarise my findings in sheet 1 based on data within the different sheets
Have you tried my script? whats the outcome?
In colum B, if the cell contains the word "boom" and Colum I contains the word "dynamite" count the number of instances and report into Sheet1 colum D cell3?
if there is data boom and dynamite in cells b and i, does it count 1 or 2?
if there is data boom and dynamite in cells b and i, does it count 1 or 2?
ASKER
if there is data boom and dynamite in cells b and i, its would count as 1.
So my summary table will reference the AD Site in sheet2 in Sheet 1, these will be under the heading DataCenter Location. for each adsite called "boom" in colum B and "dynamite" colum I, count them
So my summary table will reference the AD Site in sheet2 in Sheet 1, these will be under the heading DataCenter Location. for each adsite called "boom" in colum B and "dynamite" colum I, count them
tried my script? i guess it should work that way
That is a veeery slow way to do the logic. It is much better to let Excel do the calculations, instead of causing severe overhead by going thru several levels of COM/Office Automation.
I would just set a formula into the target cell
= CountIfS(B:B;"boom";I:I;"d ynamite")
I would just set a formula into the target cell
= CountIfS(B:B;"boom";I:I;"d
@Qlemo:
Instead of the "That is a veeery slow way to do the logic", you could have commented as "Instead of using powershell, I prefer excel formula to achieve your requirement in a faster way"
Sometimes it hurts experts who are taking time to answer and at the end receiving this type of feedback. And you did not post any logic in powershell which is better than my comment. I would have agreed you. I respect you btw.
Instead of the "That is a veeery slow way to do the logic", you could have commented as "Instead of using powershell, I prefer excel formula to achieve your requirement in a faster way"
Sometimes it hurts experts who are taking time to answer and at the end receiving this type of feedback. And you did not post any logic in powershell which is better than my comment. I would have agreed you. I respect you btw.
My point is that accessing Excel via VBS or PowerShell or any other external scripting language is much slower than doing stuff in Excel itself.
ASKER
Sorry, do you mean write a macro within in excel, I'm looking to automate the process, so it can be run on demand. Can I store macros and add & execute then within the workbook?
It's not the macro. It's the formula given by Qlemo and you need to write in the cell where you like the results to appear ie sheet1 cell D3
ASKER
If what needs to counted is in a second sheet, would the countif statement look like this?
= CountIfS(B:B;sheet2,"boom" ;I:I;"dyna mite")
= CountIfS(B:B;sheet2,"boom"
No. In Excel you address the cells on a different sheet like Sheet1!A1, so:
= CountIfS(Sheet2!B:B, "boom", I:I, "dynamite")
(the semicolons in my prior post were wrong).
= CountIfS(Sheet2!B:B, "boom", I:I, "dynamite")
(the semicolons in my prior post were wrong).
ASKER
that returns me a zero? should the count if see both colums and all rows?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.