Resolving multiple search results

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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...
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
Good day mumps. Thank you for sticking with this. I copied the Music Database workbook to "Music Database-mumps.xlsm", then deleted all but 16 songs. the songs I kept have Parenthesis characters as part of the Title, Numbers in the Title, and Titles by multiple artists. I then edited some of the Titles with multiple artists by adding a separator "1", "2", etc. Keep in mind that the Title is a File Property (number 21) from the File Properties where the File Name would include the File format of ".MP3". In the Music Database workbook, Columns B through H are extracted from various File Properties of the song file. then I created the workbook containing your View Code sub. The workbook is titled "dansoldies show log mumps.xlsm". I then edited the line in the View Code to reference the "Music Database-mumps" workbook and the "DB" sheet in that workbook.

Row 2 in the show log searches for a title with multiple artists. Note that there are no separators in the Titles. This was done on purpose to show that the view code finds the last title listed when there are multiple artists for a title. Row 3 shows that numbers work OK. Rows 4 and 5 prove that the separator in the multiple title works. Rows 6, 7, and 8 show that multiple titles with separators work for more than 2 entries. Rows 9 and 10 indicate the problem. Row 9 was entered as "Alone" and the Enter key was pressed. There is no match for that in the Music Database-mumps worksheet and Columns B thru G remained empty. Row 10 was entered as "16 Candles" and when the Enter key was pressed, the data was loaded in Columns B thru G. I then put the cursor in Column A, Row 10 and hit the Space bar erasing the cell. When I pressed the Enter key, the cell remained empty and the data for 16 Candles remained in place.

That should explain everything. If I missed something, let me know. The two workbooks are in DropBox. Look for "mumps".
Thank you,
Dan Wilson...
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Unfortunately, I don't see the links to the two workbooks in your post.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Are you saying that if you delete an existing title in column A, you want to clear columns B to H?
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you saying that if you delete an existing title in column A, you want to clear columns B to H?
HI mumps. If entering new data into a Row A cell will overwrite the contents of Columns B thru H, then clearing is not necessary. When I start a new show, I can clear the entire worksheet before starting or if the View Code can detect that Row A is vacant, it can clear Columns B thru H for that row.
Thanks, Dan...
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Try this version of the macro. When you enter new data into a Column A cell, it will overwrite the contents of Columns B to H in that row. If you delete data in a cell in column A, the contents of Columns B to H in that row will be cleared.
VBA 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-mumps.xlsm").Sheets("DB")
    If Target <> "" Then
        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 = ""
        Else
            MsgBox ("'" & Target & "' was not found in the database.")
        End If
    Else
        Target.Offset(, 1).Resize(, 7).ClearContents
    End If
    Application.ScreenUpdating = True
End Sub
For your information, this type of macro that is placed in the worksheet code module is called an event macro. This means that the macro is triggered automatically by an "event" that happens in the sheet. In your case, it is a Worksheet_Change event which means that any manual change in a cell (in your case restricted to column A) will trigger the macro. There are other events which will also trigger the macro such as double clicking on a cell or simply clicking on a cell. If you are interested, you can do some research on "event macros in Excel". They are extremely useful and powerful. By the way, we appear to have the same taste in music. :)
 
Solution

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
Wow! That works! Now all I have to do is keep looking at the code until I understand what is going on. Thank you for the explanation of "events" and Worksheet_Change. That helps a lot. Thank you for sticking with this one. This forum is the greatest!
Thanks,
Dan Wilson...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,086
Messages
5,640,007
Members
417,121
Latest member
DallyDally

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