Resolving multiple search results

[Dan Wilson]

Good day. This may take a while to explain. I am running Excel out of Office365 (updated) on Windows 10 Home. I have created a worksheet titled Music Database that contains 8 File Properties extracted from my Music folder that contains 4500 songs in MP3 format. The Macro examines the Music folder and creates a worksheet titled DB to list the data. I also have a worksheet titled Song List to list songs of my choosing using XLOOKUP in a formula for each desired File Property that searches the DB worksheet and returns the desired File Property listed in the DB worksheet. This all works. The next step is the problem. In the DB worksheet there are several songs that have identical Titles, but different Artists. When the search takes place for a given Title, the first match found is the one entered into the Song List. The Music folder contains two File Properties, "Name" and "Title". The Name property contains the actual file name along with the File Type. ie: "Anytime (1).MP3" - Since this song was recorded not only by Eddie Fisher but also by Journey, the Name for the file includes separators (1) and (2) to allow duplicate names to be filed. There are also some songs with three different artists Using (1), (2), and (3) as separators. To avoid having to enter the complete File Name including the separator and the File Format to make the XLOOKUP work, the Macro that creates the DB worksheet extracts the File Property "Title" instead of the "Name". The Title in each of the songs with multiple Artists stores only the song Title without the separators. Thus, when the DB worksheet is created, multiple songs are listed with the same Title. That means that when I execute a search for a song with multiple artists, the result is always the first one found. Is there a way to modify the Macro to indicate to me the fact that there are multiple artists for that song and allow me to choose which one I want? I realize that it might not be able to make that happen. Any help is appreciated.
Thank you,
Dan Wilson...

 

[mumps]

I'm not sure I can offer a solution but it would be easier to help if you could upload a copy of your file including the current macro to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Dan Wilson]

Good day mumps. Thank you for responding. Per your request, I will try to make this work. I have made Print Screen PDF files for the parts of my program that need explanation.


Let’s start with the Music Folder. The folder contains over 4500 songs all in MP3 format. The link for Drop 1 is immediately below this paragraph. It shows several songs from the Music folder. The two songs of interest are “Anytime (1).MP3” and “Anytime (2).MP3”. These are the same song by two different artists. The separators “(1)” and “(2)” enable the Music folder to have two songs with the same Title. The Column Headers at the top of the PDF indicate the File Properties of interest. Take note that the data in the Title Column shows the same title for both entries “Anytime”. The Artist Column shows two different Artists.


Drop 1

Drop 1.pdf


Next is the workbook for the Music Database. This workbook contains two worksheets. The first worksheet is for reference and the creation of the second worksheet titled DB. Each time the Music folder has songs altered, removed, or added, the Macro creating the DB worksheet first deletes the current DB worksheet and creates a new one extracting eight File Properties from the Music Folder for all 4500 songs. The link for Drop 2 is immediately below this paragraph. It shows the portion of DB containing the two songs “Anytime”. Note here that Column A contains two identical entries. This is because the Macro creating the DB worksheet extracts the song title from the Title File Property rather than the Name File Property. This gets rid of the separators and the .MP3 format. Notice also that Column C contains the digit “2” to indicate that there are two versions of this Title.


Drop 2

Drop 2.pdf


Last is the workbook for my Song List. The link for Drop 3 is immediately below this paragraph. This workbook allows me to enter a Title for a desired song in Column A and Columns B through G then contain XLOOKUP formulas to search the DB worksheet for a match to the title in Column A. The problem at hand is right here. When the search is done to match for the Title, the first record found that makes a match is the one that is used to fill in Columns B through G. In the case of the song “Anytime”, the match is made for the song by Eddie Fisher and the one by Journey is ignored.


Drop 3

Drop 3.pdf


I tried modifying the Macro in the Workbook that creates the DB. It works, but I then have to enter not only the song title, but also the separator and the file format. If there is a way to have the XLOOKUP formula modified to find all matches for a given song title, that would solve the problem. Perhaps a Macro could be written to find all matches for a given song and then allow me to pick the one that I want to use. I realize that I am asking a lot. I hope this helps explain the issue.


Thank you,
Dan Wilson…

 

[mumps]

