Count Unique Values and text in Excel 2019

Topic

New Reply

I have two Columns. Column A is Client ID. Each cell contains five numbers. These are not in any order, and I have about 1,200 records, and a number of them are duplicated (which is OK). I need to count the number of unique Client IDs. The illustration below shows only 10 records.

Each unique entry in Column A is matched with a county in Column B. So, if the Client ID is, for example, 80321, no matter which row that client is listed on, the County will always be Johnson.

I have a formula in A11 that counts the unique IDs. It is =SUM(IF(FREQUENCY(ID,ID)>0,1)), where ID is the Range Name. This works fine. I now need a formula for Column B which lists Each Unique County, and how many unique Client IDs live in each county. I am using Excel 2019, so I can’t use the Unique function. Thanks for any help or hints you might have to offer.

 

John

Reply | Quote

Replies

A pivot table should do that. (Don’t have an example)

cheers, Paul

Reply | Quote

John,

If you truly only have 2 columns and you don’t need the duplicates you can use the
“Remove Duplicates” feature of excel to eliminate the duplicate ClientIDs then do a simple pivot table.

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

John,

You’ve said the client id column is named ID so assuming the county column is named County you can get the unique list by using an array formula.

There’s a neat way to do this and a less-neat way.

The less-neat (but quicker way)

<li style=”list-style-type: none;”>

• Select a range of empty cells the same length as your data
• Enter the following formula into the first cell and enter it via CTRL-SHIT-ENTER to make it an array formula:

  
  =INDEX(County,SMALL(IF(MATCH(County,County,0)=ROW(INDIRECT("1:"&ROWS(County))),MATCH(County,County,0),""),ROW(INDIRECT("1:"&ROWS(County)))))
  

  
  Make sure all the quotes in the above are all plain/straight ones, not fancy ones.

• If, for example, you started your array in the previous step from G2, you can then use
  Code:

  =COUNTIF(County,G2)

  to count the number of clients.

The neater way

Rather than selecting blank cells the entire length of your data, you can count the number of unique counties (as you did with IDs) and then select only that many number of cells – then do the above.

Hope this helps.

Reply | Quote