Solved

powershell numbering excel fields automatically

Posted on 2015-01-25
5
55 Views
Last Modified: 2015-02-06
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
Comment
Question by:techdrive
  • 3
  • 2
5 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 40570131
A simple formula would suffice, so why the need for PS? Not difficult, but slow in PS.
0
 

Author Comment

by:techdrive
ID: 40570295
I would rather have PS that a formula in excel.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40570961
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
 

Author Comment

by:techdrive
ID: 40571573
Excellent sir
0
 

Author Closing Comment

by:techdrive
ID: 40594542
.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

816 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

8 Experts available now in Live!

Get 1:1 Help Now