• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Excel Formulas for formatting

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
erp1022
Asked:
erp1022
  • 13
  • 10
1 Solution
 
Christian de BellefeuilleProgrammerCommented:
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
 
erp1022Author Commented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Christian de BellefeuilleProgrammerCommented:
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
 
erp1022Author Commented:
It's actually 20 spaces for Order, 15 for Customer and 10 for Product.
0
 
Christian de BellefeuilleProgrammerCommented:
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
 
erp1022Author Commented:
We are using Version 2003.
0
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
erp1022Author Commented:
When I try to run the macro I get the attached error...




macro-error.bmp
0
 
Christian de BellefeuilleProgrammerCommented:
When you say that you tried it, did you tried it in your workbook or mine?
0
 
Christian de BellefeuilleProgrammerCommented:
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
 
erp1022Author Commented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
erp1022Author Commented:
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
 
erp1022Author Commented:
Is there any way to parse your code to see where it's failing?
0
 
erp1022Author Commented:
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
 
erp1022Author Commented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
erp1022Author Commented:
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
 
Christian de BellefeuilleProgrammerCommented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 13
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now