Excel Count for Unique Combination without repetition of one variable

I have a list of data grouped by classroom type for example, and each classroom type has two variables that are paired together. I need to count how many unique combinations of these variables there are and which ones of the variables do not repeat.

For example,
ClassRoom   Color Option    Seat
Classroom 1 Green   3
Classroom 1 Blue    3
Classroom 1 Red     4
Classroom 1 Yellow  5
Classroom 1 Orange  6
Classroom 1 Purple  6
Classroom 1 Black   8
Classroom 2 Green   2
Classroom 2 Pink    2
Classroom 2 Red     4
Classroom 2 White   6
Classroom 2 Yellow  6
Classroom 2 Purple  8
Classroom 2 Black   8

In classroom 1, there are unique color options but when paired with the seat number, the seat number variable repeats in some combinations. I would like a way to count Green seat 3 as a unique pairing, and Blue 3 as a duplicate pairing as the seat variable is repeated. The same may be the case where the color option may repeat within the same classroom.

And again, my data set has these multiple groupings of different classrooms, so I would need a way to summarize the unique and duplicate pairing counts by Classroom.

Still not sure how you want your output formatted.

Option 1

You can use formulas to determine the counts.

I think a method of reformulating your question is to look for the

• Unique combinations of ClassRoom and Seat number
• Duplicates would then be the difference between the total seat count and the uniques.

In Excel/O365 with dynamic formulas, using a Table and structured references, you can use, in the output format below:

Unique:  =COUNT(UNIQUE(INDEX(FILTER(tblClassRoom,tblClassRoom[ClassRoom]=$J2),0,3)))
Duplicates: =COUNTIF(tblClassRoom[ClassRoom],$J2)-COUNT(UNIQUE(INDEX(FILTER(tblClassRoom,tblClassRoom[ClassRoom]=$J2),0,3)))

If you have an earlier version of Excel lacking those functions, you can use these array formulas that may require to be entered with CSE (ie: holding down ctrl + shift while hitting enter) :

Unique:  =SUM(N(FREQUENCY(IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]),IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]))>0))
Duplicates: =COUNTIF(tblClassRoom[ClassRoom],$J2)-SUM(N(FREQUENCY(IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]),IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]))>0))

Option 2

You could also use Power Query available in Excel 2010+, to output a list which shows the unique and duplicate entries.

• After inputting the table to PQ

  • Data → Get & Transform → From Table/Range

  • Group by ClassRoom and Seat

  • Operation: All Rows (no aggregation)

---

• Then add a custom column which extracts the Color Option column
  • Formula: =Table.Column([Grouped],"Color Option")

• At the top of the resultant column of List‘s, there is a double headed arrow. Click on that and select to extract values with a comma separator.
• Finally, delete the unneeded columns and rearrange them.

This results in a list of the various color options available for each classroom and seat:

All of the above can be done from the UI, but here is the generated M-Code:

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="tblClassRoom"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClassRoom", type text}, {"Color Option", type text}, {"Seat", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ClassRoom", "Seat"}, {{"Grouped", each _, type table [ClassRoom=text, Color Option=text, Seat=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Color Options", each Table.Column([Grouped],"Color Option")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Color Options", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Grouped"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ClassRoom", "Color Options", "Seat"})
in
    #"Reordered Columns"

Option 3

Or, if you just want to mark each entry in your original table as to whether it is a Unique or Duplicate, you can use the formula:

=IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)>1,"Duplicate","Unique")

or, if you want to use a table with structured references:

=IF(COUNTIFS(tblClassRoom[[#Headers],[ClassRoom]]:[@ClassRoom],[@ClassRoom],tblClassRoom[[#Headers],[Seat]]:[@Seat],[@Seat])>1,"Duplicate","Unique")

You can try following formula:

=SUMPRODUCT((A7=$A$2:A7)*((B7=$B$2:B7)+(C7=$C$2:C7)))>2

in the case mentioned by @Doc Brown, this formula will return one unique value and two duplicates.