Counting unique rows

  • I have a column of names related by a column of numbers corresponding to each name. Each name is connected to a number and the same number can repeat. I want to count how many times a name occurs removing duplicates.

  • Welcome to Ozgrid!


    Please attach a sample workbook (click the "Attachments" link at the bottom left of the reply box and browse to your sample file)


    Clearly show the desired result.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • OK, I assume that all the colours in column B will have a number in column C and you are looking for a result that will give counts of each unique colour/number combinations. Is that correct?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re,


    Is the formula in Message # 4 producing your expected result ... or not ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • When I applied this formula to a large data set it returned 0, could be because it only works for manually selected rows


    1. Have you adjusted the ranges to their actual sizes ...?


    2. Have you noticed in cell G2 ... the word Blue ... cannot be written Blue :

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 1. Have you adjusted the ranges to their actual sizes ...?


    2. Have you noticed in cell G2 ... the word Blue ... cannot be written Blue :

    1. I adjusted the ranges and I still receive 0, it only works on a small data set

    2. No I haven't, I'm not sure why it can't be written like that

  • 1. I adjusted the ranges and I still receive 0, it only works on a small data set

    2. No I haven't, I'm not sure why it can't be written like that



    The word in the reference cell G2 must be written exactly in the same way as it appears in your list ... in order to be correctly identified ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Looks like the *(B1:B10=G2) part of the formula is returning 0 for all values even for the value in G2

    If you highlight exactly this portion (B1:B10=G2)


    and Hit F9 to evaluate ...


    you should see : {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • If you highlight exactly this portion (B1:B10=G2)


    and Hit F9 to evaluate ...


    you should see : {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}

    Then for my data set the return values are all false when selecting rows A1:A34538. The value I am looking for occurs 8 times within the column with duplicates. I replaced G2 with the value I am looking for using the same format.

  • Try this, click the button on the sheet to display counts


    Code assigned to the button is

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Try this, click the button on the sheet to display counts


    Code assigned to the button is

    I wanted to count one value but this would be fine, thank you