Google Answers Logo
View Question
 
Q: Reformatting Excel database files ( Answered 5 out of 5 stars,   2 Comments )
Question  
Subject: Reformatting Excel database files
Category: Computers > Programming
Asked by: simoneau-ga
List Price: $30.00
Posted: 26 Aug 2005 15:44 PDT
Expires: 25 Sep 2005 15:44 PDT
Question ID: 560956
I have a very time consuming task of reformatting an Excel database
with over 13700 rows.  The rows ar formatted as follows:

 --------------------------------------------------
| First-Name   | Last-Name | First-Name | Last-Name |
 ---------------------------------------------------
| John & Mary  | Doe       |            |           |
 ---------------------------------------------------
| James & Anne | Sanderson |            |           |
 ---------------------------------------------------

I need to reformat the above example to read:

 --------------------------------------------------
| First-Name   | Last-Name | First-Name | Last-Name |
 ---------------------------------------------------
| John         | Doe       | Mary       | Doe       |
 ---------------------------------------------------
| James        | Sanderson | Anne       | Sanderson |
 ---------------------------------------------------

My first inclination was to write a program that would search for the
'&' character, read the name that followed, and copy it into the
second |first-name| field.  It would then copy over the first
|last-name| field over to the second |last-name| field. I first saved
the Excel document as an XML file for to make it easier to parse
through the fields.  The program seems to reformat the database
correctly, but Excel is unable to open the file, giving me this error
message multiple times:

XML ERROR in Table
REASON:	Bad Value
FILE:	C:\Documents and Settings\tyler\Desktop\fixed.xml
GROUP:	Row
TAG:	Cell
ATTRIB:	Index
VALUE:	48

My question is this: Are there any existing programs or formulas
available that can accomplish this task in Excel?  If not, will you be
willing to to read my source code and tell me my error?  The program
was written in C, and is not very long.  If there is no solution other
than writing a custom program, please respond with a request for the
source code, that is, if you are willing to read it :) .

Regards,

Tyler

Clarification of Question by simoneau-ga on 26 Aug 2005 16:20 PDT
I do realize that visual basic offers a few functions for dealing with
Excel documents.  I'm just not familiar with VBA.  It may be easier
for you to just write a program in VBA to accomplish the task.

-Tyler

Request for Question Clarification by pafalafa-ga on 26 Aug 2005 16:29 PDT
Try saving just the first column (of first names) as a .txt file.

Then, open that file in a new excel file.  The import wizard should
open up, asking how to import the file.  Tell it to separate columns
at the "&" sign, and it should split the first names in just the way
you want, into two columns.

Once they're split, you can then copy the columns into your original
file and you're 90% of the way there.

Try it out.  And if my explanation is too brief, let me know, and I'll
try to elaborate.


Good luck,

pafalafa-ga

Clarification of Question by simoneau-ga on 26 Aug 2005 17:47 PDT
pafalafa-ga:

That is a good start, but there is a slight complication.  The example
I gave below of the rows:

--------------------------------------------------
| First-Name   | Last-Name | First-Name | Last-Name |
 ---------------------------------------------------
| John & Mary  | Doe       |            |           |
 ---------------------------------------------------
| James & Anne | Sanderson |            |           |
 ---------------------------------------------------

Sometimes is already separated.. meaning it really looks like this:

--------------------------------------------------
| First-Name   | Last-Name | First-Name | Last-Name |
 ---------------------------------------------------
| John & Mary  | Doe       |            |           |
 ---------------------------------------------------
| James & Anne | Sanderson |            |           |
 ---------------------------------------------------
| Adam         | Smith     |  Jane      | Smith     |
 ---------------------------------------------------
| Guy          | Berry     | Lisa       | Berry     |
 ---------------------------------------------------

Is there any way, once I split the rows using your method, to not just
copy and paste the new column back in, but to copy and APPEND the data
to the current column, allowing for a "mesh" effect.  In otherwords,
it would weave the column we just created with the original.  Is this
possible?

Clarification of Question by simoneau-ga on 26 Aug 2005 19:12 PDT
The problem, actually, is that I need a program to automate the task. 
Automation is essential because the supplier needs to do this
freequently.  If this is not really possible, then I assume that I
need to refine my program myself.

Thanks,

Tyler
Answer  
Subject: Re: Reformatting Excel database files
Answered By: hammer-ga on 27 Aug 2005 13:52 PDT
Rated:5 out of 5 stars
 
Simoneau-ga,

This VBA routine should do what you want. It looks at each used row in
your spreadsheet. For each row, it looks at the value in the first
column. If that value contains an & sign, it puts everything before
the & in column 1, everything after the & in column 3, and copies the
value in column 2 (the last name) into column 4.

Beofre running the code, make sure you have a backup copy of your workbook.

To run the code, open your workbook and go to the worksheet you need
to reformat. Press Alt-F11 to get the VBA window. In the tree on the
left side, you will see a list of your worksheets. Double-click on the
worksheet you want to affect. Paste in the code below and press F5 to
run it. That should reformat your worksheet as you describe.
  

Public Sub DoIt()
Dim vVal As String
Dim vPos As Integer

For Each rw In Me.UsedRange.Rows
    vVal = rw.Cells(1, 1).Value
    vPos = InStr(vVal, "&")
    If vPos > 0 Then
        rw.Cells(1, 1) = Trim(Left(vVal, vPos - 1))
        rw.Cells(1, 3) = Trim(Mid(vVal, vPos + 1))
        rw.Cells(1, 4) = rw.Cells(1, 2).Value
    End If
Next

End Sub

Good luck with your Excel project!

- Hammer


Search strategy
----------------
None. Reproduced and solved problem in Excel.

Request for Answer Clarification by simoneau-ga on 27 Aug 2005 15:27 PDT
Nice script..Where did you learn this? Are there any particular VBA
books that focus on Excel that you would recommend?

Thanks,

Tyler

Clarification of Answer by hammer-ga on 27 Aug 2005 15:33 PDT
Simoneau-ga,

I've been writing VBA for over ten years. I also speak several other
programming languages. One learns the tools after a while.

It's been a long time since I cracked a general book on VBA, but I
remember that, when I was first learning to use Office and VBA, I
liked the Step-By-Step series from Microsoft Press.

- Hammer
simoneau-ga rated this answer:5 out of 5 stars and gave an additional tip of: $5.00
Excellent work Hammer.  Thanks for the great script, it works perfectly.

-Tyler

Comments  
Subject: Re: Reformatting Excel database files
From: smallbusinessjim-ga on 30 Aug 2005 07:37 PDT
 
Another option using some of the same code might be to create a custom
function that could be reused.

Function FirstName(name)
    vPos = InStr(name, "&")
    If vPos > 0 Then FirstName = Trim(Left(name, vPos - 1))
End Function

Function LastName(name)
    vPos = InStr(name, "&")
    If vPos > 0 Then LastName = Trim(Mid(name, vPos + 1))
End Function

Then if cell A1 contains "John & Sue", cell B1 contains
"=FirstName(A1)" and cell C1 contains "=LastName(A1)", Cell B1 will
return John and cell C1 will return "Sue".
Subject: Re: Reformatting Excel database files
From: myexpertsonline-ga on 30 Aug 2005 23:50 PDT
 
hammer: Probably completely against the rules, right? But I'd love to
hear from you. I'll wager you can find me.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy