Re: Indexing Top Values without Pivot

Hello again,

For the Group formula, if you were to add "West", you would need to add the bolded part to the old formula:

*= IF(AND(ROW(Q2)/6>20,MOD(ROW(Q2),6)=2),"West",IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North","")))*

*And if we had an "East":**= IF(AND(ROW(Q2)/6>30,MOD(ROW(Q2),6)=2),"East",IF(AND(ROW(Q2)/6>20,MOD(ROW(Q2),6)=2),"West",IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North",""))))*

Now for the Name formula we need to add another condition for if the Group says "West":

*=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)), IF(Q2="West",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6),IF(A:A=Q2,C:C,0),0)),"")))*

Main thing here is telling the formula what nth largest number to pull for west: LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-**120**)/6). Since West starts in row 122, 1st largest= (122+4-120)/6=1, 2nd largest = (128+4-120)/6=2, etc...

Now if we were to add east:*=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="West",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6),IF(A:A=Q2,C:C,0),0)), IF(Q2="East",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-180)/6),IF(A:A=Q2,C:C,0),0)),""))))*

Notice "120" changed to "180" because East would start in row 182, and (182+4-180)/6 = 1.

To Recap all of that:**For east and west:**

Group (regular formula): Place this formula in cell Q2:

=IF(AND(ROW(Q2)/6>30,MOD(ROW(Q2),6)=2),"East",IF(AND(ROW(Q2)/6>20,MOD(ROW(Q2),6)=2),"West",IF(AND(ROW(Q2)/6>10,MOD(ROW(Q2),6)=2),"South",IF(AND(ROW(Q2)/6<=10,MOD(ROW(Q2),6)=2),"North",""))))

*Name (*array Formula)*: Place this array formula in cell R2:

=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="West",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="East",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-180)/6),IF(A:A=Q2,C:C,0),0)),""))))

=IF(Q2="North",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),(ROW(Q2)+4)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="South",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-60)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="West",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-120)/6),IF(A:A=Q2,C:C,0),0)),IF(Q2="East",INDEX(B:B,MATCH(LARGE(IF(A:A=Q2,C:C,0),((ROW(Q2)+4)-180)/6),IF(A:A=Q2,C:C,0),0)),""))))

**Make sure to double-click into the cell and press cntrl+shift+enter. If done correctly brackets will appear around the formula.*

Sales (regular formula): Place this formula in cell S2:Sales (regular formula): Place this formula in cell S2:

=IF(SUMIFS(C:C,A:A,Q2,B:B,R2)=0,"",SUMIFS(C:C,A:A,Q2,B:B,R2))=IF(SUMIFS(C:C,A:A,Q2,B:B,R2)=0,"",SUMIFS(C:C,A:A,Q2,B:B,R2))

I hope this helps you manipulate the formulas in the future and answers any questions you have! If you need anything else please do not hesitate to ask!

Sincerely,

Max