Excel: how to count and group rows
up vote
-3
down vote
favorite
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:
microsoft-excel worksheet-function libreoffice-calc
asked Nov 30 at 0:12
Roby Sottini
974
add a comment |
up vote
-3
down vote
favorite
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:
microsoft-excel worksheet-function libreoffice-calc
asked Nov 30 at 0:12
Roby Sottini
974
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.
– pat2015
Nov 30 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
Nov 30 at 4:42
Also, do you have the list of unique names for col D, or does the solution need to generate that?
– fixer1234
Nov 30 at 9:38
add a comment |
up vote
-3
down vote
favorite
up vote
-3
down vote
favorite
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:
microsoft-excel worksheet-function libreoffice-calc
asked Nov 30 at 0:12
Roby Sottini
974
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:
microsoft-excel worksheet-function libreoffice-calc
microsoft-excel worksheet-function libreoffice-calc
asked Nov 30 at 0:12
Roby Sottini
974
asked Nov 30 at 0:12
Roby Sottini
974
asked Nov 30 at 0:12
Roby Sottini
974
asked Nov 30 at 0:12
Roby Sottini
974
asked Nov 30 at 0:12
Roby Sottini
974
974
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.
– pat2015
Nov 30 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
Nov 30 at 4:42
Also, do you have the list of unique names for col D, or does the solution need to generate that?
– fixer1234
Nov 30 at 9:38
add a comment |
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.
– pat2015
Nov 30 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
Nov 30 at 4:42
Also, do you have the list of unique names for col D, or does the solution need to generate that?
– fixer1234
Nov 30 at 9:38
1
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.
– pat2015
Nov 30 at 0:36
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.
– pat2015
Nov 30 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
Nov 30 at 4:42
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
Nov 30 at 4:42
Also, do you have the list of unique names for col D, or does the solution need to generate that?
– fixer1234
Nov 30 at 9:38
Also, do you have the list of unique names for col D, or does the solution need to generate that?
– fixer1234
Nov 30 at 9:38
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
accepted
Try this formula:
=COUNTIF(A:A,D2)-COUNTIF(B:B,D2)
answered Nov 30 at 9:30
Lee
80927
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
Nov 30 at 9:37
add a comment |
up vote
1
down vote
To Count & Group Rows do the followings:
How it works:
To create List of Unique Names, write this Array formula in Cell
D3, finish withCtrl+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))), "")}
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.
answered Nov 30 at 11:08
Rajesh S
3,6271522
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1379610%2fexcel-how-to-count-and-group-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
Try this formula:
=COUNTIF(A:A,D2)-COUNTIF(B:B,D2)
answered Nov 30 at 9:30
Lee
80927
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
Nov 30 at 9:37
add a comment |
up vote
2
down vote
accepted
Try this formula:
=COUNTIF(A:A,D2)-COUNTIF(B:B,D2)
answered Nov 30 at 9:30
Lee
80927
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
Nov 30 at 9:37
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
Try this formula:
=COUNTIF(A:A,D2)-COUNTIF(B:B,D2)
answered Nov 30 at 9:30
Lee
80927
Try this formula:
=COUNTIF(A:A,D2)-COUNTIF(B:B,D2)
answered Nov 30 at 9:30
Lee
80927
answered Nov 30 at 9:30
Lee
80927
answered Nov 30 at 9:30
Lee
80927
answered Nov 30 at 9:30
Lee
80927
80927
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
Nov 30 at 9:37
add a comment |
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
Nov 30 at 9:37
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
Nov 30 at 9:37
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
Nov 30 at 9:37
add a comment |
up vote
1
down vote
To Count & Group Rows do the followings:
How it works:
To create List of Unique Names, write this Array formula in Cell
D3, finish withCtrl+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))), "")}
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.
answered Nov 30 at 11:08
Rajesh S
3,6271522
add a comment |
up vote
1
down vote
To Count & Group Rows do the followings:
How it works:
To create List of Unique Names, write this Array formula in Cell
D3, finish withCtrl+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))), "")}
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.
answered Nov 30 at 11:08
Rajesh S
3,6271522
add a comment |
up vote
1
down vote
up vote
1
down vote
To Count & Group Rows do the followings:
How it works:
To create List of Unique Names, write this Array formula in Cell
D3, finish withCtrl+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))), "")}
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.
answered Nov 30 at 11:08
Rajesh S
3,6271522
To Count & Group Rows do the followings:
How it works:
To create List of Unique Names, write this Array formula in Cell
D3, finish withCtrl+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))), "")}
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.
answered Nov 30 at 11:08
Rajesh S
3,6271522
edited Nov 30 at 13:08
answered Nov 30 at 11:08
Rajesh S
3,6271522
answered Nov 30 at 11:08
Rajesh S
3,6271522
answered Nov 30 at 11:08
Rajesh S
3,6271522
3,6271522
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1379610%2fexcel-how-to-count-and-group-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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.
– pat2015
Nov 30 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
Nov 30 at 4:42
Also, do you have the list of unique names for col D, or does the solution need to generate that?
– fixer1234
Nov 30 at 9:38