Excel macros multiple columns to a single column and delete a repetitive range of columns.

When I’m not fighting the war on bad call centers I try to assist my better half with some of her work.  Lately this has involved a lot of spreadsheets and data manipulation, which is something I enjoy as it’s a bit different than what I do day-to-day. The latest issue was turning a dataset from rows to columns. Transpose, you say? I wouldn’t be posting this if it would have been that easy.  The dataset looked like this:

A 7 2 9
B 3 4 5
A 9 9 2
C 3 2 3
C 1 3 9
B 1 2 2

The good thing is that the data had the same number or rows for every entity, in my case 10 rows belonged to each entity.  The bad news is that transposing got me from 10 rows to 10 columns.  The first thing I did was sort the rows by entity to group all entity rows together and did a transpose. 

AABBCC
793131
224223
925239

Next, I went to Google for some help and came up with the following macro:

Sub ManyColumnsTo1()
Dim LR As Long, index1 As Long, index2 As Long, x As Long, y As Long, z As Long, maxCol As Long
x = 2
y = 10
z = 1
maxCol = 50
‘ First entry
    For index1 = x To y
        LR = Cells(Rows.Count, index1).End(xlUp).Row
        Range(Cells(1, index1), Cells(LR, index1)).Copy
        Cells(Rows.Count, z).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Next index1
‘ All others
For index2 = x To maxCol
    x = x + 10
    y = y + 10
    z = z + 10
    For index1 = x To y
        LR = Cells(Rows.Count, index1).End(xlUp).Row
        Range(Cells(1, index1), Cells(LR, index1)).Copy
        Cells(Rows.Count, z).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Next index1
Next index2
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
On Error GoTo 0
End Sub

This macro will copy a range of columns into one column.  The code will copy y columns, starting with column x into column z.  The for loop will then cycle through maxCol to ensure you get every column in your dataset. Which then gives us the following.

AABBCC
793131
224223
925239
A  B  C
9  1  1
2  2  3
2  2  9

Ultimately, this is what I wanted, but I had a lot of unnecessary columns in the middle which we needed to get rid of.  Enter a column delete macro:

Sub DeleteColumn()
    Dim startCol As Long, endCol As Long, maxCol As Long
    startCol = 1
    endCol = 10
    maxCol = 50
    For i = startCol To maxCol
        ActiveSheet.Range(Cells(1, startCol + 1), Cells(10, endCol)).EntireColumn.Delete
        startCol = startCol + 1
        endCol = endCol + 1
    Next i
End Sub

This macro will delete a range of columns.  The deletion will start with startCol through endCol for maxCol.

Which gives us the following.

ABC
733
242
953
ABC
911
223
229

One more transpose and some cleanup and we’re good to go.  Hope this helps others.

david

Check Windows 2003 Service Status Remotely

At my current project there are times where I have to restart a bunch of servers at a time.  On these servers there is a particular service which I want to make sure it has started and is running.  There’s the sc.exe command which allows your stop, start, query service statuses from the command prompt.

Here’s a small batch script I wrote which runs through a list of server names and prints out the status.  You’ll need two files.

First file includes the server names and is called Servers.txt:

Server1
Server2
Server3
Server4

The second file just needs to be saved as a .bat file.

   1:  REM ***Start Here***
   2:  Echo Off
   3:  Setlocal EnableDelayedExpansion
   4:  FOR /F "Tokens=*" %%L IN (Servers.txt) DO (
   5:     SET ServerName=
   6:     SET ServerName=%%L
   7:     sc.exe \\!ServerName! query <Your Service Name> > Results.txt
   8:     ECHO !ServerName!
   9:     Find /i "RUNNING" < Result.txt
  10:     Find /i "STOPPED" < Result.txt
  11:  )
  12:  IF EXIST Results.txt DEL Result.txt
  13:  REM ***END HERE***

This script will print out the server name and if your particular service is RUNNING or STOPPED.  You can expand this as you would like.

~david

2 Months with Sprint’s HTC Touch Pro 2

If you recall last year I posted a few Tweets about the, then upcoming, Palm Pre and how much I was looking forward to this phone.  Later on I posted a story about about my impressions with the Palm Pre.  After a few months of sticking with it and really giving it a try I had to let it go and go back to Windows Mobile.  Yes, I am a Microsoft slave when it comes to phones.  I missed the integration with Exchange, I missed the seamless connectivity with Outlook, I missed being able to tweak it to try and make it far more efficient and faster.

It now has been around two months since I’ve made the switch and I can’t really complain, I got everything I expected from Windows Mobile.  First, let’s start with the phone.

The HTC Touch Pro 2 retails for $599.99 at Sprint.com.  Yes, that’s $600 dollars for a phone!  Yes, I thought it was insane when I saw the price tag.  Not only is/was the phone incredibly pricey, it was close to impossible to find it at Sprint stores.  The excuse I always got was that since it was so pricey not a lot of stores carried it and it was considered a specialty item.  Good thing I didn’t ask for diamond encrusted keys.  Regardless, I turned to my trusted friend Craig Newman and his awesome website http://craigslist.org/ and found someone letting go of their phone for $250.  Finding the guy, haggling with him, and finally getting the phone is a whole other story, but ultimately I paid the asking price and began my journey back to the dark side.

