1

I'm attempting to create an ARRAYFORMULA that can extract the first non-blank cell value on each row in a range of columns.

My latest attempt is this:

=TRANSPOSE(QUERY(TRANSPOSE( A2:D6),"select * where Col1 <>'' limit 1",9^99))

Placed in F2, this generates all the output of the range F2:F6 in the table below.

However it's quite evident that all it does it to concatenate all the cells in each column using a space as a delimiter, so the output does not resemble my expected outcome, shown in H2:H6 :-(

I've come up with several other formulas, but the all exhibit the same problem. :-(

┌───────┬───────┬───────┬───────┬───────┬───┬────────────────┬───┬──────────┐
│       │   A   │   B   │   C   │   D   │ E │       F        │ G │    H     │
├───────┼───────┼───────┼───────┼───────┼───┼────────────────┼───┼──────────┤
│   1   │ Input │ Input │ Input │ Input │   │ Formula/Output │   │ Expected │
├───────┼───────┼───────┼───────┼───────┼───┼────────────────┼───┼──────────┤
│   2   │ Test0 │       │ Test1 │       │   │Test0 Test1     │   │ Test0    │
├───────┼───────┼───────┼───────┼───────┼───┼────────────────┼───┼──────────┤
│   3   │       │       │       │       │   │                │   │          │
├───────┼───────┼───────┼───────┼───────┼───┼────────────────┼───┼──────────┤
│   4   │       │       │       │ Test3 │   │   Test3        │   │ Test3    │
├───────┼───────┼───────┼───────┼───────┼───┼────────────────┼───┼──────────┤
│   5   │       │   5   │       │       │   │ 5              │   │ 5        │
├───────┼───────┼───────┼───────┼───────┼───┼────────────────┼───┼──────────┤
│   6   │       │       │54:74=2│       │   │  54:74=2       │   │ 54:74=2  │
└───────┴───────┴───────┴───────┴───────┴───┴────────────────┴───┴──────────┘

So my question is, how can I make an arrayformula or query that I can place in F2 that will only select the first non-blank value in each row from F2:D6, and output the result to F2:F6?

Improve this question
1
  • Your question has been edited according to your provided sample image.
    – marikamitsos
    Nov 16, 2019 at 6:01

2 Answers 2

Reset to default
1

The formula that would give you the desired results would be:

=ArrayFormula(REGEXREPLACE(ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B3:H8),B3:H8&"★",)&REPT(" "&"♣︎",COLUMN(B3:H3)=COLUMN(H3))),"♣︎")))),"([^★]+).+","$1"))

It is a bit convoluted but works fine.

Improve this answer
2
  • This is a great answer! It will take me some time to understand it, though. :-) I made a slight variation, so that a range is always provided as input: =ArrayFormula(REGEXREPLACE(ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(A2:D6), A2:D6&"★",)&REPT(" "&"♣︎",COLUMN(A2:D6)= CELL("col", A2:D6)+COLUMNS(A2:D6)-1)),"♣︎")))),"([^★]+).+","$1"))
    – fsteff
    Nov 16, 2019 at 7:27
  • I'm really curious. How did you come up with this part of the formula: CONCATENATE(IF(LEN(B3:H8),B3:H8&"★",)&REPT(" "&"♣︎",COLUMN(B3:H3)=COLUMN(H3))),"♣︎") ? Especially the use of IF in combination with CONCATENATE. I guess it's a known pattern, but where can I read more about it?
    – fsteff
    Nov 18, 2019 at 9:13
0

Try

=iferror(QUERY(transpose(A2:D2),"Select Col1 where Col1 is not null limit 1",0),"")

Improve this answer
2
  • Good try, tried it too, Unfortunately it only operates on one column at a time. Even when wrapped in an ARRAYFORMULA() and with the range changed to A2:D6. I updated the question to clarify.
    – fsteff
    Nov 15, 2019 at 5:01
  • 1
    This may assist webapps.stackexchange.com/questions/97310/…
    – Tedinoz
    Nov 15, 2019 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.