Since I don't have the actual files, I couldn't test this macro. But if it works properly, you won't need the xlookup formulae. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet in the Song List workbook and click 'View Code'. Paste the macro into the empty code window that opens up. Change the name of the Music Database workbook (in red) to suit your needs. Close the code window to return to your sheet. Both the Song List workbook and the Music Database workbook must be open. Enter a title in column A in the Song List and press the RETURN key or TAB key. The data should be populated automatically. If this doesn't work for some reason, please upload copies of the Excel files of the the Song List workbook and the Music Database workbook (not pdf's or pictures).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim rName As Range, srcWS As Worksheet, sAddr As String
    Set srcWS = Workbooks("Drop2.xlsx").Sheets("DB")
    Set rName = srcWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not rName Is Nothing Then
        sAddr = rName.Address
        Do
            Range("B" & Target.Row).Resize(, 2).Value = Array(rName.Offset(, 1), rName.Offset(, 2))
            Range("D" & Target.Row).Value = rName.Offset(, 5)
            Range("E" & Target.Row).Resize(, 2).Value = Array(rName.Offset(, 3), rName.Offset(, 4))
            Range("G" & Target.Row).Value = rName.Offset(, 6)
            Set rName = srcWS.Range("A:A").FindNext(rName)
        Loop While rName.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True
End Sub

 

[Dan Wilson]

Since I don't have the actual files, I couldn't test this macro. But if it works properly, you won't need the xlookup formulae. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet in the Song List workbook and click 'View Code'. Paste the macro into the empty code window that opens up. Change the name of the Music Database workbook (in red) to suit your needs. Close the code window to return to your sheet. Both the Song List workbook and the Music Database workbook must be open. Enter a title in column A in the Song List and press the RETURN key or TAB key. The data should be populated automatically. If this doesn't work for some reason, please upload copies of the Excel files of the the Song List workbook and the Music Database workbook (not pdf's or pictures).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim rName As Range, srcWS As Worksheet, sAddr As String
    Set srcWS = Workbooks("Drop2.xlsx").Sheets("DB")
    Set rName = srcWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not rName Is Nothing Then
        sAddr = rName.Address
        Do
            Range("B" & Target.Row).Resize(, 2).Value = Array(rName.Offset(, 1), rName.Offset(, 2))
            Range("D" & Target.Row).Value = rName.Offset(, 5)
            Range("E" & Target.Row).Resize(, 2).Value = Array(rName.Offset(, 3), rName.Offset(, 4))
            Range("G" & Target.Row).Value = rName.Offset(, 6)
            Set rName = srcWS.Range("A:A").FindNext(rName)
        Loop While rName.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True
End Sub

Good day mumps. Thank you for not only responding, but teaching me a whole new function in Excel. I don't totally understand yet what the code does, but I realize a way that I can make it work. First, I had to change
Set srcWS = Workbooks("Drop2.xlsx").Sheets("DB")
to look for .xlsm instead of .xlsx as the workbook contains Macros. Then I discovered that the code was finding the last of multiple versions of a song. Any song with 2 versions found the second one in the list and any song with three versions found the third song in the list. I then temporarily modified the Title in the DB worksheet to add the separators to the Title, but not the .MP3 format. then by entering "Anytime (1)" or "Anytime (2)", the correct data was found for each one. I have no problem with editing the Title File Property in the Music Folder to add the separators where needed and apparently, that will solve my problem. I was totally unaware of the View Code possibilities and how it works. This adds a whole new dimension to my use of Excel.

Wow! In reading this over to make sure everything was right, I just realized that if I add the separators to the Music Folder File Properties, then my XLOOKUP formulas will also work. Obviously, the View Code option requires less work on my part. I will work on this more. I am marking this one as closed.
Thanks again,
Dan Wilson...

 

[mumps]

You are very welcome.

 

[Dan Wilson]

You are very welcome.

Good day mumps. I have been using your View Code function and experimenting with it. I did mark this question as concluded, but I have one more question. I have noticed that when I change the data already in Column A, if Columns B thru H are not cleared prior to hitting the Enter key, no changes are made to Columns B thru H. Can the View Code be modified to clear Columns B thru H on only the row being used prior to loading the search results?
Thank you,
Dan Wilson...

 

[mumps]

That should not be happening. The old data should be replaced with the new data. Have you made any changes to the macro I suggested or to the data setup?

 

[Dan Wilson]

Good day mumps. Here is a copy of the View Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.ScreenUpdating = False
Dim rName As Range, srcWS As Worksheet, sAddr As String
Set srcWS = Workbooks("Music Database test.xlsx").Sheets("DBT")
Set rName = srcWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rName Is Nothing Then
sAddr = rName.Address
Do
Range("B" & Target.Row).Resize(, 2).Value = Array(rName.Offset(, 1), rName.Offset(, 2))
Range("D" & Target.Row).Value = rName.Offset(, 5)
Range("E" & Target.Row).Resize(, 2).Value = Array(rName.Offset(, 3), rName.Offset(, 4))
Range("G" & Target.Row).Value = rName.Offset(, 6)
Set rName = srcWS.Range("A:A").FindNext(rName)
Loop While rName.Address <> sAddr
sAddr = ""
End If
Application.ScreenUpdating = True
End Sub

The only change I made to rename the workbook holding the data and the worksheet. I made a copy of the workbook containing 4500 songs to hold only 650 songs. the copied worksheet is an xlsx with no macros. I added the "1" and the "2" separators on two of the songs for testing and it made the search find exactly the correct record. Otherwise, the problem as stated was there before I made those changes. If I did something wrong, please advise.
Thank you,
Dan Wilson...

 

[mumps]

If the problem was there before you made those changes, you did nothing wrong. Could you upload a copy of your Song List workbook and Music Database test.xlsx workbook as per the instructions in Post #2. I wouldn't need the entire workbook. Five or six rows of data would be enough for me to test the macro.