0

In an Excel worksheet, I have a rectangular region (C3:G7 in the image below) that contains people’s names (e.g., Paul, John, Frank, etc.)  I want to

  1. Create a unique (de-duped) linear list of names (in Column B; B9:B20 in the image below, but the number of rows depends on the number of unique names in the input range).
  2. In Column C (C9:C20), count the number of times each name appears in the input.  (This is easy to do with COUNTIF.)
  3. Sort the output list (B9:C20) by count (descending).

    spreadsheet with input in Rows 2 to 7 and output in rows 9 to 20

To reiterate: rows 2 to 7 are the input (B3:B7 can be ignored) and rows 9 to 20 are the output.  I’m stuck on the first step (generating the de-duped list of names) and I could use some help on the third step (sorting the output) too.

2

The COUNTIF function in Excel counts the number of cells in a range that match the supplied criteria

range - The range of cells to count. in the example C3 to G7 this range needs to be locked (i.e. row and column preceded by dollar sign) so when you drag the cell down the range stays the same.

criteria - The criteria that controls which cells should be counted. here counting "Chris" which is in cell B9.

so the formula required in cell C9 is

=COUNTIF($C$3:$G$7,B9)

Excel Countif Image

Select cell C9 then hover over the bottom right hand side of the cell C9 until the Cross hair appears then double click to populate the formula down to cell C20

More information on using countif can be found here https://support.office.com/en-us/article/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34

EDIT to allow for edited question

To sort the unique names you will need helper columns

In Cell J3 add the following code (note as indicated by the curly brackets this is entered as an array formula i.e. use Ctrl+Shift+Enter) and then drag down to populate the unique names list.

{=INDIRECT(TEXT(MIN(IF(($C$3:$G$7<>"")*(COUNTIF($J$2:J2,$C$3:$G$7)=0),ROW($3:$7)*100+COLUMN($C:$G),7^8)),"R0C00"),)&""}

Unique Names

Apply a countif in K3 as before with same range but this time referencing the adjacent unique name cell reference J3 then drag down.

=COUNTIF($C$3:$G$7,J3)

countif for unique name

Now rank the unique names based on their helper count column by using the following formula in I3 and drag down

=RANK(K3,$K$3:$K$14,0)+COUNTIF($K3:K$14,K3)-1

Ranked

Add rank position numbers 1 to 12 in A10 to A21 then use VLOOKUPs to match the position number to the ranking in the helper column and return the "Name" and Count respectively.

for B10 enter

=VLOOKUP($A10,$I$3:$K$14,2,FALSE)

Sorted names

and C10

=VLOOKUP($A10,$I$3:$K$14,3,FALSE)

Sorted

Highlight B10 and C10 then drag down to finish.

  • Answer edited to allow for question edits. – Antony Nov 28 '18 at 17:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.