Solved

Excel vlookup using Powershell

Posted on 2014-04-01
17
1,255 Views
Last Modified: 2014-04-06
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.
0
Comment
Question by:patelbg2001
  • 7
  • 6
  • 4
17 Comments
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39969043
For more clarity, can you post sample file please.
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39969072
TRY THIS:
$filename="C:\test\lookuppowershell.xlsx"

$xlCellTypeLastCell = 11
$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open($filename)
$ws = $wb.worksheets | where {$_.name -eq "sheet2" }
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$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).FormulaLocal=$count
      #$strServer
      #Your Code Here
$ws.SaveAs($filename)
$sws.SaveAs($filename)
$xl.Quit()
0
 
LVL 6

Author Comment

by:patelbg2001
ID: 39969076
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

Open in new window

0
 
LVL 6

Author Comment

by:patelbg2001
ID: 39969079
What I'm looking to do is summarise my findings in sheet 1 based on data within the different sheets
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39969087
Have you tried my script? whats the outcome?
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39969092
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?
0
 
LVL 6

Author Comment

by:patelbg2001
ID: 39969119
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
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39969190
tried my script?  i guess it should work that way
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 68

Expert Comment

by:Qlemo
ID: 39969449
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;"dynamite")
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39969501
@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.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39969674
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.
0
 
LVL 6

Author Comment

by:patelbg2001
ID: 39970173
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?
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39970301
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
0
 
LVL 6

Author Comment

by:patelbg2001
ID: 39970424
If what needs to counted is in a second sheet, would the countif statement look like this?

   = CountIfS(B:B;sheet2,"boom";I:I;"dynamite")
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39970531
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).
0
 
LVL 6

Author Comment

by:patelbg2001
ID: 39971755
that returns me a zero? should the count if see both colums and all rows?
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39971911
Yes, both columns and all rows of those columns. Note that all conditions have to match for being count in. And column I is on the same sheet as the formula - if you want to change that, you need to provide the sheet again:
 = CountIfS(Sheet2!B:B, "boom", Sheet2!I:I, "dynamite")
and so on.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now