Looking to create a list of unique names that match criteria without using an array

Hi John
You can use Autofilter for this. Follow these steps:
1. Insert 3 blank rows at the top of your worksheet.
2. In A1 type "Dues Paid". This must be exactly as the header for the Dues paid column on your worksheet.
3. In A2 type "1".
4. Highlight all the cells you want to filter on your worksheet.
5. In the Sort & Filter section of the toolbar click on Advanced.
6. Click on Copy to another location.
7. The List Range box will be populated with your highlighted Range.
8. In the Criteria Range box highlight Cells A1 to A2.
9. In the copy to box click on a cell where you want the data to appear.
10. Click OK.
Job done.
See sample attached.
Good luck.
Tony

John

Probably you'll need an ARRAY formula for this..

This is an array solution but as Fotis I can not see non-array version.

Is there any reason for not used Array?
Is this workbokk going to be shared?
Unique1.xlsx

I need it be a normal formula as I have too many array formulas at the moment and the workbook is slowing down rapidly, just hope there is a way. This is reactive list that autopopulates as data is entered bythe user.

Cheers

JD

Generaly array are slower (this is the cost of multiple calculation in one go)but please better check your exisiting formulas and if they can not be amended or maybe the ranges can be cut down.

I don't know if there is a way to expand this concept and adapt it for this task. However, even if it did I'm not sure it would be any faster. It would still be an array formula, only difference is that INDEX is creating the array instead of CSE. What does the expertis say about this?
http://www.get-digital-help.com/2013...rray-formulas/

Even if this work this would requaied an INDEX for each range which will in fact add to the calcualtion time - so "false economy"

Here's a non-array formula method that uses a helper column.

With your data in the range A2:B20...

Enter this formula in C2 and copy down to C20:

=IF(B2=1,IF(SUMPRODUCT(--(A$2:A2&B$2:B2=A2&B2))=1,MAX(C$1:C1)+1,""),"")

Note that the formula refers to cell C1. C1 is assumed to be a TEXT column header or an empty cell. C1 must not contain a number.

Then, for the unique names...

Enter this formula in E2 and copy down until you get blanks:

=IFERROR(INDEX(A$2:A$20,MATCH(ROWS(E$2:E2),C$2:C$20,0)),"")

And of course pls note that in fact SUMPRODUCT function is an ARRAY formula and ROW function is a Volatile Function.

Of course Tony is the right man to explain all these better to you if he wants.

ROW is NOT a volatile function.

It's easy to test.

Open a new file and enter this formula in some cell:

=ROW()

Save the file then close it.

Reopen the file, don't do anything to the file then close it.

If ROW was volatile Excel would have asked you if you wanted to save the changes you made to the file.

Thanks for sharing this trick

----------