Distinct values across multiple columns with occurrence count to populate ListBox

AliO

New Member
Joined
Mar 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi there, first post on here and pretty novice at Excel VBA.
I found some great code which works really well for outputting a single column's distinct values with a count of occurrences (used for sort order) to a ListBox via another worksheet but I was wondering how I could expand the range to link multiple columns.
I am trying to improve a data entry spreadsheet so that if a user searched for a geographic place name in the ListBox it would also fill country and all the administrative subdivisions into their respective cells in the active cell row. The maximum columns I would need to use is 4 in the range("U2:Y" & LastRow) Where U is the highest level (country) and the user would be searching from the lowest level available from the source material.
Any thoughts/pointers would be greatly appreciated! :)
Thanks in advance, Ali

VBA Code:
Sub Summarize(rngSource As Range, rngTarget As Range)
    Dim d As New Scripting.Dictionary
    Dim rng As Range
    Dim var As Variant

    For Each rng In rngSource
            If d.Exists(rng.Value) Then
                d(rng.Value) = d(rng.Value) + 1
            Else
                d.Add rng.Value, 1
            End If
    Next rng

    rngTarget = "Value"
    rngTarget.Offset(, 1) = "Count"
    Set rng = rngTarget.Offset(1)
    For Each var In d.Keys
        rng = var
        rng.Offset(, 1) = d(var)
        Set rng = rng.Offset(1)
    Next
End Sub
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,127,172
Messages
5,623,178
Members
415,955
Latest member
Footballtend

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