One way using UDF
In G2 and drag down 40 rows (or as far as you need):
=top($B$2:$B$36,ROWS($1:1))
with B2:B36 is street name range, rows() generate 1,2,3...
and code of that function: (Alt-F11 to open VBA window, insert/module, copy this code into)
PHP Code:
Option Explicit Function Top(ByVal rng As Range, n As Integer) Dim i&, j&, k&, name, dic As Object, res(1 To 10000, 1 To 2), tmp1, tmp2 Set dic = CreateObject("Scripting.Dictionary") name = rng.Value For i = 1 To UBound(name) If Not dic.exists(name(i, 1)) Then dic.Add name(i, 1), 1 + i / 100 k = k + 1: res(k, 1) = name(i, 1): res(k, 2) = 1 + i / 100 Else dic(name(i, 1)) = dic(name(i, 1)) + 1 For j = 1 To k If res(j, 1) = name(i, 1) Then res(j, 2) = res(j, 2) + 1 Next End If Next For i = 1 To k - 1 For j = i + 1 To k If res(i, 2) < res(j, 2) Then tmp1 = res(j, 1): tmp2 = res(j, 2) res(j, 1) = res(i, 1): res(j, 2) = res(i, 2) res(i, 1) = tmp1: res(i, 2) = tmp2 End If Next Next Top = IIf(n <= dic.Count, res(n, 1), "") End Function
Last edited by bebo021999; 03-29-2023 at 11:22 PM.
Bookmarks