Solved

Excel Formulas for formatting

Posted on 2011-02-23
23
265 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 2013 Problem 12 48
Excel VBA - format a Shape same as a Cell 7 23
Formula to copy cell and its "format" 3 26
onOpen 14 43
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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

19 Experts available now in Live!

Get 1:1 Help Now