list all values that begin with a specific letter

Hi, apologies for resurrecting that old thread.

The formula provided by AliGW in the previous thread about this isn't working for me. excelforum.com/excel-formulas-and-functions/1256982-list-all-values-that-begin-with-a-specific-letter.html#post5029367

I have a long list of 'names' (multiple words and single words e.g. 'Me Too', 'Tubbies on Telly' or 'Footing') on a sheet. For context this is a logbook of rock climbs in different places.
On a different sheet I want to list all cells that start with a specific letter that is entered in cell M1.

I modified Ali's formula to use my sheets =INDEX('all boulders'!$D$12:$D$7263,SMALL(IF(LEFT('all boulders'!$D$12:$D$7263,1)=$M$1,ROW('all boulders'!$D$12:$D$7263)),ROW(1:1))-1,1)
but it returns #VALUE! unless I make it an array formula by doing ctrl+shift+enter, at which point it returns a valid name from the list but this name does not start with the letter entered in cell M1. e.g. M1 = "J", result given is "Thriving Sincerity".

Is there some I'm missing here?

I have been generating similar lists from this data, returning the names based on what 'place' I select from a dropdown box using this formula:
=IFERROR(INDEX('all boulders'!$D$12:$D$7642,AGGREGATE(15,6,(ROW('all boulders'!$B$12:$B$7642)-ROW('all boulders'!$B$12)+ 1)/('all boulders'!$B$12:$B$7642 =$H$1), ROW('all boulders'!$A1))), "")
The value in $H$1 is the selected 'place' that I want the list of names to be at. i.e. 'Tubbies on Telly' is at a place called 'Fontainebleau'.
I am able to drag this formula down until it returns '0' when there are no more names listed as being in Fontainebleau.

I thought I might be able to modify this formula to list all names beginning with a specific letter by using the LEFT formula instead of the AGGREGATE formula but I can't get it to work.

I confess I am not knowledgable with excel formula. Up to now I have found answers to my problems by searching the web and manipulating the suggested solutions I find. e.g. I can't explain to you/anyone how the above formula works exactly but I kind of see what it is doing!

Apologies for the long, potentially confusing, post. I hope one of you amazing peeps can help me out.
Regards,
rew

Looks like I'm off to a great start on this forum!

Sorry, as an accepted file format for the attachment feature I figured a zip file would be ok.
I have further stripped out the excel file to make it small enough.

I look forward to your thoughts and wish everyone a merry Christmas.
rew

"An alternative, since SORT, UNIQUE and FILTER are not available in Excel 2019, and since array formulae are a bit fiddly to use... this just needs ENTER:"

Good point Glenn, but I went to check and FILTER seems to be present in Excel 2019:

MS FILTER function

I jumped from Excel 2016 to Excel 365 so I don't know much, for me that www was reliable.

Well, at least rewmel will see that it could be simpler in a newer version...

Thanks to you all for your solutions, they all work perfectly, of course! A lovely xmas present

I wondered whether these formula are easily edited to cater for the first 2, 3 or 4 letters of each name. I managed to come up with a solution, using Glenn's formula above, where one can enter the number of characters for the search in one cell, type the desired text string in another cell and hey presto, the results pop up!

Please Login or Register  to view this content.

cell W1 being the # of characters
cell X1 the string of characters

Thanks all, you are amazing. I love excel and trying to find solutions to my whimsical desires to manipulate my spreadsheets and you all have provided me with more tools to do this. I am self taught and only use excel in a personal space, not for work.

If it is not too much bother, I would appreciate either links to any tutorials you know of that will teach me what's going on with the ROW function in these formula, or a simple explanation here if it's not too much to ask.

Regardless, you have given me something new to tinker with and try to figure out for myself.

Many thanks

Originally Posted by AliGW

@DJunqueira
This website is reliable:

https://bettersolutions.com/excel/fu...-functions.htm

Great site!!

Many tks AliGW, this will help.

Went strait to my library.

Thanks again Glenn, crystal clear.