RaqForum 43 No.
322 View  • 
     ...

Excel Query Sequences to Populate Transposed Values to Another Worksheet

 
excel(115)

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.

Q & A Collection

https://stackoverflow.com/questions/63840410/lookup-data-based-on-a-date-that-falls-between-two-dates


excel(115)
 
 •  322 View  •