Unique within group (2002 SP3)

Topic

New Reply

In the attached sheet, as part of a selection formula, I use AND(OR(LEFT(H4,2)=”Ra”,LEFT(H4,2)=”Ch”),COUNTIF(OFFSET(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

Reply | Quote

Replies

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”)

Reply | Quote

Doesn’t seem to in this extended version.

Reply | Quote

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

Reply | Quote

In all of the subrate ones. It is showing FALSE on all of them 🙁

Reply | Quote

Sorry, I had assumed that the formula from your first post worked correctly if you don’t take the form (column 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”)

Reply | Quote

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.

Reply | Quote