ranking most reoccuring duplicates

[mani_singh]

i need to rank duplicates in most reoccuring format

any ideas?

 

[Lewiy]

Like this perhaps?:

Excel Workbook
	A	B	C	D	E
1	Items		Results	Count	Rank
2	cat		cat	6	1
3	hamster		dog	1	4
4	hamster		hamster	4	2
5	cat		rabbit	3	3
6	rabbit
7	hamster
8	cat
9	rabbit
10	cat
11	rabbit
12	dog
13	cat
14	hamster
15	cat
Sheet1

 

[mani_singh]

thats great but how did you get the left most duplicates to be reduced into a smaller list of 4 unique entries?

is there a way to do this automatically perhaps via formulae of macro?

 

[Lewiy]

Here is a little macro which will do that based on the layout I have used:

Sub PickMyEntries()
Dim Limit As Long
Dim c As Long
Dim d As Long
Limit = Cells(Rows.Count, 1).End(xlUp).Row
d = 2
For c = 2 To Limit
    If WorksheetFunction.CountIf(Range("C:C"), Cells(c, 1)) = 0 Then
        Cells(d, 3) = Cells(c, 1)
        d = d + 1
    End If
Next c
End Sub

You may need to modify it a little depending on how you have your data arranged.

Hope that helps!

 

[barry houdini]

Here's a slightly different approach,

Assume your data is in A2:A100

D1 = "rank", D2 down 1, 2, 3 etc

E1 = "item"

E2

=INDEX(A2:A100,MODE(MATCH(A2:A100,A2:A100,0)))

confirmed with CTRL+SHIFT+ENTER

E3

=INDEX(A$2:A$100,MODE(IF(ISNA(MATCH(A$2:A$100,E$2:E2,0)),MATCH(A$2:A$100,A$2:A$100,0))))

confirmed with CTRL+SHIFT+ENTER and copied down column as far as necessary

F1 = "count"

F2 copied down

=COUNTIF(A$2:A$100,E2)

You should get a list of items in order of the most frequent, with a count of each. Items that only appear once won't be listed

Note: The above assumes that A2:A100 contains no blanks - if it might then change E2 and E3 to the following, still both confirmed with CTRL+SHIFT+ENTER

=INDEX(A2:A100,MODE(IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))))

and

=INDEX(A$2:A$100,MODE(IF(A$2:A$100<>"",IF(ISNA(MATCH(A$2:A$100,E$2:E2,0)),MATCH(A$2:A$100,A$2:A$100,0)))))

 

[Legacy 14611]

Or, if you like macros, try running this code

Sub rankdupes()
nd = [a65536].End(xlUp).Row
a = [a1].Resize(nd, 1)
With CreateObject("Scripting.Dictionary")
For i = 2 To nd
    If Not IsEmpty(a(i, 1)) And Not .exists(a(i, 1)) Then
        .Add a(i, 1), 1
    Else: .Item(a(i, 1)) = .Item(a(i, 1)) + 1
    End If
Next i
[c2].Resize(.Count, 2) = Application.Transpose(Array(.keys, .Items))
[c2].Resize(.Count, 2).Sort Key1:=[d2], Order1:=xlDescending
End With
End Sub

 

[mani_singh]

Thanks everyone for this awsome set of advice, i've sorted it out now ! but if any one has a clue on how this next thing works i'd be greatful

 

[mani_singh]

take the same idea that the first reply has in an image

multiple duplicates (in this case animals)

say we had the animals - each time the animal became ill a vet was called to sort it out. (vet 0, 1, 2 or 3)

there is only one actual animal of each but they become ill many times

each time they are ill a random vet is called and an entry is generated - hence the multiple entries.

i want to sort the list removing duplicates as the first reply adressed.

but i need to table off each of the four animals and calculate horisontally the number of times each vet has sorted each animal

any ideas how to do that? you'd be saving my life here!!

thanks everyone!

 

[mani_singh]

ok try this everyone, what i'm trying to say is:

i want to take this - the number in the first bit signifies the vet number


cat 1
cat 2
cat 2
dog 0
dog 0
dog 1
dog 2
rabbit 3
rabbit 3


and make this - the numbers signify the number of times the particular bet has healed the animal

ANIMAL | VET0 | VET1 | VET2 | VET3

cat 1 0 2 0
dog 2 1 1 0
rabbit 0 0 0 2


THANKS GUYS I REALLY NEED A HAND WITH THIS ONE !