Count duplicate values only once across two columns

Question

Hello

As per the screen shot below, I have created two pivot tables with certain data selected.

I now need to please put a formula (?) into a cell, in a separate worksheet, that counts the duplicate values.

As you can see, from the conditional highlighting in column A, I'm expecting the formula to return the value '13'.

Thank you

Answer 1

Hi,

In cell D2, enter this formula

=COUNTA(FILTER(A2:A6,COUNTIF(B2:B8,A2:A6)>0))

Hope this helps.

Answer 2

Hi JessB,

Based on your description, it seems like you want to count the number of duplicates. If so, kindly have a try with following steps.

1.Add a column besides the original data(like the B column in your scenario) and make it all to 1

2.Add a column to use the VLOOKUP function =VLOOKUP(A2,E2:F21,2,FALSE), A2 means the cell to judge if it is a duplicate value(need to change it to A3 when it comes to the next cell to have a judgement), E2:F21 means the range of the original data(no need to change as the original data won't change) and 2 means we want the result in the second column in this range.

3.For the rest of the cells needed to run the vlookup function, only change the first value in the formula(check my screenshots below)

4.Now we get the column of 1(means this cell is a duplicate) or N/A(means this cell is not a duplicate)

5.To get the result number about how many duplicates, use the formula =COUNT(C2:C16)

Here are the screenshots of my test results and hope these could offer some help on your scenario. Some function articles for your reference: VLOOKUP function - Office Support (microsoft.com) and COUNT function - Office Support (microsoft.com).

 

Also, any ideas from members in this forum are welcomed to share in here.

Best Regards,

Mia

Answer 3

Hi JessB,

May I know if you concern has been resolved by trying Ashish's suggestion? Please feel free to post back if you need further help.

Best Regards,
Mia

Answer 4

Hi Ashish

I finally got some time to try this out and it worked perfectly.

Thank you,

Answer 5

Hi Mia

Yes, I've just replied now.

Thank you,