Currently I have a column of dates (A) and a column of numerical values (B).
<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).
<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)?
<tbody>
</tbody>
Output
<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="]INDEX (array, row_num, [col_num], [area_num])
[/FONT]
Any help would be appreciated.
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="]INDEX (array, row_num, [col_num], [area_num])
[/FONT]
Any help would be appreciated.