Listing values in an array in chronological order

[ATSJ]

Currently I have a column of dates (A) and a column of numerical values (B).

A	B
1/1/19
1/2/19	8
1/3/19	3
1/4/19
1/5/19	7

<tbody>
</tbody>

The dates are listed in ascending order in col A. The values are listed in col B but not every date has a value.
In a separate column (E) I want to list the values in B in a list without the blanks, in other words just a list of the numerical values. I'm using the following array formula to do that in column E.

{=IFERROR(INDEX(B$10:B$1000,SMALL(IF(B$10:B$1000<>"",ROW(B$10:B$1000)-ROW(B$10)+1),ROWS(B$10:B10))),"")}

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-style: inherit; font-variant-caps: inherit; font-stretch: inherit; line-height: inherit; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"></code>The formula looks down col. B starting at row 10 and whenever it finds a value, drops it in col. E and it works perfectly. The values are extracted in chronological order (and if I switch out SMALL for LARGE it reverses the list so the most recent are at the top).

E

8

3

7

<tbody>
</tbody>

However, I want to add a second column of numerical values in col. C. My question is - how can I adapt my current formula so it looks in both columns B and C rather than just B and continues to list them chronologically (in other words for each date, it looks in B and C before proceeding to the next date)?

A	B	C
1/1/19		3
1/2/19	2	7
1/3/19
1/4/19	1

<tbody>
</tbody>

Output

E

3

2

7

1

<tbody>
</tbody>

I've tried adapting the formula to include an array,

B$10:C$1000

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-style: inherit; font-variant-caps: inherit; font-stretch: inherit; line-height: inherit; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"></code>for example, but without success because in this instance (where there is more than one column in the array) it seems that INDEX requires a column reference that isn't in my existing formula and I haven't managed to add yet.

[FONT=&quot]INDEX (array, row_num, [col_num], [area_num])
[/FONT]
Any help would be appreciated.

 

[bosco_yip]

In E1, copied down to E4 :

=INDEX(($B$1:$B$4,$C$1:$C$4),AGGREGATE(15,6,ROW(A$1:A$4)/(B$1:C$4<>""),ROW(A1)),1,MOD(AGGREGATE(15,6,(ROW(A$1:A$4)*100+COLUMN(A$1:B$1))/(B$1:C$4<>""),ROW(A1)),100))

Regards

 

[ATSJ]

Thank you so much. Now, say I wanted to add a third column in D, would it be a simple process to update this to incorporate an extra column?

 

[bosco_yip]

Should you add (a 3rd) one more column in D1:D4

The formula basically look in same, just add one column and change the range reference, then become :

=INDEX(($B$1:$B$4,$C$1:$C$4,$D$1:$D$4),AGGREGATE(15,6,ROW(A$1:A$4)/(B$1:D$4<>""),ROW(A1)),1,MOD(AGGREGATE(15,6,(ROW(A$1:A$4)*100+COLUMN(A$1:C$1))/(B$1:D$4<>""),ROW(A1)),100))

I used Index function for the working because INDEX is easy for you to understanding

However, it would be simplified by :

=INDIRECT(TEXT(AGGREGATE(15,6,ROW(A$1:A$4)*100+COLUMN(B$1:D$1)/(B$1:D$4<>""),ROW(A1)),"R0C00"),0)

Regards
Bosco

 

[Peter_SSs]

Thank you so much. Now, say I wanted to add a third column in D, would it be a simple process to update this to incorporate an extra column?

Just a word of warning: The suggestions made so far will return incorrect results if rows are subsequently added or deleted above the data. The formulas of course can be made robust against that but I'll leave that for bosco_yip if that is a concern for you.

Also, if using the INDIRECT formula option be aware that INDIRECT is a volatile function so can slow your sheet down if you have a lot of those formulas in it.

If you have Excel through Office 365 or Excel 2019 (that is, you have the TEXTJOIN function) then here is another option for you.
Leave cell F9 blank
Formula in F10 copied down as far as you might need.

Excel Workbook
	A	B	C	D	E	F
8						Result
9
10	1/01/2019		3			3
11	1/02/2019	2	7	5		2
12	1/03/2019					7
13	1/04/2019	1		8		5
14						1
15						8
16
17
Chronological order

 

[Peter_SSs]

Leave cell F9 blank

If this requirement in my previous suggestion is unacceptable, then here is another (longer) version that does not have that requirement.

Excel Workbook
	A	B	C	D	E	F
9						Result
10	1/01/2019		3			3
11	1/02/2019	2	7	5		2
12	1/03/2019					7
13	1/04/2019	1		8		5
14						1
15						8
16
Chronological order (3)

 

[ATSJ]

That's great help, I appreciate it.

 

[Peter_SSs]

That's great help, I appreciate it.

Glad you found something useful among that. Thanks for letting us know.