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

Find a sequence of values – wildcard search

One Response to “Find a sequence of values – wildcard search”

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 a sequence

One Response to “Find a sequence”

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

Search all workbooks in a folder and sub folders

3 Responses to “Search all workbooks in a folder and sub folders”

  1. Andrew Evans says:

    Have you thought about adding -
    Application.ScreenUpdating = False at the start and
    Application.ScreenUpdating = True at the end
    as it will stop all the frantic screen changes when the macro is running?

    It also left the last file opened open.

    There is also something not quite right if one of the files is password protected - the next couple also showed up as password protected but with a link.

    See below (I have lined up the columns as they appear in the sheet)-
    Search string: Flexi
    Path: C:\Users\AEvans\Documents\
    Folderpath Workbook Worksheet Cell Address Link
    003474-Disc-wastage.xls Password protected
    Access to offices.xls Password protected
    Bank Account Balance.xls Password protected Sheet4 $A$540 Link
    Bank Account Balance2.xlsx Password protected Sheet4 $A$715 Link
    endeavour usage.xls Password protected Sheet4 $A$542 Link
    C:\Users\AEvans\Documents\AEvans\ Accountancy Usage 20030818.xls Sheet4 $A$700 Link
    C:\Users\AEvans\Documents\AEvans\ accountancydiscusage.xls Sheet4 $A$453 Link

    • Oscar says:

      Andrew Evans,

      Have you thought about adding -
      Application.ScreenUpdating = False at the start and
      Application.ScreenUpdating = True at the end
      as it will stop all the frantic screen changes when the macro is running?

      No, I used it for monitoring the process but it is a great idea. It may also speed things up.

      It also left the last file opened open.
      There is also something not quite right if one of the files is password protected - the next couple also showed up as password protected but with a link.

      I will look into that.

      Thank you for commenting!

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