myall_blues
Well-known Member
- Joined
- Nov 26, 2015
- Messages
- 679
- Office Version
- 365
- Platform
- Windows
Is it possible to SORTBY the result of a FILTER query by the results of another filter query? I've tried a few options but I can't make it work.
In the following simplified example (my real case is more complex with more columns but this is the basic gist of the problem) I have two sets of possibly matching data that I wish to check if they do match.
In columns G and H I have extracted the data from the two different years using FILTER. In column I, I tried to use SORTBY to sort the results of the 2000 data into the same order as the 1995 data but that didn't work. Based on the answer given here I tried using INDEX in column J but also without success.
Any insight appreciated.
In the following simplified example (my real case is more complex with more columns but this is the basic gist of the problem) I have two sets of possibly matching data that I wish to check if they do match.
In columns G and H I have extracted the data from the two different years using FILTER. In column I, I tried to use SORTBY to sort the results of the 2000 data into the same order as the 1995 data but that didn't work. Based on the answer given here I tried using INDEX in column J but also without success.
Any insight appreciated.
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Data 1 | Data 2 | Filter Data 1 (1995) | Filter Data 2 (2000) | Filter Data 2 (2000) ..SORTBY | Filter Data 2 (2000) ..SORTBY with INDEX | ||||||
2 | 2000 | CBC006 | 1995 | CBC013 | CBC001 | CBC009 | CBC009 | CBC009 | ||||
3 | 1995 | CBC006 | 2000 | CBC009 | CBC002 | CBC001 | CBC001 | CBC001 | ||||
4 | 2000 | CBC013 | 2000 | CBC001 | CBC006 | CBC002 | CBC002 | CBC002 | ||||
5 | 1995 | CBC001 | 2000 | CBC002 | CBC009 | CBC013 | CBC013 | CBC013 | ||||
6 | 1995 | CBC009 | 1995 | CBC002 | CBC013 | CBC006 | CBC006 | CBC006 | ||||
7 | 1995 | CBC002 | 1995 | CBC001 | ||||||||
8 | 2000 | CBC001 | 1995 | CBC009 | ||||||||
9 | 2000 | CBC002 | 2000 | CBC013 | ||||||||
10 | 1995 | CBC013 | 1995 | CBC006 | ||||||||
11 | 2000 | CBC009 | 2000 | CBC006 | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G6 | G2 | =SORT(FILTER(B2:B11,A2:A11=1995)) |
H2:H6 | H2 | =FILTER(E2:E11,D2:D11=2000) |
I2:I6 | I2 | =SORTBY(FILTER(E2:E11,D2:D11=2000),G2#) |
J2:J6 | J2 | =LET(_x,FILTER(E2:E11,D2:D11=2000),_y,SORT(FILTER(B2:B11,A2:A11=1995)),SORTBY(_x,INDEX(_y,0,1))) |
Dynamic array formulas. |