Find Unique Values Based On Multiple Columns

Hi All,

I have a dataset which has a list of clients that I am trying to find the unique records, based on a second column saying N, R, MTA or MTC, and a third column saying H or T. I have been able to do that within a formula as below, but the formula takes about 15 minutes to run on 1 cell, i have it on 4. As well as a variation on another 4 cells, is this something that can be done with VBA?

=SUM(--(FREQUENCY(IF(Daybook!U:U<>"",IF(Daybook!AL:AL=$A4,MATCH(Daybook!U:U,Daybook!U:U,0))),ROW(Daybook!U:U)-ROW(Daybook!U2)+1)>0))

Thank you
Dustin

Sorry to sound stupid but I've tried to attach a document and cant seem to, is there a set way I have to do that?

Hi,

Im not sure if this has attached but if it has...

Apologies for the late reply, on the attached I would like the code to fill in the Test_Sheet tab, even if you could provide code for just one of the cells I should be able to manipulate to match the rest of the data.

So BA in Daybook is for Clients and BB is for Policies.

Thank you

Hi,

The only answers I have are from the formula in the original post, (I have amended below to take new column into account).

=SUM(--(FREQUENCY(IF(Daybook!BA:BA<>"",IF(Daybook!AL:AL=$A4,MATCH(Daybook!BA:BA,Daybook!BA:BA,0))),ROW(Daybook!BA:BA)-ROW(Daybook!BA2)+1)>0))


I don't have any sample answers in VBA as yet, but the formula checks number of unique values in BA which have "N" in AL (for clients). Then the same for policy but based on BB and AL. The only problem is the formula takes ages to run when I have the test sheet filled in and the full data in the Daybook tab.

Thanks