Possible Array formula needed

[Domroy]

I have a worksheet where I have a column of Claim Numbers (column AS), and a column of LOAs (column A), and I need a formula that looks for "is there more than one claim number per LOA?" I have a few other similar formulas, but can create them once I get the help I need creating this first one. Can you help?

Thank you!

 

[dreid1011]

Possibly this (I don't begin to understand how it works, as I have to play more with FREQUENCY at some point... but it works in my test):

=SUM(IF(FREQUENCY(IF($A$1:$A$10=A1,IF($AS$1:$AS$10<>"",MATCH($AS$1:$AS$10,$AS$1:$AS$10,0))),ROW($AS$1:$AS$10)-ROW($AS$1)+1),1))

Array formula, enter with CTRL+SHIFT+ENTER.

And adjust the ranges to suit your data.

Source of formula, with an explanation:

https://stackoverflow.com/questions/12787093/how-to-count-unique-values-per-category-in-excel

 

[Domroy]

Thank you. This gave me an array of ones and fours. Not sure what exactly that means, but I'm trying to see if I can find the logic.

 

[dreid1011]

I apologize for the delayed response. What it should be doing is giving you a total count of each unique Claim Number per LOA. So, say your list contains the claim number "12345" and there are two instances of this claim number for LOA "Z" and three instances of claim number "45678". The formula SHOULD return 2 on every row of Z 12345, and 3 on every row of Z 45678.

Now that I think about it, you may have only wanted a count of how many unique claim numbers there are for each loa, in which case this example would return 2 for 12345 and 45678 correct?

 

[Aladin Akyurek]

@ Domroy

Care to post a small sample and the expected result for that sample?