Pulling ordered list using array functions in Excel

Question

I have a report in excel that displays the sales results from each employee. The columns are Location, Region, Username & Sales. It is sorted by Sales descending, showing which employee has the best sales in the company.

I am attempting to have an additional sheet per region that displays the results for all employees in that region also sorted by Sales (to avoid sorting the results of the many regions myself everyday).

An example version of the first 12 rows of the Data Sheet:

    G      H           I           J        K             X
    Row    Location    Username    Sales    Region        Region    
    1      38          John.Doe    85       North1        North1
    2      154         John.Smith  83       South2
    3      23          E.Williams  83       North1
    4      210         M.Williams  79       East5
    5      139         Joe.Dawn    77       North2
    6      22          Kay.Smith   69       South2
    7      51          Jay.Smith   69       South2
    8      125         L.Smith     69       East2
    9      51          L.Day       69       South2
    10     23          23.Guest2   67       North1
    11     92          U.Goode     65       North4

I have successfully created an array function that pulls the Sales column of only the results in the specified region.

{=LARGE(SMALL(IF(IF(ISERROR(K:K),"",K:K)=$X$2,J:J),
ROW(INDIRECT("1:"&COUNTIF(K:K,$X$2)))),F2)}

I am attempting now for an array function that pulls the Username that matches the corresponding sales amount in the original array, and also matches the region. I am having trouble when a single region has 'ties' or more than one employee with the same sales that month. Here is what I started with for that function:

=INDEX(I:I,MATCH(1,(Y2=J:J)*($X$1=K:K),0)

but that is having trouble when a single region has multiple users with the same sales. So I am trying a conditional to accomodate, with the function I know that works for singles when there's only one of that sales for that region.

{=IF(COUNTIF($AB$2:AB2,AB2)>1,
INDEX(I:I,
SMALL(IF(J:J=AB2,
IF(K:K=$AB$2,ROW(K:K)-ROW(INDEX(K:K,1,1))+1)),
COUNTIF($AB$2:AB2,AB2))),
INDEX(I:I,MATCH(1,(AC2=J:J)*($AB$2=K:K),0)))}

The inner piece may be sufficient if it worked, excluding the need for the conditional:

{=INDEX(I:I,
SMALL(IF(J:J=AB2,
IF(K:K=$AB$2,ROW(K:K)-ROW(INDEX(K:K,1,1))+1)),
COUNTIF($AB$2:AB2,AB2)))}

I'll use the same function for Username. Expected results for two regions:

X        Y        Z          AA         AB       AC       AD         AE
Region   Sales    Username   Location   Region   Sales    Username   Location
North1   85       John.Doe   38         South2   83       John.Smith 154
         83       E.Williams 23                  69       Kay.Smith  22
         67       23.Guest2  23                  69       Jay.Smith  51
                                                 69       L.Day      51

Since beginning to type this question I have found a work around that includes a few additional columns to complete the calculation, but still wanted to ask this to see if it was possible for knowledge's sake.

score 1 · Accepted Answer · answered May 22 '18 at 21:54

1

With North1 in X2, these are the formulas for Y2:AA2.

=IFERROR(AGGREGATE(14, 6, ($J$2:$J$999)/($K$2:$K$999=X$2), ROW(1:1)), "")
=IFERROR(INDEX($H:$H, AGGREGATE(15, 6, ROW($2:$999)/(($K$2:$K$999=X$2)*($J$2:$J$999=Y2)), COUNTIF(Y$2:Y2, Y2))), "")
=IFERROR(INDEX($H:$H, AGGREGATE(15, 6, ROW($2:$999)/(($K$2:$K$999=X$2)*($J$2:$J$999=Y2)), COUNTIF(Y$2:Y2, Y2))), "")

Fill down as necessary.

With South2 in AB2, copy Y2:AA2 to AC2:AE2 and fill down as necessary.

answered May 22 '18 at 21:54

With an arbitrary number of employees which grows as the business does (and some not selling anything some months), is there a way to avoid the set length arrays? row($2:$999) for example. – Golden Ratio May 23 '18 at 16:05
Also, I'm always amazed with the tools I've yet to discover in excel. I spent the morning digesting Aggregate, and haven't previously seen the notation of "/" to select an array based on the conditions of another, I assume its read as 'over'. And I like the iferror() check, I had been using if(iserror()) for the same purpose. Thank you on all counts. – Golden Ratio May 23 '18 at 16:07
[Find the last non empty cell ADDRESS without VBA](https://stackoverflow.com/questions/38268333/find-the-last-non-empty-cell-address-without-vba?rq=1) – May 23 '18 at 21:43
The 6 tells aggregate to ignore errors. The / is division that forces non-matches into a #div/0! error so non-matches are ignored. – May 23 '18 at 21:46

Pulling ordered list using array functions in Excel

1 Answers1