# Thread: Unique within group (2002 SP3)

1. ## Unique within group (2002 SP3)

In the attached sheet, as part of a selection formula, I use AND(OR(LEFT(H4,2)="Ra",LEFT(H4,2)="Ch"),COUNTIF(OF FSET(F4,0,0,-ROW()+1,1),F4)=1 to identify the first occurrence of a Radio Button or Check Box on a form. This works fine if the data in the sheet is from only one form. However, if there are multiple forms in the sheet, the formula causes a control of the same name to not appear when the list is filtered for subsequent forms.

So, in the attached sheet, the radio button named "subrate" should appear (once) when the list is filtered for column I = TRUE and column B either 19 or 20.

Any ideas how I can make my formula return TRUE for the first (or last) occurrence of any particular named one of these elements within a form?

Hope this makes sense!

TIA
Regards
Paul

2. ## Re: Unique within group (2002 SP3)

Does this do what you want (in I2, fill down as far as needed)?

=OR(LEFT(H2,2)="Te",SUMPRODUCT(OR(LEFT(H2,2)="Ch", LEFT(H2,2)="Ra")*((LEFT(H\$2:H2,2)="Ch")+(LEFT(H\$2: H2,2)="Ra"))*(B\$2:B2=B2))=1,LEFT(H2,2)="Se")

3. ## Re: Unique within group (2002 SP3)

Doesn't seem to in this extended version.

4. ## Re: Unique within group (2002 SP3)

In which cell or cells does the formula return a different result than you intended?

5. ## Re: Unique within group (2002 SP3)

In all of the subrate ones. It is showing FALSE on all of them :-(

6. ## Re: Unique within group (2002 SP3)

Sorry, I had assumed that the formula from your first post worked correctly if you don't take the form (column [img]/forums/images/smilies/cool.gif[/img] into account, I omitted to check whether it actually did (it doesn't).

Does this work better?

=OR(LEFT(H2,2)="Te",SUMPRODUCT(OR(LEFT(H2,2)="Ch", LEFT(H2,2)="Ra")*(\$F\$2:F2=F2)*(B\$2:B2=B2))=1,LEFT( H2,2)="Se")

7. ## Re: Unique within group (2002 SP3)

Wow Hans. Not only does it work, it is better than my original formula (which did appear to work OK when there was only one form present).

Thank you, Thank you, Thank you, Thank you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•