I loaded the dynamic array version of Excel and already I am dreading having to revert to deliver client workbooks. I made the list of companies into a table so that the named range 'list' (of companies) becomes dynamic. I then move to my formula cell and named it 'distinct'.
The formula I inserted into the cell is
which spills to provide a list of the distinct company names from 'list', sorted alphabetically. Moving across again, I name a new formula cell 'occurrences' and then insert the formula
= COUNTIFS(list, distinct#)
to get a count corresponding to each distinct company.
The number of distinct companies 'countDistinct' is given by
Hello Ryan, Assuming the labels in A1:C1 are Duplicate, Distinct, and Count. I suggest these steps for backward compatibility. First, define the company names as DuplicateList with this formula: $A$2:INDEX($A:$A,COUNTA($A:$A)) Second, extract the distinct company names, sorted in ascending order, starting in B2 with this formula: =LOOKUP(PI(), 1/(COUNTIF(DuplicateList,”>=“&DuplicateList)=MAX(INDEX( COUNTIF(DuplicateList,”>=“&DuplicateList)*(COUNTIF(B$1:B1,DuplicateList)=0),0))), DuplicateList) Third, count the instances of each item in the Distinct List in the DuplicateList, starting in C2 with this formula: =COUNTIF(DuplicateList,B2) Consequently, there will be 2,512 distinct company names in Column B. Also, the sum of the counts in Column C will be 38,074.
O yes, you definitely will need a non-DA installation of Excel for the upcoming period, until all of your clients have upgraded. Virtual Machines (or multiple physical computers) are very useful for this.