Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

powershell numbering excel fields automatically

Good day folks I have a challenge that I have been given and need some assistance in resolving this issue. I have an excel spreadsheet with the following columns named emailaddress and batchnumbers. What I would like to do is to be able to on a fly be prompted for a batch number. The batch number depending on what it is will uniquely distinguish those group those users to the same batch. For example lets say I want to batch/group the users below every 5 users.

emailaddress                            batchnumber
user1@microsoft.com             uniquebatch1
user2@microsoft.com             uniquebatch1
user3@microsoft.com              uniquebatch1
user4@microsoft.com             uniquebatch1
user5@microsoft.com             uniquebatch1
user12@microsoft.com           uniquebatch2
user31@microsoft.com           uniquebatch2
user14@microsoft.com           uniquebatch2
user18@microsoft.com          uniquebatch2
user23@microsoft.com          uniquebatch2
user33@microsoft.com          uniquebatch3
user40@microsoft.com          uniquebatch3
user19@microsoft.com          uniquebatch3
user2@microsoft.com            uniquebatch3
user31@microsoft.com          uniquebatch3


Or if I wanted to batch 50 users per batch it will automatically be prompted for the amount of users and automatically do this. Any assistance would be great, thanks
0
techdrive
Asked:
techdrive
  • 3
  • 2
1 Solution
 
QlemoC++ DeveloperCommented:
A simple formula would suffice, so why the need for PS? Not difficult, but slow in PS.
0
 
techdriveAuthor Commented:
I would rather have PS that a formula in excel.
0
 
QlemoC++ DeveloperCommented:
I'm not clear about what you expect to see as batch number, so I will take over what you used as example, and just append a running number.
$batchsize = Read-Host "What is the size of each batch group?"

$excel = New-Object -ComObject excel.application
$excel.visible=$true
$wb = $excel.WorkBooks.open("C:\Temp\EE\Q_28603713.xlsx")
$ws = $wb.Worksheets.Item(1)
$row = $ws.Range("2:2")
$i = 0
while ($row.Cells.Item(1,1).value2)
{
  $row.Cells.Item(1,2).value2 = "uniquebatch"+ ([math]::floor($i++/$batchsize)+1)
  $row = $row.Offset(1,0)
}
$wb.Save()
$excel.Quit()
Remove-Variable excel, wb, ws, row

Open in new window

0
 
techdriveAuthor Commented:
Excellent sir
0
 
techdriveAuthor Commented:
.
0

Featured Post

Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now