+ Reply to Thread
Results 1 to 5 of 5

Counting unique values from data on TWO separate excel tabs

  1. #1
    Registered User
    Join Date
    06-10-2009
    Location
    Austin, TX
    MS-Off Ver
    Professional Plus 2010
    Posts
    21

    Counting unique values from data on TWO separate excel tabs

    Hi everyone,

    In the attached file, the problem is listed on Tab 1.

    I just need to find a way to count unique values but using a formula that would look up data in both tabs at the same time. The correct answer is provided in Tab 1. Can someone enter the required formula in the two highlighted yellow cells?

    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    564

    Re: Counting unique values from data on TWO separate excel tabs

    Attached does this.

    I had to create helper columns to do this. in the first tab i've joined A B C and D columns together as one string. In the next column I can check each row to see if the string exists in the previous rows. If yes then it is left blank, if not it joins A B and C together (ABC together will be used later). Then in Tab 2 i've done the same again but the fomula first checks tab 1 then tab 2 to decide if this row is a unique occurence.

    Then you can use a formula to count the number of times A B anc C joined together occur (ie. IA BB 2013 is searched for in Col F but joined together to become IABB2013) in the same in tab 2 and add these together to get your result.

    Hope that made sense!
    Attached Files Attached Files
    Say thanks, click *

  3. #3
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Counting unique values from data on TWO separate excel tabs

    Hello,

    Check if the attached file is what you are expecting.

    I literally made 3 array formula for each case: counting unique in tab 1, counting unique in tab 2, sum them together then minus the duplicated in both tab. There is no helper column.
    Attached Files Attached Files
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  4. #4
    Registered User
    Join Date
    06-10-2009
    Location
    Austin, TX
    MS-Off Ver
    Professional Plus 2010
    Posts
    21

    Re: Counting unique values from data on TWO separate excel tabs

    Thanks Harribone, your formula solved the issue!

  5. #5
    Registered User
    Join Date
    06-10-2009
    Location
    Austin, TX
    MS-Off Ver
    Professional Plus 2010
    Posts
    21

    Re: Counting unique values from data on TWO separate excel tabs

    Thanks Lemice, your suggestion works as well, and it also gives me something to learn further (arrays).

    I'll mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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