006 Query and Sort Excel Data by Ranges

 

Title: Excel: Match and Index based on range

Source: https://stackoverflow.com/questions/64164736/excel-match-and-index-based-on-range

Question

I am stumped with the following problem and not sure how to accomplish it in excel. Here is an example of the data:

A    B

1 Date    Stock_Return

2 Jan-95    -5.2%

3 Feb-95    2.1%

4 Mar-95    3.7%

5 Apr-95    6.9%

6 May-95    6.5%

7 Jun-95    -5.6%

8 Jul-95    6.6%

9 Aug-95    6.2%

What I would like is to have the dates returned which fall within a certain return range and sorted from low to high.

For example:

1  2  3  4  5

Below -7%  0  0  0  0  0

-7% to -5%  Jun-95  Jan-95  0  0  0

-5% to -3%  0  0  0  0  0

-3% to 0%  0  0  0  0  0

0% to 3%  Feb-95  0  0  0  0

3% to 5%  Mar-95  0  0  0  0

5% to 7%  Aug-95  May-95  Jul-95 Apr-95 0

I thought Index and Match might make the most sense but when I drag across columns it doesn’t work. Any help is very much appreciated.

 

Answer

This is a procedural computation. You create a set of continuous ranges, loop through each range to query the original data, sort the result by ranges, and export it to a two-dimensional table.

The first two answers are very close to the correct solution. But both got stuck on the sorting operation because they tried to solve the problem using formula. Yet formulas are not suitable for handling a computation with a long series of operations.

Scripting languages are the right choice.

Besides Excel VBA, Python Pandas and esProc SPL can also handle Excel data conveniently. VBA is designed for programmers, Pandas is complicated to install and deploy, and SPL is the simplest. You can use the following SPL script to accomplish your task:

A

B

C

D

1

=file("D:\data.xlsx").xlsimport@t()

2

=[-inf(),-0.07,-0.05,-0.03,0,0.03,0.05,0.07]

3

=create(range,1,2,3,4,5)

/Create an empty result set

4

for A2.len()-1

=A2(#A4)

=A2(#A4+1)

/ Loop through each range

5

=A1.select(Stock_Return>B4   &&  Stock_Return<=C4).sort(Stock_Return)

/ Query the source data and   sort the result

6

=A3.record([B4/   "to" /C4]|B5.(Date))

/ Append result to the   target result set

7

=file("D:\result.xlsx").xlsexport@t(A3)

SPL is good at handling operations on xls or csv files. Find more examples and learn more about SPL in http://www.raqsoft.com/p/script-over-csv-xls.