Excel Query Sequences to Populate Transposed Values to Another Worksheet
Problem description & analysis
The sheet2 of an Excel file is a to-be-searched data area, where column A contains start dates and column B contains end dates, and the date periods of each row are not overlapped. Below is part of the data area:
A |
B |
C |
D |
E |
F |
|
1 |
this is a title |
|||||
2 |
date period |
2wk HH |
WPH |
WCP |
WPL |
|
3 |
2018/7/10 |
2018/7/14 |
0.7839 |
0.7729 |
0.772 |
0.7655 |
4 |
2018/7/17 |
2018/7/21 |
0.799 |
0.794 |
0.7909 |
0.7599 |
5 |
2018/7/24 |
2018/7/28 |
0.8069 |
0.8007 |
0.7911 |
0.7783 |
By entering a date parameter, we get the corresponding row data, take columns from C to F, transpose the row data to column data, and populate values in C2:C5 of sheet1. Suppose the date parameter we entered is 2018/7/18, then sheet 1 will become as follows:
A |
B |
C |
|
1 |
result below |
||
2 |
2wk HH |
0.799 |
|
3 |
WPH |
0.7729 |
|
4 |
WCP |
0.772 |
|
5 |
WPL |
0.7655 |
|
The algorithm involves sequence query and single-record transposition.
Solution & explanation
Use the SPL XLL plug-in of Excel
A1 is the input date, write the formular in C2:
=spl("=?1.select@1(?2>~(1) && ?2<=~(2)).to(3,)",Sheet2!A3:F5,A1)
如图：
Explanation:
Query sequences by a certain date period. Here A1 is the input date parameter, whose value is, e.g. 2018/7/18. The date format should be consistent with that defined in Excel, which is yyyy/mm/dd. Get members from the 3rd one on.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
