• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2019 Create distinct list based on multiple criteria

jonastiger

Member
Hi
I´m using the following formula to create a unique list based on multiple criteria:
{=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF[SERVICES[OBD]=B$5,COUNTIF($B$6:B7,SERVICES[COD]),""),0)),"")} - This for B7 and cells below
=IFERROR(INDEX(SERVICES[CLIENT],MATCH(B7,SERVICES[COD],0)),"") - This for C7 and cells below
=SUMIFS(SERVICES[PROF],SERVICES[OBD],B$5,SERVICES[COD],B7) - This for D7 and cells bellow
SERVICES is a table with more than 10000 rows and it grows everyday
The distinct list has no more than 20 unique items.
I have 4 more structures with these formula starting at SERVICES[OBD]=F$5, SERVICES[OBD]=J$5, SERVICES[OBD]=N$5 and SERVICES[OBD]=R$5

This works, but everytime I change anything in this file, I have to wait for iterative calculation to finish to see the update, and it takes too damn long

Is there a way to change the array formula to make the calculation faster or convert this in a VBA option.

Thank you in advance
 

jonastiger

Member
Hi
Here's a sample of original file
Note that original has more than 20 sheets with pivots, dashboards and queries, don't know if file performance and speed is afected by all sheets data, but slow iterative calculation only happened since I had created the plan sheet with the array formula
Thanks in advance
 

Attachments

Peter Bartholomew

Well-Known Member
I use Excel 365 so, for me, everything is an array formula; it is the default. The problem I encountered was that instead of counting matches in the cells above the formula cell, you included the cell itself in the formula. I suspect that simply adjusting the range reference to exclude the formula cell will sort the problem. My alternatives are FILTER and UNIQUE but they are not available to you.
 

jonastiger

Member
...The problem I encountered was that instead of counting matches in the cells above the formula cell, you included the cell itself in the formula...
It´s the method I know to extract distinct values from a source. Is there another way to do that? How do I exclude the cell above?
 

Peter Bartholomew

Well-Known Member
I think there is a mistake in your implementation of the method. I do not think that the formula in B10, for example, should be
=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF(SERVICES[OBD]=B$5,COUNTIF(B$6:B10,SERVICES[COD]),""),0)),"")
I believe the reference should stop at cell B9.
=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF(SERVICES[OBD]=B$5,COUNTIF(B$6:B9,SERVICES[COD]),""),0)),"")
It may be necessary to set the calculation to manual whilst you make changes.
 

jonastiger

Member
Sorry for the late answer
I think there is a mistake in your implementation of the method. I do not think that the formula in B10, for example, should be
=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF(SERVICES[OBD]=B$5,COUNTIF(B$6:B10,SERVICES[COD]),""),0)),"")
I believe the reference should stop at cell B9.
=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF(SERVICES[OBD]=B$5,COUNTIF(B$6:B9,SERVICES[COD]),""),0)),"")
It may be necessary to set the calculation to manual whilst you make changes.
A simple detail that makes all the difference, in front of my eyes :(
Thank you very much for the correction. It works fine.
 

p45cal

Well-Known Member
I´m using the following formula to create a unique list based on multiple criteria:
{=IFERROR(INDEX(SERVICES[COD],MATCH(0,IF[SERVICES[OBD]=B$5,COUNTIF($B$6:B7,SERVICES[COD]),""),0)),"")} - This for B7 and cells below
=IFERROR(INDEX(SERVICES[CLIENT],MATCH(B7,SERVICES[COD],0)),"") - This for C7 and cells below
=SUMIFS(SERVICES[PROF],SERVICES[OBD],B$5,SERVICES[COD],B7) - This for D7 and cells bellow
It gets hard to maintain so many long formulae.
In the attached is a suggestion using pivot tables; I've organised them on your Plan sheet below your existing data for comparison.
I suspect you may just need 1 pivot table and you select which OBD it refers to in the dropdown at the top of the pivot table.
No complicated formulae at all - nothing to maintain.
I have put one formula in the top right of each pivot to replicate the total (eg. in cell D31) you have, but that info is already there at the bottom.
 

Attachments

Top