Find maximum value for each group and retrieve associated value from another column
Please see sample data in attached picture.
I need to extract a list of Dept for all maximum values in CountOfDocs, for each unique MatterNumber.
So basically, given the data in the picture, my list should look like this:
000054 LIT
00006 BANKR
00007 NONE
00008 NONE
00009 BANKR
0001 BANKR
I played around with subtotals and pivot tables. I'm using Excel 2010, so I don't have the option to display distinct values in the pivot table... :(
worksheet-function microsoft-excel-2010
edited Jan 12 at 18:11
fixer1234
18.8k144982
asked Jan 11 at 14:30
user983929user983929
1
add a comment |
Please see sample data in attached picture.
I need to extract a list of Dept for all maximum values in CountOfDocs, for each unique MatterNumber.
So basically, given the data in the picture, my list should look like this:
000054 LIT
00006 BANKR
00007 NONE
00008 NONE
00009 BANKR
0001 BANKR
I played around with subtotals and pivot tables. I'm using Excel 2010, so I don't have the option to display distinct values in the pivot table... :(
worksheet-function microsoft-excel-2010
edited Jan 12 at 18:11
fixer1234
18.8k144982
asked Jan 11 at 14:30
user983929user983929
1
add a comment |
Please see sample data in attached picture.
I need to extract a list of Dept for all maximum values in CountOfDocs, for each unique MatterNumber.
So basically, given the data in the picture, my list should look like this:
000054 LIT
00006 BANKR
00007 NONE
00008 NONE
00009 BANKR
0001 BANKR
I played around with subtotals and pivot tables. I'm using Excel 2010, so I don't have the option to display distinct values in the pivot table... :(
worksheet-function microsoft-excel-2010
edited Jan 12 at 18:11
fixer1234
18.8k144982
asked Jan 11 at 14:30
user983929user983929
1
Please see sample data in attached picture.
I need to extract a list of Dept for all maximum values in CountOfDocs, for each unique MatterNumber.
So basically, given the data in the picture, my list should look like this:
000054 LIT
00006 BANKR
00007 NONE
00008 NONE
00009 BANKR
0001 BANKR
I played around with subtotals and pivot tables. I'm using Excel 2010, so I don't have the option to display distinct values in the pivot table... :(
worksheet-function microsoft-excel-2010
worksheet-function microsoft-excel-2010
edited Jan 12 at 18:11
fixer1234
18.8k144982
asked Jan 11 at 14:30
user983929user983929
1
edited Jan 12 at 18:11
fixer1234
18.8k144982
asked Jan 11 at 14:30
user983929user983929
1
edited Jan 12 at 18:11
fixer1234
18.8k144982
edited Jan 12 at 18:11
fixer1234
18.8k144982
edited Jan 12 at 18:11
fixer1234
18.8k144982
18.8k144982
asked Jan 11 at 14:30
user983929user983929
1
asked Jan 11 at 14:30
user983929user983929
1
asked Jan 11 at 14:30
user983929user983929
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There are fancy ways to do this, but it's always handy when you can knock out a simple solution. Here's an approach you can use for problems of this type. You were on the right track with a pivot table. You can use the pivot table for the heavy lifting of identifying the targets, then do a lookup based on the results. It's two steps, but it won't make your brain hurt.
You're only using the last three columns for this task, so I didn't include the Office column in my example.
Column D is a helper column to simplify the lookup. You can hide that if you don't want to see it. The counts aren't unique, so I combined them with the MatterNumber. I stuck a period between them to ensure uniqueness. That formula was just:
=A2 & "." & C2
(My example starts with MatterNumber in column A.) The ampersands concatenate the pieces.
You're already working with pivot tables, so I won't go into detail on how to do it. MatterNumber goes in the Rows window, and Count gets aggregated with Max.
To get the Dept name associated with each maximum count, lookup against the helper column to find the Dept name:
=INDEX($B$2:$B$19,MATCH(A23&"."&B23,$D$2:$D$19,0))
In my example, column B is Dept, which INDEX retrieves. MATCH looks in the helper column for an exact match with its own concatenated target value (MatterNumber.Max_Count).
Note that the pivot table sorts the results based on the Rows values. Also note that if there is a tie for maximum within a MatterNumber, this will retrieve just the first one.
If you don't want to see the Max-Count column of the pivot table, just position the pivot table so you can hide that column.
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
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%2f1393183%2ffind-maximum-value-for-each-group-and-retrieve-associated-value-from-another-col%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are fancy ways to do this, but it's always handy when you can knock out a simple solution. Here's an approach you can use for problems of this type. You were on the right track with a pivot table. You can use the pivot table for the heavy lifting of identifying the targets, then do a lookup based on the results. It's two steps, but it won't make your brain hurt.
You're only using the last three columns for this task, so I didn't include the Office column in my example.
Column D is a helper column to simplify the lookup. You can hide that if you don't want to see it. The counts aren't unique, so I combined them with the MatterNumber. I stuck a period between them to ensure uniqueness. That formula was just:
=A2 & "." & C2
(My example starts with MatterNumber in column A.) The ampersands concatenate the pieces.
You're already working with pivot tables, so I won't go into detail on how to do it. MatterNumber goes in the Rows window, and Count gets aggregated with Max.
To get the Dept name associated with each maximum count, lookup against the helper column to find the Dept name:
=INDEX($B$2:$B$19,MATCH(A23&"."&B23,$D$2:$D$19,0))
In my example, column B is Dept, which INDEX retrieves. MATCH looks in the helper column for an exact match with its own concatenated target value (MatterNumber.Max_Count).
Note that the pivot table sorts the results based on the Rows values. Also note that if there is a tie for maximum within a MatterNumber, this will retrieve just the first one.
If you don't want to see the Max-Count column of the pivot table, just position the pivot table so you can hide that column.
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
add a comment |
There are fancy ways to do this, but it's always handy when you can knock out a simple solution. Here's an approach you can use for problems of this type. You were on the right track with a pivot table. You can use the pivot table for the heavy lifting of identifying the targets, then do a lookup based on the results. It's two steps, but it won't make your brain hurt.
You're only using the last three columns for this task, so I didn't include the Office column in my example.
Column D is a helper column to simplify the lookup. You can hide that if you don't want to see it. The counts aren't unique, so I combined them with the MatterNumber. I stuck a period between them to ensure uniqueness. That formula was just:
=A2 & "." & C2
(My example starts with MatterNumber in column A.) The ampersands concatenate the pieces.
You're already working with pivot tables, so I won't go into detail on how to do it. MatterNumber goes in the Rows window, and Count gets aggregated with Max.
To get the Dept name associated with each maximum count, lookup against the helper column to find the Dept name:
=INDEX($B$2:$B$19,MATCH(A23&"."&B23,$D$2:$D$19,0))
In my example, column B is Dept, which INDEX retrieves. MATCH looks in the helper column for an exact match with its own concatenated target value (MatterNumber.Max_Count).
Note that the pivot table sorts the results based on the Rows values. Also note that if there is a tie for maximum within a MatterNumber, this will retrieve just the first one.
If you don't want to see the Max-Count column of the pivot table, just position the pivot table so you can hide that column.
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
add a comment |
There are fancy ways to do this, but it's always handy when you can knock out a simple solution. Here's an approach you can use for problems of this type. You were on the right track with a pivot table. You can use the pivot table for the heavy lifting of identifying the targets, then do a lookup based on the results. It's two steps, but it won't make your brain hurt.
You're only using the last three columns for this task, so I didn't include the Office column in my example.
Column D is a helper column to simplify the lookup. You can hide that if you don't want to see it. The counts aren't unique, so I combined them with the MatterNumber. I stuck a period between them to ensure uniqueness. That formula was just:
=A2 & "." & C2
(My example starts with MatterNumber in column A.) The ampersands concatenate the pieces.
You're already working with pivot tables, so I won't go into detail on how to do it. MatterNumber goes in the Rows window, and Count gets aggregated with Max.
To get the Dept name associated with each maximum count, lookup against the helper column to find the Dept name:
=INDEX($B$2:$B$19,MATCH(A23&"."&B23,$D$2:$D$19,0))
In my example, column B is Dept, which INDEX retrieves. MATCH looks in the helper column for an exact match with its own concatenated target value (MatterNumber.Max_Count).
Note that the pivot table sorts the results based on the Rows values. Also note that if there is a tie for maximum within a MatterNumber, this will retrieve just the first one.
If you don't want to see the Max-Count column of the pivot table, just position the pivot table so you can hide that column.
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
There are fancy ways to do this, but it's always handy when you can knock out a simple solution. Here's an approach you can use for problems of this type. You were on the right track with a pivot table. You can use the pivot table for the heavy lifting of identifying the targets, then do a lookup based on the results. It's two steps, but it won't make your brain hurt.
You're only using the last three columns for this task, so I didn't include the Office column in my example.
Column D is a helper column to simplify the lookup. You can hide that if you don't want to see it. The counts aren't unique, so I combined them with the MatterNumber. I stuck a period between them to ensure uniqueness. That formula was just:
=A2 & "." & C2
(My example starts with MatterNumber in column A.) The ampersands concatenate the pieces.
You're already working with pivot tables, so I won't go into detail on how to do it. MatterNumber goes in the Rows window, and Count gets aggregated with Max.
To get the Dept name associated with each maximum count, lookup against the helper column to find the Dept name:
=INDEX($B$2:$B$19,MATCH(A23&"."&B23,$D$2:$D$19,0))
In my example, column B is Dept, which INDEX retrieves. MATCH looks in the helper column for an exact match with its own concatenated target value (MatterNumber.Max_Count).
Note that the pivot table sorts the results based on the Rows values. Also note that if there is a tie for maximum within a MatterNumber, this will retrieve just the first one.
If you don't want to see the Max-Count column of the pivot table, just position the pivot table so you can hide that column.
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
edited Jan 12 at 17:33
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
answered Jan 12 at 7:38
fixer1234fixer1234
18.8k144982
18.8k144982
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.
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%2f1393183%2ffind-maximum-value-for-each-group-and-retrieve-associated-value-from-another-col%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
