-3

I have an Excel file with Column A and Column B. Both have people names (Elizabeth, John, Michael and Robert).

I need to count each name of Column A and count each name of Column B. And then I need to show each name (Name column) with the difference that I counted before (Difference column).

In databases it is called Group By.

I made this example so it can be easier to explain. It has less than 10 rows but I will use it with a file that has 7000 rows:

Example using LibreOffice

Improve this question
3
  • 1
    Is the difference always going to be Count A - Count B. Is Column B always a subset of Column A? Is there a possibility that item exists in Column B only but not in A? In that case how do you want to show the results? Explore COUNTIF Function and see if it will work here to solve this.
    – patkim
    Commented Nov 30, 2018 at 0:36
  • To add to pat2015's questions: Are the names always aggregated and sorted alphabetically as in the example? Do all names always appear in both columns? Is the col A count always >= col B count? If not, do you care about negative results or do you want the absolute difference? What version of what application are you using?
    – fixer1234
    Commented Nov 30, 2018 at 4:42
  • Also, do you have the list of unique names for col D, or does the solution need to generate that?
    – fixer1234
    Commented Nov 30, 2018 at 9:38

2 Answers 2

Reset to default
2

Try this formula:

=COUNTIF(A:A,D2)-COUNTIF(B:B,D2)

enter image description here

Improve this answer
1
  • 1
    How do you get the list of unique names in col D? The OP describes 7000 rows and doesn't mention having a list of the unique names (actually says both columns are needed). The safe approach is to either ask for clarification or include generating the list in the solution.
    – fixer1234
    Commented Nov 30, 2018 at 9:37
1

To Count & Group Rows do the followings:

enter image description here

How it works:

  1. To create List of Unique Names, write this Array formula in Cell D3, finish with Ctrl+Shift+Enter & fill down.

       {=IFERROR(IFERROR(INDEX($A$3:$A$11, MATCH(0, COUNTIF($D$2:D2, $A$3:$A$11), 0)), INDEX($B$3:$B$8, MATCH(0, COUNTIF($D$2:D2, $B$3:$B$8), 0))), "")}

  2. To count the difference, write this formula in Cell E3 & fill down.

    =COUNTIF($A$3:$A$11,D3)-COUNTIF($B$3:$B$8,D3)

Note, Formula 2 in my post is inspired from @Lee's Answer.

Adjust cell references in the Formula as needed.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .