VBA Excel 2010 - "Crosstab" in Excel

Posted on 2011-04-22
Last Modified: 2012-05-11
Dear Experts,

Can you please have a short look on the attached file, basically if somebody is familiar its a download from SAP, where items have stock on different storage locations, but being in a single field.

The pattern of it is always

1) text "SLOC"
2) number of the SLOC and ":"
3) stock
4) ","

So for example the cell "SLOC4:-2.947, SLOC3:201.121, SLOC1:3.414" means that item has minus 2.947 stock on SLOC4, stock 201.121 but on SLOC3, and 3.414 on SLOC1.

Could you advise whether with VBA or formula, is it possible to write some logic which would result as I did manually the C-D-E-F columns? So cutting the stock numbers out and putting under the certain SLOC column.

I assume rather it is complex but maybe someone has idea

Question by:csehz
    LVL 30

    Accepted Solution

    Is this What you want?

    Sample Attached. I have written a Excel Function for you.


    Code Used

    Public Function GetSloc(rng As Range, rnglookup As Range) As Double
        Dim MyArray() As String, TempArray() As String
        On Error GoTo Whoa
        '~~> Check if there is more than 1 SLOC Value
        If InStr(1, rng.Value, ",") Then
            MyArray = Split(rng.Value, ",")
            For i = 0 To UBound(MyArray)
                If InStr(1, MyArray(i), rnglookup.Value, vbTextCompare) Then
                    TempArray = Split(MyArray(i), ":")
                    GetSloc = Val(TempArray(1))
                    Exit Function
                End If
            Next i
            If InStr(1, rng.Value, rnglookup.Value, vbTextCompare) Then
                rng.Value = Replace(rng.Value, rnglookup.Value, "", , , vbTextCompare)
                GetSloc = Val(Trim(rng.Value))
            End If
        End If
    End Function

    Open in new window

    LVL 2

    Assisted Solution

    here u go!!!!!!!!

    use this formula!!!

    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    use this formula in c3 and copy it down and across

    LVL 1

    Author Closing Comment

    This forum is amazing :-) From where I should have 1500 points? :-)))

    I even almost did not have dare to put this question, and got three working solutions in so short and almost the same time. I think the fair to split the points as equal, thanks very much.

    And Happy Easter too :-)
    LVL 30

    Expert Comment

    Both the formulas with fail for values like

    SLOC3:-9.32615623, SLOC2:93

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    How come Siddharth? it works fine with me.
    LVL 30

    Expert Comment

    My Mistake. Yeah you are right. I didn't autofit the columns. It was showing truncated values :)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

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

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now