+ Reply to Thread
Results 1 to 6 of 6

Count Matches Between 2 Columns

  1. #1
    Registered User
    Join Date
    03-11-2019
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Count Matches Between 2 Columns

    I'm having issues counting matches between 2 columns. Both of the columns dynamically shorten, so there are blanks at the end.

    Here's the code I'm using:
    Please Login or Register  to view this content.
    If one column has blanks at the end, it works fine. If both have blanks, it's counting the blanks as matches. I'm struggling with how to include an AND statement that excludes "" or blank values. Any help?
    Last edited by DarrenPO; 03-20-2019 at 03:18 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Count Matches Between 2 Columns

    Maybe...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Assuming that blank appears only at end of the range.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Count Matches Between 2 Columns

    Are the blanks the result of formulas? I am not having that issue working with straight blank cells.

    You can use dynamic ranges, maybe something like

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where SetA is defined in Name Manager as

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    03-11-2019
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: Count Matches Between 2 Columns

    Ok I just ran a test with both of our formulas, and they work correctly with simple text in each column with blanks. The problem I guess is in my formula in each of those columns. I'm using the INDEX and SMALL functions in each list:

    Please Login or Register  to view this content.
    So I'm thinking my "blanks" at the end of the range are not really blanks.

  5. #5
    Registered User
    Join Date
    03-11-2019
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: Count Matches Between 2 Columns

    Quote Originally Posted by ChemistB View Post
    Are the blanks the result of formulas? I am not having that issue working with straight blank cells.

    You can use dynamic ranges, maybe something like

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where SetA is defined in Name Manager as

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks, I'll try this.

  6. #6
    Registered User
    Join Date
    03-11-2019
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: Count Matches Between 2 Columns

    I was using google sheets, so I don't think I could make dynamic ranges as I couldn't put a formula in the Name Manager. I was able to solve my count issue by having one column output " ", and the other output "", so the blanks did not match and count.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to count matches between multiple columns on different sheets w/ multiple criteria
    By RICK JAMES in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-05-2019, 03:28 PM
  2. Replies: 6
    Last Post: 07-14-2016, 11:54 AM
  3. Replies: 2
    Last Post: 09-15-2014, 09:58 AM
  4. Searching for matches from 2 columns and copying data from matches
    By Jesper Daelin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-01-2014, 10:49 AM
  5. Replies: 1
    Last Post: 09-26-2013, 12:16 PM
  6. Replies: 0
    Last Post: 08-11-2012, 03:25 PM
  7. Excel 2007 : Vlookup to count matches in two columns
    By notwen in forum Excel General
    Replies: 4
    Last Post: 06-10-2011, 06:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1