Help with formula
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Help with formula

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all. Any help is greatly appreciated on this:

    I'm working with a database consisting of one and two digit numbers, if more than one number, it is separated by the || characters.

    One column of the database looks something like this:

    DATA
    1
    1||2
    2
    1||2||3||4||5||6
    1||2||3||4||5||7
    1||2||3||4||5||6||8
    1||2||3||4||5||6||9

    To do my analysis, I needed to count all the 1's in this column, all the 2's etc.etc.

    I've been using this formula to do this:
    =SUMPRODUCT((ISNUMBER(SEARCH("||"&1&"||","||"&A1:A9&"||"))+0))

    My dilemma now is that I need to filter this by using a database formula of some sort and add criteria. If these were single numbers I'd use a formula such as =DCOUNT(A1:D9,1,G1:G2) where G1:G2 is my criteria range.

    Does anyone have any suggestions for a database formula I can use that would do the same thing as my SUMPRODUCT formula above?

    Again, any help is GREATLY appreciated. Thanks!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jennn:
    How about using Autofilter with a custom criterion, to say apply a filter to select only those rows that contain 2; and then with the filtered data highlited go to StatusBar and look for the count of items filtered ... as an example
    original data
    DATA
    1
    1||2
    2
    1||2||3||4||5||6
    1||2||3||4||5||7
    1||2||3||4||5||6||8
    1||2||3||4||5||6||9

    now filtered data (with custom filter contains 2 applied)
    DATA
    1||2
    1||2||3||4||5||6
    1||2||3||4||5||7
    1||2||3||4||5||6||8
    1||2||3||4||5||6||9

    and from the StatusBar I read the Count=6

    HTH

    Please post back if it works for you ... otherwise explain a litle further and let us take it from there!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jennn:
    Subsequent to my posting I notice that the lone 2 in the extreme left position was not picked up ... I wonder why?

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    London
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not quite sure I follow completely but won't this be much simpler if you convert your data using the "text to colums" feature and specify the delimiter character as ?

    You should then be able to use the COUNTIF function.

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi DAWSON:
    By the way DATA|TEXT_to_COLUMN will not accept the || (double split vertical bar) as a delimiter ... but that's no problem, one can easily deal with that.
    After that there is smooth sailing with COUNTIF()
    Now JENNN knows whether doing this is feasible since it would involve creating several additional columns to accomodate the parsed data.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com