Count and List Duplicates

[marko2002]

Hi Guys, as always I need to turn to the experts for some advice regarding Excel . . . here's the scenario . . . I have just exported over 10,000 users and groups from multiple domains for a project I'm working on and got the results into numerous spreadsheets.

One of the columns list's all the groups available on the domain along with the users who are members of these groups. Unfortunately though, the users are listed seperately and have a row each - for example . . .

GroupName Username
Group1 User 1
Group1 User 2
Group1 User 3
Group1 User 4
Group2 User 1
Group2 User 2

SO, what I need is a way of counting how many instances of the group occurs, and also find some way of listing the various groups along with their associated instances, and using the example above this would look something like . . .
Group1 4
Group2 2
. . and so on!

It doesn't matter if the results are on a different sheet or can be integrated on the same sheet as the data, but I'm pulling my hair out trying to figure this out!!.

I was originally intending on using the
=COUNTIF(C335:C20331,"Group1")
formula, though there are basically too many groups to list manually and so would need to have some form of automation involved in these spreadsheets.

many Thanks in advance guys.
Marko

 

[xlbo]

have a look at PivotTables

Use GROUPNAME as your ROW item
COUNT of GROUPNAME as VALUE

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dcompto]

Using your example above, you could also...

Data > Subtotals
At each change in: GroupName
Use function: Count
Add subtotal to: checkmark UserName
checkmark Replace current subtotals
then collapse the view to level 2

If you also want a bottom line of Grand Count 6, then also checkmark Summary below data

 

[marko2002]

Guys, many thanx for the fast reply, as always it's much appreciated - I started using xlbo's reply using a seperate sheet for the calculations and was blown away with the results though I will try your suggestion also dcompto on my main sheet which would be a nice "tot" for those working from that particular sheet - cannot thank u guys enough for this, you have just saved me weeks of endless copying, pasting and not to mention coffee overdose!.