Dear all,
I'm having difficulties trying to solve one problem.
I have a spreadsheet with B column with more than 2000 different street names (B1 to B2000). I need to have a VBA code that can count the top 20 rank of the most repeated street names and list them on G1 to G20 cells.
Does anyone can help me?
It would help immensely if you could provide a sample file. Knowing the file structure is just as important as knowing the logic requirement. See yellow block above that describes how to upload a file. Make sure to desensitize it.
Dear maniacb
Here I attach a sample file. This is just a sample because the original file has more than 2000 entries. So what I would like to have is a top 20 of the most repeated street names.
Thank you so much
Here is a power query solution. I see in your profile that you are using XL2007. Is this still the case. If not, please update your profile so that contributors can offer solutions that match your version. I have offered a version that will only work with XL2010 or later. I did not notice your version until after I had built a solution.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
Please Login or Register to view this content.
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Dear alansidman,
Thank you so much for your time helping me.
I do indeed have Excel 2016, I will update my profile information.
I've tried your code but I got this error message:
I shall say that my Excel software is in Portuguese, I have translated the error message that I got to english." Expression.Error: Couldn't find a Excel table with the name "Table1".
Details:
Table1 "
Thank you
the range of data that you bring into PQ Editor needs to be named Table1 or you need to change the first line of Mcode to the Table Name you have.
By "range of data that needs to be named Table1" do you mean the spreadsheet name of the Excel file?
No the range of data to be imported to PQ Editor
See the attached file.
Greetings to all.
The attached model is -almost- the same as the one posted by @alansidman.
The important difference is that it tries to "dodge" Power Query's big problem: changing data column titles.
Indeed: a good development can be frustrated if we change -for example- 'Street Name' to 'Street NAME'.
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
Just in case you still want a VBA solution, here is one way to do it:
Please Login or Register to view this content.
Last edited by maniacb; 03-29-2023 at 09:51 PM.
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.
Quang PT
Greetings to all. A VBA solution would approach it like this:
Please Login or Register to view this content.
Dear alansidman, beyond excel, maniacb and bebo021999,
Thank you all so much for the help that all of you guys gave to me.
I now can sort what I really want! Thank you thank you thank you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks