Create Index lookup that takes duplicates into account
I have a dummy dataset in excel which looks like this.
For convenience assume that this range is pasted from cell A1 to B7 in a worksheet.
I want to create a range that shows me the top 5 highest Counts by ID. Hence the end product should look like:
To accomplish this I have used an index formula together with a match function containing a Large function that gives me which the K largest value is. So for example for the top 1 result in the ID column below I use:
=INDEX($A$2:$B$7,MATCH(LARGE($B$2:$B$7,1),$B$2:$B$7,0),1)
Similarly for the top 1 result in the Count column below I use
LARGE($B$2:$B$7,1)
If I apply this to all my cells, changing the value of K per row, I get a final result looking like:
As you can see the result works well for ID's that do not have the same Count. However on those where the Count is the same, I get duplicates. Hence what I am looking for is a function that looks if it is about to output a duplicate and then instead look if there is another ID with the same Count value.
I believe this may be challenging in base Excel but perhaps there is a way to do it in VBA? However I am not very familiar with VBA and I can't seem to find anything on the internet around this. Would anyone have a clue how to go about this?
Many thanks!
excel vba
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
add a comment |
I have a dummy dataset in excel which looks like this.
For convenience assume that this range is pasted from cell A1 to B7 in a worksheet.
I want to create a range that shows me the top 5 highest Counts by ID. Hence the end product should look like:
To accomplish this I have used an index formula together with a match function containing a Large function that gives me which the K largest value is. So for example for the top 1 result in the ID column below I use:
=INDEX($A$2:$B$7,MATCH(LARGE($B$2:$B$7,1),$B$2:$B$7,0),1)
Similarly for the top 1 result in the Count column below I use
LARGE($B$2:$B$7,1)
If I apply this to all my cells, changing the value of K per row, I get a final result looking like:
As you can see the result works well for ID's that do not have the same Count. However on those where the Count is the same, I get duplicates. Hence what I am looking for is a function that looks if it is about to output a duplicate and then instead look if there is another ID with the same Count value.
I believe this may be challenging in base Excel but perhaps there is a way to do it in VBA? However I am not very familiar with VBA and I can't seem to find anything on the internet around this. Would anyone have a clue how to go about this?
Many thanks!
excel vba
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
1
This can easily be done in vanilla Excel with a pivot table and a custom top filter -- not even any formulas required.
– Jeroen Mostert
Nov 12 '18 at 15:58
Yes I can see how that might look like a solution given the dummy sample code I posted. But in reality I have a very large Pivot with ID and Dates as a group and count as an aggregate together with many other aggregates. Hence it is a bit more complex and can't be done with filtering.
– user2182066
Nov 12 '18 at 16:12
1
It's possible to create a pivot table using a pivot table as the source (i.e. not modifying the first one). I do agree that can get complicated, though.
– Jeroen Mostert
Nov 12 '18 at 16:15
add a comment |
I have a dummy dataset in excel which looks like this.
For convenience assume that this range is pasted from cell A1 to B7 in a worksheet.
I want to create a range that shows me the top 5 highest Counts by ID. Hence the end product should look like:
To accomplish this I have used an index formula together with a match function containing a Large function that gives me which the K largest value is. So for example for the top 1 result in the ID column below I use:
=INDEX($A$2:$B$7,MATCH(LARGE($B$2:$B$7,1),$B$2:$B$7,0),1)
Similarly for the top 1 result in the Count column below I use
LARGE($B$2:$B$7,1)
If I apply this to all my cells, changing the value of K per row, I get a final result looking like:
As you can see the result works well for ID's that do not have the same Count. However on those where the Count is the same, I get duplicates. Hence what I am looking for is a function that looks if it is about to output a duplicate and then instead look if there is another ID with the same Count value.
I believe this may be challenging in base Excel but perhaps there is a way to do it in VBA? However I am not very familiar with VBA and I can't seem to find anything on the internet around this. Would anyone have a clue how to go about this?
Many thanks!
excel vba
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
I have a dummy dataset in excel which looks like this.
For convenience assume that this range is pasted from cell A1 to B7 in a worksheet.
I want to create a range that shows me the top 5 highest Counts by ID. Hence the end product should look like:
To accomplish this I have used an index formula together with a match function containing a Large function that gives me which the K largest value is. So for example for the top 1 result in the ID column below I use:
=INDEX($A$2:$B$7,MATCH(LARGE($B$2:$B$7,1),$B$2:$B$7,0),1)
Similarly for the top 1 result in the Count column below I use
LARGE($B$2:$B$7,1)
If I apply this to all my cells, changing the value of K per row, I get a final result looking like:
As you can see the result works well for ID's that do not have the same Count. However on those where the Count is the same, I get duplicates. Hence what I am looking for is a function that looks if it is about to output a duplicate and then instead look if there is another ID with the same Count value.
I believe this may be challenging in base Excel but perhaps there is a way to do it in VBA? However I am not very familiar with VBA and I can't seem to find anything on the internet around this. Would anyone have a clue how to go about this?
Many thanks!
excel vba
excel vba
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
asked Nov 12 '18 at 15:52
user2182066user2182066
8718
8718
1
This can easily be done in vanilla Excel with a pivot table and a custom top filter -- not even any formulas required.
– Jeroen Mostert
Nov 12 '18 at 15:58
Yes I can see how that might look like a solution given the dummy sample code I posted. But in reality I have a very large Pivot with ID and Dates as a group and count as an aggregate together with many other aggregates. Hence it is a bit more complex and can't be done with filtering.
– user2182066
Nov 12 '18 at 16:12
1
It's possible to create a pivot table using a pivot table as the source (i.e. not modifying the first one). I do agree that can get complicated, though.
– Jeroen Mostert
Nov 12 '18 at 16:15
add a comment |
1
This can easily be done in vanilla Excel with a pivot table and a custom top filter -- not even any formulas required.
– Jeroen Mostert
Nov 12 '18 at 15:58
Yes I can see how that might look like a solution given the dummy sample code I posted. But in reality I have a very large Pivot with ID and Dates as a group and count as an aggregate together with many other aggregates. Hence it is a bit more complex and can't be done with filtering.
– user2182066
Nov 12 '18 at 16:12
1
It's possible to create a pivot table using a pivot table as the source (i.e. not modifying the first one). I do agree that can get complicated, though.
– Jeroen Mostert
Nov 12 '18 at 16:15
1
1
This can easily be done in vanilla Excel with a pivot table and a custom top filter -- not even any formulas required.
– Jeroen Mostert
Nov 12 '18 at 15:58
This can easily be done in vanilla Excel with a pivot table and a custom top filter -- not even any formulas required.
– Jeroen Mostert
Nov 12 '18 at 15:58
Yes I can see how that might look like a solution given the dummy sample code I posted. But in reality I have a very large Pivot with ID and Dates as a group and count as an aggregate together with many other aggregates. Hence it is a bit more complex and can't be done with filtering.
– user2182066
Nov 12 '18 at 16:12
Yes I can see how that might look like a solution given the dummy sample code I posted. But in reality I have a very large Pivot with ID and Dates as a group and count as an aggregate together with many other aggregates. Hence it is a bit more complex and can't be done with filtering.
– user2182066
Nov 12 '18 at 16:12
1
1
It's possible to create a pivot table using a pivot table as the source (i.e. not modifying the first one). I do agree that can get complicated, though.
– Jeroen Mostert
Nov 12 '18 at 16:15
It's possible to create a pivot table using a pivot table as the source (i.e. not modifying the first one). I do agree that can get complicated, though.
– Jeroen Mostert
Nov 12 '18 at 16:15
add a comment |
1 Answer
1
active
oldest
votes
Link your ID output to that of the Count Ouput, using COUNTIF() to return the correct k.
=INDEX(A:A,AGGREGATE(15,7,ROW($A$2:$A$7)/($B$2:$B$7=F2),COUNTIF($F$2:F2,F2)))
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
Thank you Scott! This works very well!
– user2182066
Nov 12 '18 at 16:07
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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%2fstackoverflow.com%2fquestions%2f53265681%2fcreate-index-lookup-that-takes-duplicates-into-account%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
Link your ID output to that of the Count Ouput, using COUNTIF() to return the correct k.
=INDEX(A:A,AGGREGATE(15,7,ROW($A$2:$A$7)/($B$2:$B$7=F2),COUNTIF($F$2:F2,F2)))
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
Thank you Scott! This works very well!
– user2182066
Nov 12 '18 at 16:07
add a comment |
Link your ID output to that of the Count Ouput, using COUNTIF() to return the correct k.
=INDEX(A:A,AGGREGATE(15,7,ROW($A$2:$A$7)/($B$2:$B$7=F2),COUNTIF($F$2:F2,F2)))
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
Thank you Scott! This works very well!
– user2182066
Nov 12 '18 at 16:07
add a comment |
Link your ID output to that of the Count Ouput, using COUNTIF() to return the correct k.
=INDEX(A:A,AGGREGATE(15,7,ROW($A$2:$A$7)/($B$2:$B$7=F2),COUNTIF($F$2:F2,F2)))
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
Link your ID output to that of the Count Ouput, using COUNTIF() to return the correct k.
=INDEX(A:A,AGGREGATE(15,7,ROW($A$2:$A$7)/($B$2:$B$7=F2),COUNTIF($F$2:F2,F2)))
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
answered Nov 12 '18 at 15:57
Scott CranerScott Craner
89.8k82550
89.8k82550
Thank you Scott! This works very well!
– user2182066
Nov 12 '18 at 16:07
add a comment |
Thank you Scott! This works very well!
– user2182066
Nov 12 '18 at 16:07
Thank you Scott! This works very well!
– user2182066
Nov 12 '18 at 16:07
Thank you Scott! This works very well!
– user2182066
Nov 12 '18 at 16:07
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53265681%2fcreate-index-lookup-that-takes-duplicates-into-account%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
This can easily be done in vanilla Excel with a pivot table and a custom top filter -- not even any formulas required.
– Jeroen Mostert
Nov 12 '18 at 15:58
Yes I can see how that might look like a solution given the dummy sample code I posted. But in reality I have a very large Pivot with ID and Dates as a group and count as an aggregate together with many other aggregates. Hence it is a bit more complex and can't be done with filtering.
– user2182066
Nov 12 '18 at 16:12
1
It's possible to create a pivot table using a pivot table as the source (i.e. not modifying the first one). I do agree that can get complicated, though.
– Jeroen Mostert
Nov 12 '18 at 16:15