+ Reply to Thread
Results 1 to 2 of 2

Counting if a unique identifier corresponds to another unique identifier more than once

  1. #1
    Registered User
    Join Date
    02-16-2017
    Location
    New York, NY
    MS-Off Ver
    MS Office 2010
    Posts
    4

    Question Counting if a unique identifier corresponds to another unique identifier more than once

    I'm trying to determine how to count the number of times a text value in ID1 matches up with a text value in ID2, but the problem is that the values aren't necessarily unique. See below for some sample data:

    COLUMN A (ID1): SLS.123, SLS.123, SLS.123, SLS.234, SLS.123, SLS.123, SLS.299, SLS.123, SLS.299, SLS.333

    COLUMN B (ID2): GPS.111, GPS.222, GPS222, GPS.222, GPS.333, GPS.333, GPS.444, GPS.444, GPS.444, GPS.555

    These are hypothetical text values and will not be arranged in any specific order. I'm basically trying to see how many matches a given text value from ID1 (i.e., SLS.123) has across unique values from ID2. Another way to think about this is how many times does SLS.123 have a unique relationship with each unique text value in ID2. Please note that ideally, I'd like to avoid counting duplicates. See below:

    Example Output:
    SLS.123 = 4

    Because:
    SLS.123 & GPS.111
    SLS.123 & GPS.222
    SLS.123 & GPS.333
    SLS.123 & GPS.444

    and
    SLS.123 & GPS.222 (2) not counted
    SLS.123 & GPS.333 (2) not counted

    I've tried all sorts of iterations of COUNTS/COUNTIFS/SUM(IF(FREQUENCE, but nothing seems to work. Any help on this would be greatly appreciated!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Counting if a unique identifier corresponds to another unique identifier more than onc

    Hi alekos7 and welcome to the forum.

    Is this the outcome you envisioned?

    Row\Col
    A
    B
    C
    D
    1
    ID1 ID2 ID1 ID2
    2
    SLS.123 GPS.111 SLS.123 GPS.111
    3
    SLS.123 GPS.222 SLS.123 GPS.222
    4
    SLS.123 GPS.222 SLS.234 GPS.222
    5
    SLS.234 GPS.222 SLS.123 GPS.333
    6
    SLS.123 GPS.333 SLS.299 GPS.444
    7
    SLS.123 GPS.333 SLS.123 GPS.444
    8
    SLS.299 GPS.444 SLS.333 GPS.555
    9
    SLS.123 GPS.444
    10
    SLS.299 GPS.444
    11
    SLS.333 GPS.555


    After trimming and cleaning the data in columns A:B I array entered this formula in C2 filled down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

+ 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. Replies: 1
    Last Post: 01-29-2015, 04:08 PM
  2. Unique Identifier
    By mussonip in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-07-2014, 10:46 AM
  3. [SOLVED] Counting a column of numbers based on a unique identifier
    By aaanenson in forum Excel General
    Replies: 2
    Last Post: 08-23-2012, 09:01 PM
  4. Unique identifier for a row
    By drdavidge in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-12-2010, 11:52 PM
  5. [SOLVED] Unique identifier
    By Steve Barnett in forum Excel General
    Replies: 19
    Last Post: 01-06-2006, 07:30 AM
  6. Unique identifier
    By Steve Barnett in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-06-2006, 07:30 AM
  7. [SOLVED] Unique identifier
    By Steve Barnett in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 01-06-2006, 07:30 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