Array formulas to create a sub list - a problem I can't fix

I have attached a spreadsheet which is trying to extract names from a sorted list when you select the first letter. Credit is not mine, I copied the idea from a website but cannot find it to credit the original author!

$A1:$A1000 holds a list of names sorted alphabetically.
$B1 is either emply or you can select letters A to Z from the drop down list.

$D1:$D32 contain the following array formula to list only those names in $A1:$A1000 that start with the letter.

{=OFFSET(INDIRECT(ADDRESS(MATCH($B$1 & "*",$A1:$A1000,0),1)),0,0,COUNTIF($A1:$A1000,$B$1 & "*"),1)}

If $B1 is blank, contains a letter which matches no entries or contains a letter matching >1 entry it works exactly as I want. #N/A are there to signify empty values.

However, if I seelct a letter which returns only 1 name, the name is repeated in all 32 cells.

When letter S,Y or Z is selected (which match 2 rows) the ADDRESS is returning $A3:$A4, $A5:$A6 and $A7:$A8 respectively which is what I want.

If C or J is selected ADDRESS is returning $A1 and $A2 respectively.

I'm assuming that is the cause of my problem. If I try and hard code INDIRECT($A1:$A1) it makes no difference!!!

Any help would be greatly appreciated... (I'm at the limit of my array formula knowledge!)

Thanks dll

I tried that and it fixes the duplicates problem when one matches but, when >1 match, the first matched name is repeated (the correct number of times).

In the early hours last night I got a similar problem but cannot remember how I did it! It was definately using array formulas.

I did think of using a helper column to set TRUE/FALSE then use the SUMPRODUCT / SMALL / IF technique to do it but I thought that there may be a way to get this approach to work and it drove me so crazy I missed the end of the Masters!

Thanks Martin and daddylonglegs.

That did the trick and now works as I want for 0, 1 and >1 matches.

You are some pretty clever guys!!!

Originally Posted by martindwilson

try this non array
=IF(ROWS($A$1:A1)>COUNTIF($A$1:$A$1000,$B$1 & "*"),NA(),INDEX(INDEX(A:A,MATCH($B$1 & "*",$A$1:$A$1000,0)):INDEX(A:A,MATCH($B$1 & "*",$A$1:$A$1000,0)+COUNTIF($A$1:$A$1000,$B$1 & "*")-1),ROWS($A$1:A1)))

Now that I understand how your forumla works it is doing everything I wanted - thanks for that.

I moved the MATCH and COUNTIF formulas to hidden cells and did a test with a source of 20000 sorted names with a target range of 100 cells and it was only taking just over a second to execute when I changed cell $B$1.

As we will only be talking about 2-3000 names max this is far better than I expected!

Thanks - your help was appreciated greatly.

Originally Posted by daddylonglegs

Sorry, I messed it up because I missed out a vital part of the formula. This version should work as I intended (and I think you wanted)

=IF(ROWS(D$1:D1)>COUNTIF(A:A,B$1&"*"),NA(),OFFSET(A$1,MATCH(B$1&"*",A:A,0)-2+ROWS(D$1:D1),0))

It's shorter than Martin's but it may possibly be less efficient used over a large range because OFFSET function is volatile (which means the formulas are recalculated every time anything in the worksheet changes)

Thanks.

That also does exactly what I wanted. Thanks a lot. I've learned a lot too.

I'd forgotten about the volatility of formulas. I know UDFs can be marked as volatile but I didn't realise that some of Excels built in formulas were volatile too.

Originally Posted by daddylonglegs

Sorry, I messed it up because I missed out a vital part of the formula. This version should work as I intended (and I think you wanted)

=IF(ROWS(D$1:D1)>COUNTIF(A:A,B$1&"*"),NA(),OFFSET(A$1,MATCH(B$1&"*",A:A,0)-2+ROWS(D$1:D1),0))

It's shorter than Martin's but it may possibly be less efficient used over a large range because OFFSET function is volatile (which means the formulas are recalculated every time anything in the worksheet changes)

I found an artcile on chandoo.org about using INDEX as an alternative to OFFSET to get round the volatility problem.

Using your code I amended it as follows and it works exactly as I want:

=IF(ROWS(E$1:E1)>COUNTIF($A:$A,$B$1&"*"),NA(),INDEX($A:$A,MATCH($B$1&"*",$A:$A,0)-1+ROWS(E$1:E1),1))

Thanks all for your help. I'm chuffed to bits.

I only found this site yesterday and I think I'll become a regular!

My next task is to add a page x of page y functionality when it returns > one page of results. From these techniques I can see how I'm going to do it by using a scroll bar control to change the page number.