ranking most reoccuring duplicates

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Like this perhaps?:
Excel Workbook
ABCDE
1ItemsResultsCountRank
2catcat61
3hamsterdog14
4hamsterhamster42
5catrabbit33
6rabbit
7hamster
8cat
9rabbit
10cat
11rabbit
12dog
13cat
14hamster
15cat
Sheet1
 

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Here is a little macro which will do that based on the layout I have used:
Code:
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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)))))
 
L

Legacy 14611

Guest
Or, if you like macros, try running this code
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

Well-known Member
Joined
Jul 24, 2007
Messages
583

ADVERTISEMENT

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

Well-known Member
Joined
Jul 24, 2007
Messages
583
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! :confused:
 

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
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 :confused: !
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,885
Messages
5,766,936
Members
425,388
Latest member
Cave_Johnson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top