?
Solved

Excel Formulas for formatting

Posted on 2011-02-23
23
Medium Priority
?
274 Views
Last Modified: 2012-06-22
Hello Experts,

I sales order data coming out of the database in just one column. The column contains the order number, the customer and the product. The data comes out tiered, or indented. First is the order number, indented around 20 spaces, then the customer indented around 10 spaces, then the product indented around five. I would like to get this into three columns. Any ideas?

Please see the attachment and you will see how my data comes out of the database, and how I would like it to look.

Thanks.
NOI-Sample.xls
0
Comment
Question by:erp1022
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
23 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34962322
are you open to some VBA solution?

Because it's the only way i would do this kind of thing... but some "Excel Power Users" might find some tricky method to do it without VBA.
0
 

Author Comment

by:erp1022
ID: 34962375
Not sure about the VBA, I have never worked with it before. If it's something that would be fairly straight-forward to implement, I would be open to it.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34962384
Alright.  I'll show you how to put that in your own workbook, it's quite easy.  I'll be back in a couple of min with your solution
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34962456
I've noticed that you don't always have the same number of spaces in your sample.

For example, Johnsmith have 10 spaces before its name, while Janedoe got 9.

Could you tell me exactly how many spaces there's before their names?  
Can you find a pattern (for example, name always have 20 characters and is filled with blank if there's not enough characters in the name)?

Because right now the pattern is not the same all the time...
0
 

Author Comment

by:erp1022
ID: 34962569
It's actually 20 spaces for Order, 15 for Customer and 10 for Product.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34962793
Here's what the code look like, and you can place it in ThisWorkbook code:

 
Public Sub Formatting()
    Dim SPC_ORDER As String
    Dim SPC_CUST As String
    Dim sContent As String
    Dim sCustomer As String
    Dim sProduct As String
    Dim nRow As Long
    Dim nFirstOrderCust As Long
    Dim nFirstOrderProd As Long
    
    SPC_ORDER = Space(20)
    SPC_CUST = Space(15)
    
    ' Prepare Sheet2
    With Sheet2
        .UsedRange.Clear
        .Cells(1, 1) = "Product"
        .Cells(1, 2) = "Customer"
        .Cells(1, 3) = "Order Number"
        .Rows(1).EntireRow.Font.Bold = True
    End With
    
    ' Loop thru all the informations to separate them into 3 columns in sheet2
    nRow = 2
    nFirstOrderCust = 2
    nFirstOrderProd = 2
    For Each Cel In Sheet1.UsedRange
        sContent = Cel.Value
        If Left(sContent, Len(SPC_ORDER)) = SPC_ORDER Then
            If nFirstOrderCust = 0 Then
                nFirstOrderCust = nRow
            End If
            If nFirstOrderProd = 0 Then
                nFirstOrderProd = nRow
            End If
            
            Sheet2.Cells(nRow, 3) = Trim(sContent)
            nRow = nRow + 1
        ElseIf Left(sContent, Len(SPC_CUST)) = SPC_CUST Then
            Sheet2.Range(Sheet2.Cells(nFirstOrderCust, 2), Sheet2.Cells(nRow - 1, 2)).Value = Trim(sContent)
            nFirstOrderCust = 0
        Else
            Sheet2.Range(Sheet2.Cells(nFirstOrderProd, 1), Sheet2.Cells(nRow - 1, 1)).Value = Trim(sContent)
            nFirstOrderProd = 0
        End If
    Next
    Sheet2.Columns.AutoFit
End Sub

Open in new window


You can see how it's implemented in the file i've attached.  It's done with Excel 2010.

If you are not using Excel 2010, can you tell me which version you are using?
I'll explain you how you can place it in your workbook
SplitIn3Columns.xlsm
0
 

Author Comment

by:erp1022
ID: 34963013
We are using Version 2003.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34963135
ok, then:

Open your workbook
Press Alt-F11 to open VB Editor
On your left, you should see a tree.  Expand VBAProject/Microsoft Excel Objects
Double click on ThisWorkbook in this tree
On your right, you should see a white window where you can put some code.   Copy the code that i've provided you in this window

Now, to call this code, you can associate this piece of code to a button, or just run it as a macro.

Press Alt-F8 to display the macros of your workbook.  You should see ThisWorkbook.Formatting.  Click on it, then click the RUN button.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34963156
If you still have difficulties, you can check at this workbook.  I've saved it in version 2003.

Feel free to ask question if you have difficulties to implement it.
SplitIn3Columns.xls
0
 

Author Comment

by:erp1022
ID: 34963686
When I try to run the macro I get the attached error...




macro-error.bmp
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34963703
When you say that you tried it, did you tried it in your workbook or mine?
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34963746
I've just tried it on a computer with Excel 2003, so i guess there's some typo error on your side if you copied the code in your workbook.   Could you post your workbook?
0
 

Author Comment

by:erp1022
ID: 34963791
I did what you said, I was in my workbook and opened the VB Editor and pasted the code there. Then tried to run the macro.

I really cannot send live data.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34963818
Do you have any protection on your sheet?

Do you have sheet1 and sheet2 in your workbook?  (of course, if you have different names, then you have to change the names!  (Sheet1 is for the source, and Sheet2 is for the sheet who will get the result).
0
 

Author Comment

by:erp1022
ID: 34964127
Nope, no protection.

I do have two worksheets. The one that contains the source data is called SV and the other one is just Sheet2. I can see where Sheet2 is referenced in your code, but not Sheet1.
0
 

Author Comment

by:erp1022
ID: 34964140
Is there any way to parse your code to see where it's failing?
0
 

Author Comment

by:erp1022
ID: 34964225
I just found the debuggin tool and went through the code. It fails on the second pass of the loop. I get to the second line of the Else statement and the error pops up.
0
 

Author Comment

by:erp1022
ID: 34964726
I figured out the issue, there were some blank cells at the top of my column which were causing the error.

Now I am having a new issue. When the macro runs, it formats about half of the file correctly, then it stops with an error. When I look at the data, I think I can see what's causing the issue.

The database generating the file will spit out all of the customers, whether or not they have an order. So the data could look like this:

          Order
          Order
          Order
     Customer1
          Order
          Order
     Customer2
     Customer3
     Customer4
          Order
          Order
          Order
     Customer5
Product

I think it's failing when we have one Customer right after another without any Orders in between.

0
 
LVL 10

Accepted Solution

by:
Christian de Bellefeuille earned 2000 total points
ID: 34964903
You say that you don't see where Sheet1 is refenced in my code.  It's in the FOR loop:
    For Each Cel In Sheet1.UsedRange

When you debug the code, give me the line number where it fail.  Check at my comment 34962793 for the line numbers.

But in your last comment, you seems to have figured out the problem.  If a customer didn't placed any order and figure in your result set, then it will cause an error because nFirstOrderCust will be at 0.  You can't reference a row 0... it start at 1.  

 
Public Sub Formatting()
    Dim SPC_ORDER As String
    Dim SPC_CUST As String
    Dim sContent As String
    Dim sCustomer As String
    Dim sProduct As String
    Dim nRow As Long
    Dim nFirstOrderCust As Long
    Dim nFirstOrderProd As Long
    
    SPC_ORDER = Space(20)
    SPC_CUST = Space(15)
    
    ' Prepare Sheet2
    With Sheet2
        .UsedRange.Clear
        .Cells(1, 1) = "Product"
        .Cells(1, 2) = "Customer"
        .Cells(1, 3) = "Order Number"
        .Rows(1).EntireRow.Font.Bold = True
    End With
    
    ' Loop thru all the informations to separate them into 3 columns in sheet2
    nRow = 2
    nFirstOrderCust = 2
    nFirstOrderProd = 2
    For Each Cel In Sheet1.UsedRange
        sContent = Cel.Value
        If Left(sContent, Len(SPC_ORDER)) = SPC_ORDER Then
            If nFirstOrderCust = 0 Then
                nFirstOrderCust = nRow
            End If
            If nFirstOrderProd = 0 Then
                nFirstOrderProd = nRow
            End If
            
            Sheet2.Cells(nRow, 3) = Trim(sContent)
            nRow = nRow + 1
        ElseIf Left(sContent, Len(SPC_CUST)) = SPC_CUST Then
            If nFirstOrderCust <> 0 Then
                Sheet2.Range(Sheet2.Cells(nFirstOrderCust, 2), Sheet2.Cells(nRow - 1, 2)).Value = Trim(sContent)
                nFirstOrderCust = 0
            End If
        Else
            If nFirstOrderProd <> 0 Then
                Sheet2.Range(Sheet2.Cells(nFirstOrderProd, 1), Sheet2.Cells(nRow - 1, 1)).Value = Trim(sContent)
                nFirstOrderProd = 0
            End If
        End If
    Next
    Sheet2.Columns.AutoFit
End Sub

Open in new window

0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34964926
I've coded based on what you asked.

But may i ask you a question?  Why are you getting the resultset this way?  Do you have any control over this database that you are querying?

Wouldn't it be easier to modify the store procedure that is returning you this kind of result?  Because really, it make no sense to get this kind of result in 1 single column...

You could do an inner join between tables to get all the details from your DB.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34964945
And by the way, in the last code i've provided to you, you see that i check for the nFirstOrderCust and nFirstOrderProd to make sure that they are not equal to 0 now.  (Lines 40 and 45).
0
 

Author Comment

by:erp1022
ID: 34980114
Unfortunately, when you work for the federal government you can't just go around modifying things. It's just not that simple. And I don't even have access to that stored procedure. This is the way it comes from the Hyperion report and there's no changing that. It's based on some end user setup and different hierarchies.

Your code works this time, thanks!

0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34982011
Don't worry, i understand.  I've worked for a long time with pharmaceutical companies, and they have strong security measure too :).

It was just a suggestion.  We usually try to solve the problem at it's source, when we can.

Glad it worked.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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