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...

	A	B	C	D
1	T/L	Location	Date	Description
2	A Person	Kanban	01/06/14	Text1
3	A Person	Stores	02/06/14	Text2
4			02/06/14	Text3
5	B Good	Kanban	02/06/14	Text4
6	A Person	Kanban	03/06/14	Text5
7	C Clearly	Kanban	03/06/14	Text6
8	B Good	Carousel	04/06/14	Text7
9	C Clearly	Stores	05/06/14	Text8

<tbody>
</tbody>

(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...

	A	B	C
1	T/L	(Dropdown list of names)
2	Date From	(date)
3	Date To	(date)
4
5	Location	Date	Description
6
7
8
9
10
11

<tbody>
</tbody>

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...

	A	B	C
5	Location	Date	Description
6	Kanban	01/06/14	Text1
7	Stores	02/06/14	Text2
8	Kanban	03/06/14	Text5
9

<tbody>
</tbody>

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!