Count unique distinct values IF....

Hi All

I am really hoping someone clever can help with a solution, as I've got a real puzzler that I've been unable to crack the last 2 days! >.<

I have a data set with account IDs from 4 separate systems. They should all be the same, but sadly they're not

So I've added a column called 'Distinct' to alert us to any rows where the 'Acc ID's don't match.

But (and here's the pesky bit) annoyingly some of the cells contain string values that begin with a '#'.
I am unable to remove them from the data, so need to find a way to exclude them from the calculation in the distinct column

I've been trying the advice here but have been unable to get anything working

"get-digital-help.com/count-unique-distinct-values-that-meet-multiple-criteria-in-excel"
[I'm not allowed to post proper links yet]

Sample workbook attached
Any help would be massively appreciated as I am totally stuck!

All the best
Vicky

M11=SUM(IF(FREQUENCY(IF(Table24[@[Acc ID (1)]:[Acc ID (4)]]<>"",MATCH(Table24[@[Acc ID (1)]:[Acc ID (4)]],Table24[@[Acc ID (1)]:[Acc ID (4)]],0)),COLUMN(Table24[@[Acc ID (1)]:[Acc ID (4)]])-COLUMN([@[Acc ID (1)]])+1),1))


Control+shift+enter

copy down

Hi CarraCalla

Thanks for the quick response!
I tried pasting that formula in (with ctrl + shift + ent) but it's producing the same results?

Am I doing something something wrong?

All the best
V

M11=SUM(IF(FREQUENCY(IF(Table24[@[Acc ID (1)]:[Acc ID (4)]]<>"",IF(TRIM(LEFT(Table24[@[Acc ID (1)]:[Acc ID (4)]],1))<>"#",MATCH(Table24[@[Acc ID (1)]:[Acc ID (4)]],Table24[@],0))),COLUMN(Table24[@[Acc ID (1)]:[Acc ID (4)]])-COLUMN([@[Acc ID (1)]])+1),1))


Control+shift+enter

copy down

That's fantastic!!
I can't tell you what a headache this has resolved for us
Thank you very much!

You are welcome

Hi All,

Sorry I thought this one was resolved but actually I'm having trouble copying the amazing formula that CARACALLA provided, into to my 'Clients Workbook'

The formula works great in the dummy data doc I uploaded yesterday called Book1.xlsx

But when I try to implement it into my Clients workbook it returns '1' for every row which is incorrect on rows where not all the account refs match

I'd be super grateful if anyone can see where I'm going wrong.

Any help would be appreciated
All the best
Vicky

The reason appears to be is that the column bit refers to the column in the table and you have 4 more columns in the table before the Acc ID (1), if you delete these columns the formula works!

However I have not thought how to amend the table to avoid doing this or why it should error!

Hi davsth

Good call. Your're absolutely right, it's the preceding columns that are causing the formula to not work.

Does anyone know why this is happening?

Is there any way to amend the formula to still work with preceding columns?

Hi CARACALLA

You must have the patience of a saint
Apologies my last post was poorly worded

Is there any way to amend the formula to allow for preceding columns, but still keeping the array range the same (those 4 columns [Acc ID (1):Acc ID (4)]?

*sorry*

This is perfect! And I'm not getting the circular ref error anymore either. Thank you so much CARACALLA!

You are welcome