• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • 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
  • 3
  • 2
1 Solution
QlemoBatchelor and DeveloperCommented:
A simple formula would suffice, so why the need for PS? Not difficult, but slow in PS.
techdriveAuthor Commented:
I would rather have PS that a formula in excel.
QlemoBatchelor and 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
$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)
Remove-Variable excel, wb, ws, row

Open in new window

techdriveAuthor Commented:
Excellent sir
techdriveAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

We Need Your Input!

WatchGuard is currently running a beta program for our new macOS Host Sensor for our Threat Detection and Response service. We're looking for more macOS users to help provide insight and feedback to help us make the product even better. Please sign up for our beta program today!

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