Solved

Excel Formulas for formatting

Posted on 2011-02-23
23
268 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
  • 13
  • 10
23 Comments
 
LVL 10

Expert Comment

by:cdebel
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:cdebel
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
 
LVL 10

Expert Comment

by:cdebel
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:cdebel
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:cdebel
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:cdebel
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:cdebel
ID: 34963703
When you say that you tried it, did you tried it in your workbook or mine?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 10

Expert Comment

by:cdebel
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:cdebel
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:
cdebel earned 500 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:cdebel
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:cdebel
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:cdebel
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 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

15 Experts available now in Live!

Get 1:1 Help Now