Eliminating 0 values for blank filtered records

**ron2k_1** · 10-29-2010, 07:33 PM

Hi Crew,

A member asked few days ago how to generate a list of records based on a lookup criteria that appears several times on the lookup column. I you use Vlookup you'll only get the first hit, so I thought that arrays would be the answer. Of course, I was lazy and recommended him to just use AutoFilter.

Refer to the attached:
You see if you select any value on C19 other than "CAB", you'll be fine. However, under the Sub column for those items that are blank, I'm getting a 0. Is there a way to remove the zero and to show what it actually is, which is Blank ""?

Thank you

**beaunydal** · 10-29-2010, 08:23 PM

The formula cells under the "Price" heading have a Custom format that displays the formula's zero as a dash.
If you apply the same format to the formula cells under the "Sub" heading they too will display the formula's zero as a dash.

If that is not an acceptable solution then your formula under the "Sub" heading in D22 could be changed to...

Please Login or Register  to view this content.

which tests for the blanks in D3:D14 then forces the return of a blank rather than a zero.

BTW: The sheet has the defined name "List" (B3:B14) which could be used in the formulas under the "Price" and "Sub" headings.

**DonkeyOte** · 10-30-2010, 03:52 AM

@ron2k_1, goes without saying you should be looking to avoid double evaluation of any Array.

For the text strings D22 onwards simply encase the INDEX within a REPT call

Please Login or Register  to view this content.

Note: here there's no need to bother with the pre-emptive COUNTIF we can simply use contents of adjacent cell as our test (which has already conducted this test)

Regards your unique list... you needn't worry about double evaluation here given you can restrict your DNR to ignore the #N/A

Please Login or Register  to view this content.

Modify Data Validation source for C19 from the INDIRECT to:

Please Login or Register  to view this content.

there is no need for UniqueValidationList nor do you need the formula in J2

**beaunydal** · 10-30-2010, 05:44 AM

Originally Posted by DonkeyOte

For the text strings D22 onwards simply encase the INDEX within a REPT call

I've not seen this trick before.
I've done what appears to be the same with the T function.
Just wondering if there is any difference?

**DonkeyOte** · 10-30-2010, 05:50 AM

REPT would return any non-blank/non-error as a text string (text, numbers, logicals) whereas T would only return strings (either/or may be preferable).

As a function REPT obviously has "number of times" so can be useful for padding etc - ie the equiv. to VBA String Function.

**ron2k_1** · 10-30-2010, 12:06 PM

Hey! Wicked!

Neat tricks I've learned today! I must confess, as beaunydal I have never used REPT before. Thank you you saved me lots of turn arounds with the unique list process and the validation list.

I almost gave up modifying the array formula on D22 down. To eliminate the zeroes I guess I could've used custom text format to "", but for educational purposes it was a pleasure to have received your assistance.

I'll try to locate that post now and attach the workbook, or make reference to this thread.

Thank you.