One step further on formula to eliminate duplicate records in array.

**harleypop** · 03-26-2009, 06:38 AM

I got a formula from this forum to eliminate duplicate records in a array from 1 column in database. Now I would like to take it one step further and filter out records in the array that do not meet the criteria of being in a particular "Zone" selected by the user by clicking on a ComboBox from cell "AA18".

The first formula is copied to cell "C7":

=INDEX(Data!E3:E119,MATCH(0,-ISBLANK(Data!E3:E119),0))

The next forumla is copied to cell "C8" and below:

=IF(COUNT(MATCH(Data!E$3:E$119,C$7:C7,0))<COUNT(1/(Data!E$3:E$119<>"")),INDEX(Data!E$3:E$119,MATCH(0,(Data!E$3:E$119<>"")-ISNA(MATCH(Data!E$3:E$119,C$7:C7,0)),0)))

**harleypop** · 03-27-2009, 11:14 AM

Bump - Any suggestions?

**DonkeyOte** · 03-31-2009, 02:36 AM

My suggestion... as I say a lot here (seemingly) avoid arrays as they are not efficient... they may seem elegant but they are not efficient.

EDIT:
Probably also worth mentioning that you could produce something very similar to your report using a Pivot Table and dispensing with formulae altogether
(Zone & Types as Page Fields, Install Name as Row Field, Type Description & FY as Column Field with any field as Data Field set to COUNT)

EDIT: 0852 UK time - attachment removed & replaced by attachment in latter post.

**DonkeyOte** · 03-31-2009, 02:57 AM

Harleypop, disregard the prior attachment as it does not do what you want... I will post a revised version shortly.

**DonkeyOte** · 03-31-2009, 03:51 AM

Revised below which (I think) does what you want - using helpers to negate need for arrays thereby aiding performance.

**harleypop** · 03-31-2009, 02:37 PM

Thanks DonkeyOte - I think that's it, I will apply to the real spreadsheet with about 3000 records and will close this post if it works for me.

Thanks again you are a life saver.