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
techdriveAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
techdriveAuthor Commented:
Excellent sir
0
 
techdriveAuthor Commented:
.
0
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.

All Courses

From novice to tech pro — start learning today.