1

I want to copy down filter formula using Arrayformula. I also gave a hand to copy down add-on but it's not giving required result. Here is a picture of an example sheet:

enter image description here

I am filtering the non blank row-wise data. As data is coming from Google Form thus want to auto replicate this formula. Copy-down add-on and ARRAYFORMULA both are giving inaccurate results.

0

1 Answer 1

0

Please use the following formula

=INDEX(IFERROR(SPLIT( 
                  TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B2:F),B2:F&"@",)&REPT(" "&"♣︎",COLUMN(B2:F)=COLUMN(F2))),"♣︎"))), 
                        "@")))

If on the other hand if you want to get just the first non-blank cells in each row, please use the following

=INDEX(IFERROR(SPLIT( 
                  TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B2:F),B2:F&"@",)&REPT(" "&"♣︎",COLUMN(B2:F)=COLUMN(F2))),"♣︎"))), 
                       "@")),,1)

(I know they are a bit long... But they work)

enter image description here

Functions used:

5
  • Thankyou for your help. But still facing an issue as this formula is returning only first value from each row. Please suggest Feb 15, 2021 at 14:47
  • docs.google.com/spreadsheets/d/… @marikamitsos please find the attached sheet for your reference Feb 16, 2021 at 4:55
  • Sorry. My mistake. Check the updated answer and think positive. You can 2 answers for just 1 question :) Feb 17, 2021 at 10:33
  • Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Feb 17, 2021 at 10:36
  • 1
    Thankyou @marikamitsos - it's working perfectly well. Feb 18, 2021 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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