Happy with my purchase I decided that this phone had so much potential, which was being thrown away by the stock operating system provided by Sprint.  First and foremost, there was Windows 6.5 and the phone runs Windows 6 stock.  Second, there was HTC’s Sense UI, among other tweaks available which would put this phone over the top when it came to usability.

When it comes to hacking your phone and making it do things it might not be supposed to do, you head over to the always awesome and informative, http://forum.ppcgeeks.com/.  After a few days of research I decided to give Mike and his http://www.mightyrom.com/ a spin.  Flashing your phone is not for the faint of heart, I was pretty nervous something would happen in the middle of the flash which would render my phone useless, but thankfully everything has been great so far and I’ve enjoyed what my phone has become.

The key features I love about this phone:

– Great voice quality, including the speaker.

– A keyboard, I love having a physical keyboard.

– Very responsive, more than my Palm Pre.

Cons:

– This thing is a brick, it’s huge.

– It’s Windows, which needs a kick to restart it ever few days… well maybe about once every two weeks.

– Windows Marketplace sucks.

~david

Microsoft’s SyncToy in Windows XP 64

Well, for those of you struggling trying to get SyncToy working on Windows XP 64, here is the fix.

Download custsat.dll version 9.0.3790.2428 and replace it in the SynToy install directory (usually C:Documents and Settings<usernam>Local SettingsApplication DataSyncToy)

Cheers.

~david

Windows 2003 Server

So I finally cracked down and started playing with the future of the Windows Servers. I am talking about the Windows 2003 server. Read on to find out what some of my initial thoughts on this new operating system.



edit: 04/08/04
Security Focus just posted a new article on the IIS6.0 security :-) Talk about timely.1. It actually loaded pretty fast, which I was surprised, I didn’t use a beast of a machine, but what I did use, seemed to work well and have a nice response time.



2. Security, this has to be the first server where it seems like the computer is tight down out of the box. A couple of ports were open which seem to be unnecessary, but I will not close then until I find out more about the operating system.



3. The interface is a great improvement from that horrible interface called Windows XP.



4. Symantec Antivirus Corporate Edition v8 does not play well with 2003, at least for me, and I am having a hard time automating the virus updates. I think it might have something to do with Windows security.



5. I absolutely love remote desktop connection; MS finally got on the ball and did what PC Anywhere and tightVNC have been doing for years.



6. Sharepoint is nice; I just need to read up on how to configure it correctly.



I don’t see the migration from Windows 2002 Server to Windows 2003 should be too painful. However, I think that with the new IIS 6.0 that my Linux server might end up doing something else.



-david macias

Job Search Information Management

The saying is that treat a job search like a full time job. Right, specially when you already have a job that is close to full time and you’re a full time student. So can you really have 3 full time jobs? Not really.

The point is that I looked around the web for a tool that would help me manage all my information concerning my job search. The tool I am createing will manage:

  • Job Postings
  • Job Contacts
  • Job Leads
  • References
  • Resumes
  • Cover Letters
  • Thank You Letters
  • Read on…So ideally this tool would be web accesible, however due to time constraints and the fact that I have “3” full time jobs, I will be releasing the first version of this tool in MS Access. For those of you who do not have Access, I will release a MySQL version as soon as the Access version is usable.



    This is just a heads up on what is to come at the end of the week, hopefully sooner.

    -david macias

  • Trying out Mozilla Thunderbird for the first time.

    So at work I usee MS Outlook 2003 and Outlook Express for my personal email. So, I just reformatted my machine and decided to try a new/better email client. I had heard a lot about Mozilla Thunderbird so I decided to give a spin. The installation was quite painless, and it is very fast. I am also using it for surfing newsgroups and so far so good. There is somewhat of a learning curve and I am still trying to figure out the new keyboard shortcuts, as well as how NG posts are handled when received. Overall this is definetly a step forward passed Outlook Express. Next, I will try to import all of my old email, as well as try some themes and extensions.

    -david macias

    Going around word document protection.

    So someone sent me a word document that was write protected, and I needed to edit the file, I had no password, and no way of asking for it…So I looked around the web, and they mentioned that there was a pretty easy way to do this:



    Create a new blank document, then insert file, and insert the file that is protected.



    Sure this works, however if your file has images or headers and footers, they sometimes get lost in the process. So there has to be a better way.



    Here it is:



    Save your protected file in RTF format. Then using good old Notepad, open that file up. Look for the following string of text:

    
    formprot/allprot/
    

    Delete that, save the file, open the file, and now you can edit your file.


    This has been tested only with Office XP. If you notice some different behavior let me know.



    -dm