1

I have a column of data (Y2:Y4993 with a heading in Y1) in a worksheet showing various text entries.
Currently there are ten different items in the list, although the number may go up or down as the data changes from week to week - I have a dynamic named range to reference the column.

Using an array-formula I am returning a unique list of entries (formula is dragged down across 10 rows from F3:F12):
{=INDEX(Gas_Reason_Column,MATCH(0,COUNTIF($F$2:F2,Gas_Reason_Column),0))}

The named range Gas_Reason_Column is defined as:
=INDEX('Audit Raw Data'!$Y:$Y,2):INDEX('Audit Raw Data'!$Y:$Y,COUNTA('Audit Raw Data'!$A:$A))

This all works very well and gives me what I asked for:
Unique list of 10 entries in raw data

I'm looking to update the formula to ignore the 'Pass' entry as this is dealt with elsewhere on the sheet and would make the chart I'm planning on building from the data hard to read due to the large disparity between the Pass count and all the other counts.
I just can't figure out where to add a <>"Pass" check in the formula.

NB: The raw data sheet will be cleared and new data copy/pasted in every week, so I would definitely prefer a solution that doesn't add require any changes to that sheet.

1

Add the condition to the Array Formula:

=INDEX(Gas_Reason_Column,MATCH(1,(COUNTIF($F$2:F2,Gas_Reason_Column)=0)*(Gas_Reason_Column<>"Pass"),0))

It is still an array formula and needs to be entered with Ctrl-Shift-Enter instead of Enter.

It now is finding the first row where it is true, not on the list and not "Pass"

  • 1
    That did the trick. Thanks Scott. I'll accept it as soon as it lets me. – Darren Bartrup-Cook Jan 27 '17 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.