Build list based on partial text but multiple results

[Shaun Allan]

I have a tab with a list of files in multiple folders, with the folder name too. On another tab I have a list of notification numbers, eg. 12345678. Multiple files (lines) will have the notification number with the file name:
Filename:
-026D0003 12523032 IWS.pdf
-026D0003 12523032 TQ 2016-INT-013.pdf
-026D0003 13074692 THO.pdf
-026D0003 13104853 REV 01 WSE.pdf
-026D0003 13104853 WSE.pdf
-026D0003 13169779 NDT Request.pdf

To the right of these filenames is the folder they're in. The filename above is made up of item number, notification number, description.

So, on the second tab, I have the notification number. Using VLOOKUP with a wildcard, I could pull up the first instance where the notification number occurs in the filename, returning the folder name. Unfortunately, this only shows the first instance of the notification number, not all of them.

Notification number list:
-12523032
-13074692
-13169779

Is there a way I can build a list of the folders (second column to filename) for each occurrence of the notification numbers, not just the first one?

 

[L. Howard]

Maybe some form of this on your sheets, with a FIND formula.

Howard

Excel 2012
	A	B	C	D	E	F	G	H
2	-026D0003 12523032 IWS.pdf				Yes			12523032
3	-026D0003 12523032 TQ 2016-INT-013.pdf				Yes			13074692
4	-026D0003 13074692 THO.pdf					Yes		13169779
5	-026D0003 13104853 REV 01 WSE.pdf
6	-026D0003 13104853 WSE.pdf
7	-026D0003 13169779 NDT Request.pdf						Yes
Sheet1

Cell Formulas
Range	Formula
E2	=IF(COUNT(FIND($H$2,A2))>0, "Yes", "")
F2	=IF(COUNT(FIND($H$3,A2))>0, "Yes", "")
G2	=IF(COUNT(FIND($H$4,A2))>0, "Yes", "")

 

[portews]

This VBA solution will create a Summary sheet that will list the notification number then the files they are in. I'm assuming that the item number, notification number and filenames are in separate columns. It might help if you use the Tabelizer listed below to show how the data is formatted when you post.

Try this:

Sub findfiles()
'http://www.mrexcel.com/forum/excel-questions/959879-build-list-based-partial-text-but-multiple-results.html
Dim source As Worksheet
Dim summary As Worksheet
Dim Rng As Range
Dim uniquearray() As Variant
Dim lastCol As Integer


ReDim Preserve uniquearray(0)


Set source = ActiveSheet


'go thru each item number
For i = 1 To ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    'go thru each number in the list
    For j = 1 To UBound(uniquearray)
        'if the item number is not in the array
        If Cells(i, 2) = uniquearray(j) Then
            GoTo quitJloop
        End If
    Next
    'add one spot to the array
    ReDim Preserve uniquearray(UBound(uniquearray) + 1)
    'put the new item number in the array
    uniquearray(UBound(uniquearray)) = Cells(i, 2)
    'go to the next item number
quitJloop:
Next


'create a new "Summary" sheet
ActiveWorkbook.Sheets.Add.Name = "Summary"
Set summary = Worksheets("Summary")
'put the unique numbers in a column
For j = 1 To UBound(uniquearray)
    summary.Cells(j, 1) = uniquearray(j)
Next


'set the search range for the account name
With source.Range("B1:B" & source.Cells(source.Rows.Count, "B").End(xlUp).Row)
    'go thru each unique number
    For i = 1 To summary.Cells(summary.Rows.Count, "A").End(xlUp).Row
        'find each match in the data sheet
        Set Rng = .Find(What:=summary.Cells(i, 1), _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        'if there is a match
        If Not Rng Is Nothing Then
            'set the last found range
            FirstAddress = Rng.Address
            Do
                'find the last column for that row in the summary sheet
                lastCol = summary.Cells(i, summary.Columns.Count).End(xlToLeft).Column
                'put the file name in the summary page, offset from the account number
                Cells(i, lastCol + 1) = Rng.Offset(0, 1)
                Set Rng = .FindNext(Rng)
            'keep looping until you don'f find any more matches
            Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
        End If
    Next i
End With
End Sub