Dynamic list that sorts

[btardo01]

Hi there

I have the below formula that I use to create a dynamic list of my top 20 accounts by revenue in descending order. The only problem is that the assumption is that the client is only listed once in column E. Does anyone have a solution if the clients are listed multiple times in column E and I want the formula to create the list based on the sum of the values (in column G) for each client? ( basically I want to obtain the same results but realize the client could exist more than once and need to look at sum to sort vs a single value)

{=IF(ISERROR(INDEX('CL Detail'!$E$2:$E$502,MATCH(1,INDEX(('CL Detail'!$G$2:$G$502=LARGE(IF('CL Detail'!$G$2:$G$502=0,-100000000,'CL Detail'!$G$2:$G$502),ROWS(B$8:B9)))*(COUNTIF(B$8:B9,'CL Detail'!$E$2:$E$502)=0),),0))),"",INDEX('CL Detail'!$E$2:$E$502,MATCH(1,INDEX(('CL Detail'!$G$2:$G$502=LARGE(IF('CL Detail'!$G$2:$G$502=0,-100000000,'CL Detail'!$G$2:$G$502),ROWS(B$8:B9)))*(COUNTIF(B$8:B9,'CL Detail'!$E$2:$E$502)=0),),0)))}

I am really hoping be able to achieve this by keeping a formula.
thanks

 

[Eric W]

Maybe this could work for you:

	E	F	G	H	I	J	K	L	M
Name	Amount	Top 20	Name	Amount
Al	Bob
Bob	Julie
Mary	Lisa
5	Elayne	Elayne
Bob	Mark
Joe	Mary	15
Julie	Joe
Mark	Al
Lisa
Lisa
Bob

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]28[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]21[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]19[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

CL Detail​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]{=IF(L2="","",INDEX('CL Detail'!$E$2:$E$502,MATCH(L2,IF('CL Detail'!$E$2:$E$502<>"",IF(COUNTIF($K$1:$K1,'CL Detail'!$E$2:$E$502)=0,SUMIF('CL Detail'!$E$2:$E$502,'CL Detail'!$E$2:$E$502,'CL Detail'!$G$2:$G$502))),0)))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF('CL Detail'!$E$2:$E$502<>"",IF(COUNTIF($K$1:$K1,'CL Detail'!$E$2:$E$502)=0,SUMIF('CL Detail'!$E$2:$E$502,'CL Detail'!$E$2:$E$502,'CL Detail'!$G$2:$G$502))),1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[btardo01]

Thanks for responding and I was able to get the formula to work. However, I was wondering..is it possible to use multiple criteria when summing the totals. I have three criteria I want it to consider and tried changing the sumif to sumifs and adjusting accordingly, but that didn't work for me.

Secondly, with it being an array formula the performance is obviously effected the amount of rows increases. I guess the only way to improve performance would be VBA, right?

 

[Eric W]

I'm sure that there's a way to use multiple criteria. You'll need to provide details, and preferably an example, so I can see what you want.

True, array formulas can slow down your sheet, especially with large ranges. VBA could help. We could set up a button that executes a macro when you push it, that generates the list you want.

 

[btardo01]

Ok, hopefully this helps.


The first screen below is example of my data. The data on this tab will be 2 years worth of information. In this case its 2018 and 2019. The number of rows is dynamic and can increase or decrease.
The second tab is a print screen of my NB tab that I want to populate from the Vert Sch4 tab.

On the NB tab, I want two sections to be auto filled. Starting at A14, I want to create a client list here. This client list would include a list unique client names from the Vert Sch4 tab for only clients who have dollars in the sales flow bucket (from Vert Sch4 ) equal to cell B13 on the NB tab. This would be one of the criteria. It would also then return the results for each month. So this would be a second criteria. The quarters and full year are simple sum formulas. I would want the list to be sorted in descending order. The 2019 information would also need to populate. I would have to insert rows as necessary

Starting at A26, this would basically do the same thing as above except, it would only grab the clients with sales flow equal to the value in B25.

I then have a lost tab that would do exact same thing except I want to sort the list in ascending order of the total

 

[Eric W]

Your sample sheets did not make it. You may want to try one of the tools to print a screenshot, such as the HTML Maker in my signature. Without the sample, I really can't design the formulas and/or VBA code. But in looking at your requirements, I don't see anything that can't be done.

 

[btardo01]

I just went to the HTML Maker link and its telling me something is wrong. Do you know if that link is still active?

 

[Eric W]

I just tried it, and it worked for me.

 

[btardo01]

hmm, when I click on the HTML Maker in your signature, it takes me to another post. but when I click on the link in Peter's post to go to the HTML maker...thats when I get an error. But you got it to work from his post?

 

[Eric W]

I did. That link opened another OneDrive page which had the files available to download. You may need to try another browser, or make sure that your security settings allow it.