Posts by rooboy

  • Advanced Filter: Exclude Blank/Empty Cells Criteria

    Re: Advanced Filter


    I know this is an old thread, but I'm not sure if it was ever resolved... Anyway, for anyone that might be looking for an answer (as I was) if you want to use Advanced Filter to isolate blank cells simply enter = with nothing after it. If you want to filter-out blank cells then simply enter <> with nothing after it.

  • Using COUNTIF to loop through multiple sub-sequences

    Re: Using COUNTIF to loop through multiple sub-sequences


    OK, I think I've gotten a little further down the path with this..

    I've added a helper column to differentiate between groups and subgroups.

    This allowed me to reset the countif function X times. The problem is there is not a fixed number of times that the counter can be reset, so the formula defaults one position if the number is exceeded.

    I've attached the updated file.

    I've being trying to avoid VBA, but I think there is no way around it...

    The VBA code should be something like:


    Sub ResetCounter()
    Integer Count


    Counter = 0
    For no change in product group Count
    If the Number of Branches exceeds the Count, Reset Counter
    End If
    End Sub

    But I'm not very experienced with VBA, if anyone can shed some further light it would be great.

    Thanks again, RooBoy.

  • Using COUNTIF to loop through multiple sub-sequences

    Re: Using COUNTIF to loop through multiple sub-sequences


    Hello Ally,

    Yes, that is oversimplifying the problem. I just happen to use three in this example, but really the sequence could vary any number of times.

    The determining factors are Column A which could have X groups of varying sizes and Column D which could have any number of values. These two columns will determine how many times the sequence is reset.

    I think I need to add a "helper" column which provides further sub-grouping. That way the countif criteria can use major and minor subgroups to determine when to reset the count sequence.

    I'll report back if I make any progress...

    Cheers, RooBoy

  • Using COUNTIF to loop through multiple sub-sequences

    Hi All,

    Trying to toggle through a sequence numbers based on a criteria identified in Column A.


    The sequence is reset at each change in product group in Column A.


    The challenge arises when the product groups exceed the numbers to output and we need to loop back to the start.


    For example, when we hit product group Dean (down 6 rows), the output is 15,32,47,32,32,32 instead of 15,32,47,15,32,47 etc


    The sequence needs to continue while there is no change in the criteria but reset to beinginning if there is nothing left to output.

    I've attached a file to better explain the problem.

    Any guidance would be greatly appreciated.

  • Use array of values in Custom function to join strings

    Hello,

    I'm having some problems with the StringConcat macro from Cpearson:

    www.cpearson.com/Excel/stringconcatenation.aspx

    When used in the standard way, it seems to work fine. However, when using an array with multiconditions it seems to fall short or I'm not applying it properly (I'm tipping its the latter).

    For example, the macro seems to work fine like this:

    =StringConcat("|",IF(ISBLANK($B2:$U2),"",MID($B2:$U2,4,2)))

    (array entry with ctr+sht+enter)

    However, if I want the function to return values based on additional conditions, like if results from MID() match a defined name it seems to only output the first two results:

    =StringConcat("|",IF(ISBLANK($B1:$U1),"",IF(MID($B1:$U1,4,2)*1=VIC,MID($B1:$U1,4,2),"")))

    I've tried to simplify the formula to this:

    =StringConcat("|",IF(MID($B3:$U3,4,2)*1=VIC,MID($B3:$U3,4,2),""))

    But encountered the same problems.

    I thought the input array might be getting too large and I was running into some memory limitations, so I reduced the data range and defined name range with the same results.

    The strange thing is, when I "evaluate" the results in excel I can see the correct output, but for some reason only the first two results are displayed and the rest seem truncated?

    I've posted a spreadsheet to better highlight the problem (& stop me from blabbing on)...

  • Non-activex Datepicker Calendar Control

    Re: Non-activex Datepicker Calendar Control


    Hi all, nice work on this! very elegant solution. Just had a quick question, & I'm not sure if this a separate thread, but how easy/hard is it to convert/add international week feature. That is, having option where user can input Week/Year format for the date. Example, 01/10 = Week 1, 2010. More information on international week format here: http://blogs.msdn.com/b/excel/…eek-numbers-in-excel.aspx