Sort results of Index/Match array formula OR VBA to paste values

[JonHaywood]

I'm using an Index/Match array formula to pull out results from a data table (based on three search criteria). The formula works great, except that it populates the results in the same order as the data table - I want to sort them based on another category.

My data table is on Sheet 1...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]T/L[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A Person[/TD]
[TD="align: center"]Kanban[/TD]
[TD="align: center"]01/06/14[/TD]
[TD="align: center"]Text1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]A Person[/TD]
[TD="align: center"]Stores[/TD]
[TD="align: center"]02/06/14[/TD]
[TD="align: center"]Text2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]02/06/14[/TD]
[TD="align: center"]Text3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]B Good[/TD]
[TD="align: center"]Kanban[/TD]
[TD="align: center"]02/06/14[/TD]
[TD="align: center"]Text4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]A Person[/TD]
[TD="align: center"]Kanban[/TD]
[TD="align: center"]03/06/14[/TD]
[TD="align: center"]Text5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]C Clearly[/TD]
[TD="align: center"]Kanban[/TD]
[TD="align: center"]03/06/14[/TD]
[TD="align: center"]Text6[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]B Good[/TD]
[TD="align: center"]Carousel[/TD]
[TD="align: center"]04/06/14[/TD]
[TD="align: center"]Text7[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]C Clearly[/TD]
[TD="align: center"]Stores[/TD]
[TD="align: center"]05/06/14[/TD]
[TD="align: center"]Text8[/TD]
[/TR]
</tbody>[/TABLE]

(Note: The blank cells in A4 and B4 are deliberate)

I don't want users to have access to this main data table, but I do want different users to be able to select some search criteria, then view a summary of their data. I've created a summary table on Sheet 2...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]T/L[/TD]
[TD="align: center"](Dropdown list of names)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Date From[/TD]
[TD="align: center"](date)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Date To[/TD]
[TD="align: center"](date)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

The search criteria are entered by the user in cells B1 to B3.

In A6 (then copied across and down) I have the formula...

{=IF(ROWS(A$6:A6)<=(SUMPRODUCT(--(Sheet1!$A$2:$A$9=$B$1),--(Sheet1!$C$2:$C$9>=$B$2),--(Sheet1!$C$2:$C$9<=($B$3+0.999)))),INDEX(Sheet1!B$2:B$9,SMALL(IF((Sheet1!$A$2:$A$9=$B$1)*(Sheet1!$C$2:$C$9>=$B$2)*(Sheet1!$C$2:$C$9<=($B$3+0.999)),ROW(Sheet1!B$2:B$9)-ROW(Sheet1!B$2)+1),ROWS(A$6:A6))),"")}

So, if B1 = A Person, B2 = 01/06/14 and B3 = 03/06/14 the results I get are...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Kanban[/TD]
[TD="align: center"]01/06/14[/TD]
[TD="align: center"]Text1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Stores[/TD]
[TD="align: center"]02/06/14[/TD]
[TD="align: center"]Text2[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Kanban[/TD]
[TD="align: center"]03/06/14[/TD]
[TD="align: center"]Text5[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to sort these results by location? I've tried all basic ways to sort them but no joy. I have looked at other posts where Autofilter has been suggested, but my VBA knowledge is weak and I can't figure out how to change any of the suggestions to fit my tables.

Alternatively, I'd be happy if there was a VBA routine to automatically copy and paste the values into another table which could be filtered and sorted by the user, but again, I can't figure out how to adapt the examples I've seen of this approach.

Help me Mr Excel forum... you're my only hope!!!

Thanks

 

[XOR LX]

Hi,

Perhaps this array formula** in A6 (I've removed your inclusion of +0.999 since I could not see its purpose, though kindly explain and/or re-insert it. I've also amended the SUMPRODUCT to a SUM since the use of the former in an array formula is somewhat tautological):

=IF(ROWS(A$6:A6)<=SUM((Sheet1!$A$2:$A$9=$B$1)*(Sheet1!$C$2:$C$9>=$B$2)*(Sheet1!$C$2:$C$9<=$B$3)),INDEX(Sheet1!B$2:B$9,MATCH(SMALL(IF(Sheet1!$A$2:$A$9=$B$1,IF(Sheet1!$C$2:$C$9>=$B$2,IF(Sheet1!$C$2:$C$9<=$B$3,COUNTIF(Sheet1!$B$2:$B$9,"<"&Sheet1!$B$2:$B$9)+ROW(Sheet1!$B$2:$B$9)/10^9))),ROWS(A$6:A6)),COUNTIF(Sheet1!$B$2:$B$9,"<"&Sheet1!$B$2:$B$9)+ROW(Sheet1!$B$2:$B$9)/10^9,0)),"")

Copy down and across as required.

Incidentally, it's more efficient to have the first clause of the IF statement in a separate cell somewhere, e.g. in D1:

=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$B$1),--(Sheet1!$C$2:$C$9>=$B$2),--(Sheet1!$C$2:$C$9<=$B$3))

and then the beginning of the formula becomes:

=IF(ROWS(A$6:A6)<=$D$1,INDEX(...

etc., and each instance of the formula does not have to perform this initial calculation again.

Regards

 

[JonHaywood]

Wow, thank you - it works a treat!!

If I'm seeing it right, the SMALL function brings the results out in order based on the figure created by the COUNTIF + the row number divided by 10^9. It's ingenious!

FYI, I included the +0.999 because the date field in my actual data also contains a timestamp (I simplified the data for posting); adding 0.999 includes the entries made on the same date as the 'Date To' criterion.

Also, the original version of the formula I adapted had the SUMPRODUCT in a 'helper cell'... I thought I was being clever by incorporating it into a sinlge formula!!! Lesson learnt there!

Thanks so much for taking time to help me.

 

[XOR LX]

Glad I could help and thanks for the clarification re the +0.999 - makes good sense now!

Cheers!