# ranking most reoccuring duplicates

#### mani_singh

##### Well-known Member
i need to rank duplicates in most reoccuring format

any ideas?

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### Lewiy

##### Well-known Member
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
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
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
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
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
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

##### Well-known Member
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 !

Replies
6
Views
231
Replies
3
Views
355
Replies
4
Views
532
Replies
5
Views
618
Replies
3
Views
504

1,186,365
Messages
5,957,445
Members
438,306
Latest member
Crystal_Blue

### 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?

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