Returning 1 when it should be 0 ROWS UNIQUE FILTER Function

[halimgunawan]

Hi ALL,

I'm using the formula below and its always returning a value of 1 when it should be 0. I've tried different options to generate but this seems to be the best way. What am I missing here?

Please Login or Register  to view this content.

[AliGW]

You forgot to attach a sample workbook.

[AliGW]

To clarify the need for a workbook: there is no way to troubleshoot this long formula without knowing what it is referring to. We have no idea what's in the ranges and cells referenced, so it's impossible to tell you why it's not doing what you want.

[TMS]

Your FILTER will return an error if there is no matching data. Hence, what you see is a count of the error message, that is, one row (probably #CALC).

You would need to test for the FILTER returning an error. Might be better if you used the last parameter of the FILTER to return "No Data". Then you'll know what to test for.

[halimgunawan]

sorry I forgot the sample data..here it is..
I also have another question on the file. Pls help alook, Thanks in advance.

[josephteh]

You would probably be better off using PivotTable. Easy to set up and use.

And with slicers, you can do multiple selections easily and have better visualisation.

[halimgunawan]

Hi Josepteh,

yes, I know, but due to the certain circumstances. I have to use the way it is.
the pivotable option unable to solve any duplicates.

[windknife]

Try this,

Please Login or Register  to view this content.

[halimgunawan]

Hi Windknife,


Thanks, but if I choose company Z, Dept ANS the result become, #REF!?
can it still show the table with value 0 ?

[AliGW]

Try this (untested):

Please Login or Register  to view this content.

[windknife]

Try this,

Please Login or Register  to view this content.

[halimgunawan]

Thanks windknife, AliGW,

another question and it is the last.
I have another question on the file. Could you pls help to check on it?

if I choose the month Jun, how to show Jan-June?
also how to show the Mar: 0, May: 0, which not on rawdata?

[windknife]

Try this,

Please Login or Register  to view this content.

[halimgunawan]

it works windknife...many thanksssssss....

[josephteh]

Add a helper column in MasterData with this formula:=IF(COUNTIFS(C$1:C6,[@Participant],J$1:J6,[@Year],K$1:K6,[@Month])>1,"Yes","No").

[windknife]

You are welcome.

[halimgunawan]

Hi Windknife,

I realized that the formula return to the wrong numbers when I select company: Z, dept: ALL, training topics: ALL, month: Jan

it should be 19 right? but it shows 124.
which part should I revise? many thanks in advance.

[windknife]

Amended formula,

Please Login or Register  to view this content.