How to return first not empty cell from importrange values?

Question

my google sheet excel document contain data like this

+---+---+---+---+---+---+
|   | A | B | C | D | E | 
+---+---+---+---+---+---+
| 1 |   | c |   | x |   |
+---+---+---+---+---+---+
| 2 |   | r |   | 4 |   |
+---+---+---+---+---+---+
| 3 |   |   |   | m |   |
+---+---+---+---+---+---+
| 4 |   |   |   |   |   |
+---+---+---+---+---+---+

Column B and D contain data provided by IMPORTRANGE function, which are store in different files.

And i would like to fill column A with first not empty value in row, in other words: desired result must look like this:

+---+---+---+---+---+---+
|   | A | B | C | D | E | 
+---+---+---+---+---+---+
| 1 | c | c |   | x |   |
+---+---+---+---+---+---+
| 2 | r | r |   | 4 |   |
+---+---+---+---+---+---+
| 3 | m |   |   | m |   |
+---+---+---+---+---+---+
| 4 |   |   |   |   |   |
+---+---+---+---+---+---+

I tried ISBLANK function, but apperantly if column is imported then, even if the value is empty, is not blank, so this function dosn't work for my case. Then i tried QUERY function in 2 different variant:

1) =QUERY({B1;D1}; "select Col1 where Col1 is not null limit 1"; 0) but result in this case is wrong when row contain cells with numbers. Result with this query is following:

+---+---+---+---+---+---+
|   | A | B | C | D | E | 
+---+---+---+---+---+---+
| 1 | c | c |   | x |   |
+---+---+---+---+---+---+
| 2 | 4 | r |   | 4 |   |
+---+---+---+---+---+---+
| 3 | m |   |   | m |   |
+---+---+---+---+---+---+
| 4 |   |   |   |   |   |
+---+---+---+---+---+---+

2) =QUERY({B1;D1};"select Col1 where Col1 <> '' limit 1"; 0) / =QUERY({B1;D1};"select Col1 where Col1 != '' limit 1"; 0) and this dosn't work at all, result is always #N/A

Also i would like to avoid using nested IFs and javascript scripts, if possible, as solution with QUERY function suits for my case best due to easy expansion to another columns without any deeper knowladge about programming. Is there any way how to make it simply, just with QUERY, and i am just missing something, or i have to use IFs/javascript?

player0 · Accepted Answer · 2020-05-01 14:19:14Z

1

try:

=ARRAYFORMULA(SUBSTITUTE(INDEX(IFERROR(SPLIT(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE(SUBSTITUTE(B:G, " ", "♦")),,99^99))), " ")),,1), "♦", " "))

selective columns:

edited May 1, 2020 at 14:19

answered Apr 30, 2020 at 21:10

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hekimen Over a year ago

This dosn't work too, because i have to select exact columns, from example above it would be like {B1:B;D1:D}, in this case B and D refer to last cell in column, beacues of this i have to multiple numbers of rows in whole document by numbers of selected columns (#REF!: Error Result was not expanded automatically, please insert more rows (50500) ). I guess this is because when range is wrapped in { } brackets, then is outside of document context, empty cells included.

Hekimen Over a year ago

{B1:B\D1:D} this is give me parse error, so i guess this is most likely from excel, as excel change syntax on windows regional settings and if comma is deciaml symbol, then \ is used in place of comma in Array. If semi colon is decimal symbol, then \ is used in place of semi colon in Array. Never saw this separator for array in google sheets.

Collectives™ on Stack Overflow

How to return first not empty cell from importrange values?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related