CODEWITHSUNDEEP
excel-formula
Adam Scot
Adam Scot

Reputation: 1409

Find all other cells with same adjacent element

I have an excel spreadhseet with the following columns

For example, the result may look like this:

    City             State                Other cities in State
    --------------------------------------------------------------
    Philadelphia     Pennsylvania        Pitsburgh
    Pitsburgh        Pennsylvania        Philadelphia
    San Diego        California          Palo Alto, Mountain View, LA, San Jose, Houston
    Palo Alto        California          San Jose, Mountain View, San Diego
    Mountain View    California          San Jose, LA, Palo Alto, San Diego
    LA               California          San Jose, Mountain View, Palo Alto, San Diego
    San Jose         California          LA, Mountain View, Palo Alto, San Diego
    Austin           Texas               Houston, Dallas
    Houston          Texas               Austin, Dallas
    Dallas           Texas               Dallas, Houston

What formula could I use to generate the 'other cities in state' column?

Upvotes: 1

Views: 57

Answers (1)

user4039065
user4039065

Reputation:

Ragged edge string concatenation is difficult using Excel worksheet functions; even with the new Excel 2016/Office 365/Excel Online CONCAT and TEXTJOIN functions¹.

A well written UDF² can easily overcome the limitations.

Module1 code sheet

Option Explicit

Function CITYJOIN(rst As Range, sst As String, rct As Range, _
                  Optional sct As String = "", _
                  Optional bIncludeSelf As Boolean = False, _
                  Optional delim As String = ", ")
    Dim r As Long
    Static dict As Object
    If dict Is Nothing Then
        Set dict = CreateObject("Scripting.Dictionary")
        dict.compareMode = vbTextCompare
    End If

    dict.RemoveAll
    'truncate any full column references to the .UsedRange
    Set rst = Intersect(rst, rst.Parent.UsedRange)
    'set the cities to the same size as the states
    Set rct = rct.Resize(rst.Rows.Count, rst.Columns.Count)

    'loop through the cells to create unique dictionary keys
    For r = 1 To rst.Cells.Count
        If LCase(rst(r).Value2) = LCase(sst) Then
            dict.Item(StrConv(rct(r).Value2, vbProperCase)) = vbNullString
        End If
    Next r

    'get rid of 'self-city'
    If Not bIncludeSelf Then
        dict.Remove sct
    End If

    'return a delimited string
    CITYJOIN = Join(dict.keys, delim)

End Function

      cityjoin

The optional city (e.g. sct ) is only optional if you choose to include the city on the same row. By default the city on the same row is excluded and must be supplied as a parameter in order to remove it.

The static dict object means that you will only create the Scripting.Dictionary object once. The same object is used for subsequent calls to the function. This is particularly useful when filling down a long column with a formula containing this UDF.

¹ See What's new in Excel 2016 for Windows for more information.

² A User Defined Function (aka UDF) is placed into a standard module code sheet. Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the function code into the new module code sheet titled something like Book1 - Module1 (Code). Tap Alt+Q to return to your worksheet(s).

Upvotes: 1

Related Questions