Number of unique matches in two ranges within the same column

[slmayobb]

Hi there,

I have two long columns of data. One column has ID# and the other column has a year (see attachment example with fewer rows). I would like to see how many of the ID#s in one year, also exist in the other year (unique matches only).

In the attached example, the numbers 1, 2, 3, 4, & 5, (column A) all exist for both 2014 (in red) and 2015 (in blue) (column B), so I would like the output of my formula to be 5. Note that I do not want duplicate numbers counted more than once.

Also, because I have a lot of data, it is not ideal for me to select the cells manually, I would prefer to tell excel to search for matching values in two ranges that are conditional on the year in column B. I know how to do this if there are two columns, but I cannot figure out how to specify two ranges conditionally based on the year in column B.

Can someone help me? Do I have to use VBA for this (I have zero experience with VBA) or can I get away with formulas?

Many thanks in advance,
Slmayobb

Screen Shot 2017-05-06 at 10.57.07 AM.png

[sandy666]

is that what you want?

[avk]

As per Sandy file. Mentioned criteria in "D1" & "E1" (2017 & 2016)
In "D2" formula :

Please Login or Register  to view this content.

In "E2" formula :

Please Login or Register  to view this content.

Ensure Array formula [SHIFT+CTRL+ENTER]
I think this you want.

[slmayobb]

Thanks to both of you for your quick help on this. Unfortunately, neither suggestion produced the answer I was hoping. AVK, I have a feeling your formulas were getting closer to what I want, but they both produced a 1. I think I may need to be more clear (and my example could be improved). I'll give it a shot.

I've modified my dummy example a bit. Let's say we are keeping track of the number of points players score in a basketball game over two years. If they score in a game, they are entered into the spreadsheet (with each row representing a single game). When they are entered, we type in their Player#, the amount they scored in total for the game, and the year. Now, I want a formula that will automatically answer the question, "how many players scored at least once in both 2017 AND 2016." Ideally, I'd like to do this in one cell for efficiency.

In this example, players 2, 3, 4, & 5, both scored at least once in 2016 and at least once in 2017. Therefore, I would like my output to be 4. Player 2 scored several times in 2017, but I only want her counted once.

Now imagine we have 4569+ rows of data, instead of just 17.

Unfortunately, my computer is resistant to uploading my excel file on this forum so here is a dropbox link to the file: https://www.dropbox.com/s/8yt3pyjy0m...mple.xlsx?dl=0

Best,
Slmayobb

[sandy666]

To attach sample workbook

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[slmayobb]

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Hi Sandy. Thank you for your comment. This is precisely why I had included a dropbox link in my previous post when I was unable to add an attachment. However, thanks to your kind instructions, I have worked out how to attach the file and have included it here. My previous post outlines exactly what I would like to do. I have no code that I am currently using. Also, as I said in my previous post, I would like the output to be "4" as there are four players who scored in both 2016 and 2017. This is now indicated in red, which is where I would like my formula to be. I hope that answers your requests.

Sincerely,
Slmayobb

[sandy666]

You mean count duplicates only once
try

Please Login or Register  to view this content.

array entered