0

I have two columns in Microsoft Excel, A and B. The input is displayed below

A      B
ABC    1
ABC    1
ABC    2
ABC    2
ABC    2
DEF    1
DEF    3
DEF    3
CDE    1
CDE    2
CDE    3
CDE    4

I need an output like below whose meaning is also explained

ABC   2.....which means (two unique values with ABC, which is 1 & 2)
DEF   2......(1 & 3)
CDE   4.......(1,2,3,4)
fixer1234
  • 28,064
user3448838
  • 9

2 Answers2

1

Create a third column with in C1 the formula =A1 & B1 and copy-paste down.

After that, you can count them using a formula (which gives the counts in the table) or make a pivot table. So yur two options are from here:

  • In cell D1, use the formula =COUNTIF(C:C;"=" & C1 and copy-past that down too.
  • Create a pivot table based on the first three columns and put the data from column C in the pivot table columns and their frequency in the values of the pivot table.
agtoever
  • 6,402
0

If you can sort them, then put a column in which is: =IF(A3=A2,SIGN(B3-B2),1)

Then just put a subtotal in. :)

Rob Farley
  • 353