INDIRECT function

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Find the longest/smallest consecutive sequence of a value (vba)

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Split text across columns

6 Responses to “Split text across columns”

  1. Ingolf says:

    Hi Oscar,

    To avoid array formula,and use Text to columns, can be used:

    Sub Splitcell()
    [A1].TextToColumns [A1].Offset(, 2), xlDelimited, , , 0, 0, 1, 0, 0
    End sub

    PS
    Your site is excellent. One of the best of the best.

    Ingolf

  2. Two points about your SplitValues UDF...

    First, when the Split function will be called only once during a procedure, there is no need to assign the output from the Split function to an intermediary dynamic array in order to work with its output (doing so similar to selecting a range and then woring with the Selection object instead of the range itself); rather, you can work directly with the Spiit function itself...

    Function SplitValues(a As String, b As String)
      SplitValues = Split(b, a)
    End Function
    

    Second, the way you have implemented your function, you need to know, in advance, how many substrings the 'b' argument will split into so that you can select exactly that amount of cells to array-enter your formula in, otherwise, any excess selected cells will display the #N/A error which, of course, looks kind of ugly but would be necessary if the text passed into the 'b' argument could vary in the number of delimiters over time. We can eliminate the #N/A errors for any excess selected cells fairly easily using the Application.Caller object like so...

    Function SplitValues(a As String, b As String)
      SplitValues = Split(b & String(Application.Caller.Count, a), a)
    End Function
    
  3. Ingolf says:

    [quote]
    Second, the way you have implemented your function, you need to know, in advance, how many substrings the 'b' argument will split into so that you can select exactly that amount of cells to array-enter your formula in, otherwise, any excess selected cells will display the #N/A error which, of course, looks kind of ugly but would be necessary if the text passed into the 'b' argument could vary in the number of delimiters over time.
    [/quote]

    Not too sure..

    Sub SplitCell()
    With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .TextToColumns .Offset(, 2), xlDelimited, , , 0, 0, 1, 0, 0
    End With
    End Sub

    • I was referring to problems associated with implementing the UDF in my previous posting... macros (like what you posted) are completely different than UDFs and do not have the same (or virtually any such) restrictions. As for determining the vertical extent of the cells to apply the Text-To-Columns method to... that is not necessary as Text-To-Columns will only work on cells with data, so applying it to the entire column will work the same as restricting it to your calculated range...

      Columns("A").TextToColumns .Offset(, 2), xlDelimited, , , 0, 0, 1, 0, 0

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Count the number of cells within a range that match multiple comma separated values

3 Responses to “Count the number of cells within a range that match multiple comma separated values”

  1. As long as the Values list in Column A is in alphabetical order, this formula appears to work...

    =SUMPRODUCT(0+(LOOKUP(TRIM(MID(SUBSTITUTE(","&B2,",",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*999,999)),A$2:A$20)=TRIM(MID(SUBSTITUTE(","&B2,",",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1))*999,999))))

    • Oscar says:

      Rick,

      Impressive formula! I had to remove a leading blank in cell A8 to make it count correctly. Perhaps wordpress filtered out some html characters from your formula again?

      I don´t know why I made the values in col A in alphabetic order, it was not my intention.

  2. Vernita says:

    That looks really good.

    Is there a way to extract the values (rather than count them) and place the results in column C?

    For example:

    Cell B3 ("jj,oo,pp").

    In column C3, it returns "2" as 2 of the values in cell B3 were found in column A

    However, I am after which 2 values were found in column A

    i.e. I am after a formula that returns "jj,pp") in cell c3 rather than "2"

    is this possible?
    thanks

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Find the longest/smallest consecutive sequence of a value

2 Responses to “Find the longest/smallest consecutive sequence of a value”

  1. Chris Macro says:

    This reminded me of a article I wrote a while back, only I took the perspective of using this methodology to calculate win/loss streaks for a sports team. There's a lot of cool stuff you can do with this consecutive sequence formula!

    http://www.thespreadsheetguru.com/blog/2014/6/29/formulas-to-calculate-longest-current-win-streaks?rq=streak

    • Oscar says:

      Chris Macro,

      Yes, there is a lot of cool stuff you can do I only wish I could come up with shorter formulas.

      The FREQUENCY function really saved me, I thought quite a while before figuring out how to solve this problem.

      Thanks for sharing your post.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file