Trying to locate item titles using partial titles

[Dan Wilson]

Good day. Every once in a while I work myself into a corner and can't find the way out. I am running Office 365 (updated) on Windows 10 Home. I have created a worksheet containing data about almost 5,000 songs. I am now trying to generate a weekly play list in a separate worksheet that will locate and access a song title using one or more words from the desired title. I can find the row using the Find/Replace function on the home page. I have perused through the function list, cannot find a function that will search for anything less than the full song title. Any help will be greatly appreciated. The answer is probably going to be a very simple one.
Thank you,
Dan Wilson...

 

[KRice]

Here is one approach. Song names in one column. A short search input cell, and then listed below are song names that contain that string.

Book1
	A	B	C	D
1	Song Name		Search -->	wo
2	Let It Be			What a Wonderful World
3	Satisfaction			Woman
4	What a Wonderful World
5	Woman
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$1))/ISNUMBER(SEARCH($D$1,$A$2:$A$5)),ROWS(D$1:D1))),"")

 

[Dan Wilson]

Good day Kirk. I thought I was doing rather well with Excel, but what you sent to me has me completely baffled. I entered the formula exactly as you sent it and my Excel does not even recognize it as a formula. Perhaps there is an add-on that I do not have? Anyway, let’s try this a different way.

I have created a worksheet called DB (database) that contains a listing of almost 5,000 songs in my Music folder. The worksheet contains 8 Columns populated with data obtained from the Shell File Properties of each song.

Column A = Title
B = Contributing Artists
C = Year
D = #
E = Length
F = Comments
G = Genre
H = Album Artist

I have also created a worksheet called Prep containing 6 Columns to be used as a working list of music to be played on a weekly internet show.

Column A = Blank (selection number)
B = Title
C = Artist (Album Artist)
D = Length
E = Year
F = #

I want to be able to open a blank Prep worksheet and by entering song titles into Column A, have the formulas in Columns B through F access the DB worksheet and fill in the data for that song. Each week will be a new Prep worksheet.

I have tried hlookup, vlookup, search, index, indirect, everything that I thought might work. This should be easy to do, but apparently my mind is not working. I hope this gives you enough information to come up with a fix.

Thank you, Dan Wilson…

 

[KRice]

Hi Dan,

That's interesting. No add-ins are required. For a quick tryout, rather than entering the formula manually, have you tried copying it directly? In my earlier post, click on the clipboard icon shown (which copies the entire table with formulas to your clipboard) and then go to a blank worksheet, select cell A1 (or the same cell shown in the upper left of a post) and paste. That should give you the same working sample.

I'm curious if that type of search will suit your needs somehow. How many entries are typically in the Prep weekly playlist? If that list is of a manageable length, then one idea is to use the partial string search approach shown above, and then in addition to showing the short list of song titles that contain the search string, we could also show the row number or index number for each of those songs. Then when you identify the song title of interest, you could simply enter the index or row number in the Prep table. Then a series of INDEX functions could populate the Prep table with everything needed. With this approach, you wouldn't need to type the full song title in the Prep table, only a short text string to display a short list and the corresponding index number. Do you have an index number in the main data table...a unique number for each entry?

Do you have any duplicate song titles, I mean songs that are the same musically, but performed by different artists, as well as different songs (musically) performed by different artists. If so, how would the Prep worksheet "know" which one to target? Perhaps the suggestion I just mentioned should also list the artist and anything else needed to uniquely identify only one entry in the data table..

In the meantime, I'll have a look at the rest of your post....thanks for the additional detail.

By the way, you may want to install the XL2BB add-in to facilitate sharing parts of the worksheet (my signature block has a link to that).

Kirk

 

[KRice]

Dan,
Based on your description, I imagine the DB data table resembles the table below, only much longer. What information is found in the # column? Is that a unique ID assigned only to that row entry, or is it something else? If it is something else, you might want to consider adding a column that contains a unique ID...a numeric value that does not change and can be used as a convenient target for any match activities. Note that I've added a column to illustrate how it could be used.

MrExcel_20200716_Wilson_songs.xlsx
	A	B	C	D	E	F	G	H	I
1	Title	Contributing Artists	Year	#	Length	Comments	Genre	Album Artist	Index#
2	Let It Be	Lennon/McCartney	1970	7	4:03	album version	Rock	The Beatles	1
3	Satisfaction	Jagger/Richards	1965	8	3:45	single	Rock	The Rolling Stones	2
4	What a Wonderful World	Bob Thiele (as "George Douglas") & George David Weiss	1967	9	2:21	single	Trad. Pop, Jazz	Louis Armstrong	3
5	Woman	John Lennon	1981	10	3:32	single	Soft Rock	John Lennon	4
DB

Then on the Prep sheet for the weekly playlist, there is a search block shown to the right that is used to perform a partial string search of the song title column on the DB sheet. This is the same functionality described in my first post. This search block is currently set up for 10 matches. This search block uses the more complex INDEX/AGGREGATE construction to return multiple matches, and this formula is used for the song title (which is not necessarily unique) and the unique identifier (UID, which is unique by definition). The UID returned in the search block is shown in the green-shaded cells in I2:I11. This seems to be the key...from this point, you can review the shortened list of song titles that match the search string and then note the UID number for that song. Enter the UID in the weekly play list table in column A (also green-shaded cells) and then INDEX/MATCH functions are used to pull the relevant information over from the DB sheet.

