CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Omar
Omar

Reputation: 117

How to lookup for a value in the header

My headers contain types of errors whenever someone submits a new form he marks someone down in some errors which will be shown as a No in the cell below the error

I tried to do it by applying this formula 

=ARRAYFORMULA(if(row(A:A)=1,"Errors",if(len(A:A)=0,iferror(1/0),IFERROR(INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1,SMALL(IF($D:$EJ="No",COLUMN($T:$FK)+19,""),1),4),1,""),"1")),""))))

But I got only the first error

I need to get the names of the errors that was marked down to be in a separate column named errors at the end of the spreadsheet and if someone was marked down in many errors it will be shown like this error1,error2,error3 ...ETC

Here is a spreadsheet containing some sample data https://docs.google.com/spreadsheets/d/1SksZv0h82j5oEZBj2AN5anDFr80AYNR5ettSwkpUKys/edit#gid=0

Upvotes: 1

Views: 98

Answers (1)

player0
player0

Reputation: 1

={"Errors"; ARRAYFORMULA(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(D2:EJ="no", D1:EJ1&", ", )),,999^99))), ",$", ""))}

0

Upvotes: 1

Related Questions