Find values and count duplicates in a sheet

    I would like to find the values and count duplicates on a sheet. Here is my example:


    [TABLE="width: 500"]

    [tr]


    [td]

    A1

    [/td]


    [td]

    A2

    [/td]


    [/tr]


    [tr]


    [td]

    Organization1

    [/td]


    [td]

    Organization2

    [/td]


    [/tr]


    [tr]


    [td]

    XXY

    [/td]


    [td]

    BBA

    [/td]


    [/tr]


    [tr]


    [td]

    ACC

    [/td]


    [td]

    XXY

    [/td]


    [/tr]


    [tr]


    [td]

    BBA

    [/td]


    [td]

    ABC

    [/td]


    [/tr]


    [/TABLE]


    I would do countif, but I do not know my values (this is from a report that I ran from a database) and it would take too long to find all the individual values every time I needed to run this report. So this is what I am looking for (a graph would work too).


    [TABLE="width: 500"]

    [tr]


    [td]

    A3

    [/td]


    [td]

    A4

    [/td]


    [/tr]


    [tr]


    [td]

    ABC

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    ACC

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    BBA

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    XXY

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    Thanks in advance!

    Re: Find values and count duplicates in a sheet


    in C2 put
    =IFERROR(IFERROR(INDEX($A$2:$A$100, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$100), 0)), INDEX($B$2:$B$100, MATCH(0, COUNTIF($C$1:C1, $B$2:$B$100), 0))), "")
    enter using
    control+shift+enter as an array formula
    in D2 put
    =IF(C2="","",COUNTIF($A$2:$B$100,C2))

    ETAF

    Re: Find values and count duplicates in a sheet


    Thanks for the formula. If I use that formula then I would have to copy the two cells into cell C then remove duplicates, then add the formula. It just seams like a lot of steps that I am hoping to avoid.

    Re: Find values and count duplicates in a sheet


    sorry dont understand the question, the formula removes all the duplicates from the two lists


    perhaps if you loaded a sample spreadsheet

    ETAF

    Re: Find values and count duplicates in a sheet


    When I downloaded your excel the formula in cell C wasn't showing up. And I couldn't see the body of your message, it just said "ggg". I have been playing with the formula's, but I haven't been able to get them to work.


    I have attached the example I am working from. It will be over 200 people if I run the full report.


    Thanks!!

    Re: Find values and count duplicates in a sheet


    That is wonderful, thank you! Ideally we would be able to count the duplicates in all three orgs. but I see if I try to include all three it says there are too many arguments.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login