MrExcel_20200716_Wilson_songs.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Index	Title	Album Artist	Length	Year	#			Search -->	wo
2	4	Woman	John Lennon	3:32	1981	10			3	What a Wonderful World	Louis Armstrong
3	2	Satisfaction	The Rolling Stones	3:45	1965	8			4	Woman	John Lennon
4	1	Let It Be	The Beatles	4:03	1970	7
5
6
7
8
9
10
11
12
Prep

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX(DB!$A$2:$I$5,MATCH($A2,DB!$I$2:$I$5,0),1),"")
C2:C5	C2	=IFERROR(INDEX(DB!$A$2:$I$5,MATCH($A2,DB!$I$2:$I$5,0),8),"")
D2:D5	D2	=IFERROR(INDEX(DB!$A$2:$I$5,MATCH($A2,DB!$I$2:$I$5,0),5),"")
E2:E5	E2	=IFERROR(INDEX(DB!$A$2:$I$5,MATCH($A2,DB!$I$2:$I$5,0),3),"")
F2:F5	F2	=IFERROR(INDEX(DB!$A$2:$I$5,MATCH($A2,DB!$I$2:$I$5,0),4),"")
I2:I11	I2	=IFERROR(INDEX(DB!$A$2:$I$5,AGGREGATE(15,6,(ROW(DB!$A$2:$A$5)-ROW(DB!$A$1))/ISNUMBER(SEARCH($J$1,DB!$A$2:$A$5)),ROWS(I$1:I1)),9),"")
J2:J11	J2	=IFERROR(INDEX(DB!$A$2:$I$5,AGGREGATE(15,6,(ROW(DB!$A$2:$A$5)-ROW(DB!$A$1))/ISNUMBER(SEARCH($J$1,DB!$A$2:$A$5)),ROWS(J$1:J1)),1),"")
K2:K11	K2	=IFERROR(INDEX(DB!$A$2:$I$5,MATCH(I2,DB!$I$2:$I$5,0),8),"")

Please let me know if this comes close to the functionality you envisioned.

 

[Dan Wilson]

Hi Kirk. Thanks for answering so quickly. I will answer your questions and then try your suggestion. I typed in your formula very carefully. I did not create a template such as yours. I will try your idea about clicking on the clipboard. As to your questions - the Prep worksheet will start with 20 entries. That will cover the need to fill an hour of music. Hopefully the show will attract interest and I can then expand the show to as much as four hours which will require up to 80 entries. It would be easy enough to add a unique number for each entry in the DB. There are several duplicate song titles in the DB. I have fixed that by entering a number in parenthesis for each duplicate song. IE: Alone (1) is by the Shepherd Sisters and Alone (2) is by the Four Seasons. When entering the title to be searched, the number in parenthesis would have to be entered as part of the search. Thank you for the hint about using the XL2BB link. I will try that.
Thank you, Dan Wilson...

 

[Dan Wilson]

Kirk, I forgot to answer one of the questions. The # relates to the Billboard Charting system. A "1" means that the song reached position number one on the charts,
Thanks, Dan Wilson...

 

[Dan Wilson]

Good day Kirk. I wanted to let you know that I found a new function that I have not used before. XLOOKUP It works and does everything that I want. The only downside is that I have to get the song title absolutely correct or it does not work. If there is a way to include the ability to search on a partial (but unique) song title, that would be great. Thanks for your help.
Dan Wilson...

 

[KRice]

Hi Dan, XLOOKUP is an Excel 365 function whose behavior is similar to the INDEX/MATCH in my previous post, except XLOOKUP has some added features. Unfortunately, I don't have access to the XLOOKUP function (I'm using Excel 2019), so I'm not sure how to advise. I believe there is a match mode option in XLOOKUP that will allow for some fuzzy matching, but I still don't think that will offer the flexibility needed. In my last post, I mentioned "This seems to be the key...", with regard to knowing that you've identified the actual song of interest. Any partial song title search (which is what the search block in my previous post does...see cells I1:K11) has the potential to return multiple results. Because of that, I don't think there is a good way to integrate a partial string search within XLOOKUP (perhaps with a wildcard and fuzzy matching, but I don't know if that is even possible) and have confidence that the correct song has been found. Consider the case where even a complete song title search does not offer the confidence that the correct song has been found---if "Alone" is searched for from within XLOOKUP, the result returned will depend on some of the options available in the function, specifically the match mode and search mode options. You'll still have at least two potential matches (from The Shepherd Sisters and The Four Seasons), and XLOOKUP will give you only one of them, depending on the search mode option. I would think that you will still want to review the matches and determine which of them is the preferred one, and because of this, I keep returning to the two-step idea, where the partial match is performed to identify the one song of interest, and something unique about that preferred song is learned from that step (like a unique identifier number...a UID). Then the UID could be entered into the weekly song list table and XLOOKUP could then access the correct record from the large data table using the UID.

If that makes sense, then the search block in my previous post may still be worth considering. And then the formulas using INDEX/MATCH formulas in my previous post would be replaced with versions using XLOOKUP.

 

[Dan Wilson]

Thank you for all the EXCELlent help. I will experiment with your original suggestion as soon as I can figure it out. This forum is the greatest. It has never let me down.
Dan Wilson