Welcome Guest
Log In | Register )
You last visited December 10, 2016, 2:50 am
All times shown are
Eastern Time (GMT-5:00)

Excel split

Topic closed. 19 replies. Last post 3 years ago by SergeM.

Page 2 of 2
3.33
PrintE-mailLink
SergeM's avatar - slow icon.png
Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
Posted: April 25, 2014, 12:27 pm - IP Logged

A^^(-1).A=I

    SergeM's avatar - slow icon.png
    Economy class
    Belgium
    Member #123700
    February 27, 2012
    4035 Posts
    Offline
    Posted: May 2, 2014, 5:00 pm - IP Logged

    Yes I remember this from the Lottery Master Guide!

    I think it went something like

    A^2 + B^2 = C^2

     Your smart picks master guide that just didn't do it for you?

     I think that the wheels do it, not the picks.

      SergeM's avatar - slow icon.png
      Economy class
      Belgium
      Member #123700
      February 27, 2012
      4035 Posts
      Offline
      Posted: May 5, 2014, 2:47 pm - IP Logged

      Small concatentation workshop with addin function JoinElements(rowmatrix,delimiter)

      &ABCDEF
      1 1-2-3-4-5
      2 5-6-7-8-9
      3{=JoinElements(TRANSPONEREN(TRANSPONEREN(A1:E1));"-")}
      4{=JoinElements(TRANSPOSE(TRANSPOSE(A1:E1)),"-")}

      You need two nested Transpose() functions to generate a rowmatrix from a row of cell values.
      Add a delimiter like for example "-". Push CTRL+SHIFT+ENTER to finish the cell input.

      The function will work better than the built in concatenate function. This is an exagerated example to show the efficiency of the join function:

      1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20-21-22-23-24-25-26-27-28-29-30-31-32-33-34-35-36-37-38-39-40-41-42-43-44-45-46-47-48-49-50-51-52-53-54-55-56-57-58-59-60-61-62-63-64-65-66-67-68-69-70-71-72-73-74-75-76-77-78-79-80-81-82-83-84-85-86-87-88-89-90-91-92-93-94-95-96-97-98-99-100-101-102-103-104-105-106-107-108-109-110-111-112-113-114-115-116-117-118-119-120-121-122-123-124-125-126-127-128-129-130-131

       

        SergeM's avatar - slow icon.png
        Economy class
        Belgium
        Member #123700
        February 27, 2012
        4035 Posts
        Offline
        Posted: May 6, 2014, 3:59 pm - IP Logged

        Mini VBA workshop

        Tired of nesting? Why not create your own function?
        Instead of nesting two Transpose() functions, we can do the nesting with VBA in the VBE.

        'Returns an array or matrix m*n
        Public Function ToMatrixMN(TheRange)
           ToMatrixMN = WorksheetFunction.Transpose(WorksheetFunction.Transpose(TheRange))
        End Function

        This can also be written as follows:

        'Returns an array or matrix m*n
        Public Function ToMatrixMN(ByVal TheRange As Range) As Variant
           ToMatrixMN = WorksheetFunction.Transpose(WorksheetFunction.Transpose(TheRange))
        End Function

        On the worksheet, type =ToMatrixMN(A1:B3) followed by CTRL+SHIFT+ENTER, to enter the function into one or more cells.

        You can now do: =JoinElements(ToMatrixMN(A1:B1),"+").
        If you want no separation between the elements, then use "" as separator.

          SergeM's avatar - slow icon.png
          Economy class
          Belgium
          Member #123700
          February 27, 2012
          4035 Posts
          Offline
          Posted: May 8, 2014, 10:55 am - IP Logged

          Assignment: Split some text to characters. Put the characters into cells.

          x123
          1231             
          23
          abcabc

          Use the worksheetfunction MID().

          =MID($A2,B$1,1)