-1

I have 345 columns with names in each of them. I would like to find a way to pick a column (currently have a drop-down on a separate sheet to make that selection), then find the rest of the columns that contain at least one of the entries in the selected column and list them. For example, I would like to be able to select Col A in this sample:

Sample


and have a list returned with Col B and Col D in it on the sheet with the drop-down selection.

Everything I have found only matches entries that are on the same row and would only return Col D in the sample.

Community
  • 1
  • 1
Jeff
  • 9
  • 1
  • 3
    What do you mean by "returned"? You want to see the names in a modal window? You want to see a new worksheet with just those columns? You want to see those columns highlighted a certain color and focused, in their existing worksheet? – TylerH Jan 18 '18 at 22:44
  • If I can get the column names listed on the sheet with the drop-down lists, that would be great. I'll edit the question to be more clear. – Jeff Jan 18 '18 at 22:49

1 Answers1

0

this code uses regex to match the names. so you have to enable regex under extras --> references and select "Microsoft VBScript Regular Expressions 5.5". (read more about RegEx)

Sub MatchNames(col As Range, ws as Worksheet)
Dim regEx As New RegExp
Dim lRow As Long, lCol As Long
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Dim exp As String: exp = "(" & Join(Application.Transpose(ws.Range(ws.Cells(2, col.Column), ws.Cells(lRow, 1))), "|") & ")"
exp = Replace(exp, " ", "")

With regEx
    .Global = True
    .MultiLine = False
    .IgnoreCase = True
    .Pattern = exp
End With
Dim matched As String
For i = 1 To lCol
    lRow = ws.Cells(ws.Rows.Count, i).End(xlUp).Row
    Dim data As String: data = Join(Application.Transpose(ws.Range(ws.Cells(2, i), ws.Cells(lRow, i))), ";")
    data = Replace(data, " ", "")
    If regEx.Test(data) Then
        matched = matched & ws.Cells(1, i).Value & ";"
    End If
Next i
MsgBox ("Matched Columns:" & vbNewLine & Replace(matched, ";", vbNewLine))
End Sub

The parameters are any cell of the column with the names to match and the worksheet the data is in, i.e Call MatchNames(Worksheets(1).Cells(1,1), Worksheets(1)) to match Col A on worksheet 1. it will then return a message box with all columns that had a match. What it basically does is to create a pattern with all names of that column and this will be matched with a joined string of all cells of column to match. As it only takes n steps for n columns the runtime should be pretty good aswell for all sizes of data.

Plagon
  • 2,689
  • 1
  • 11
  • 23