Forum Discussion
Consolidating Data
=INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))
Is this what you are looking for? It works in my spreadsheet if the columns B, D, F, H and J are empty. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
I am very grateful for your help with this!
=INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)Maybe with this formula if you want to return e.g. "monthly W ". Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
OliverScheurich This looks wonderful! 🙂 I forgot that sometimes there is multiple visits a week. Is there a way to add this too? Like "Weekly M, W, F" etc.
Now i understand that within the same week there is either "weekly" or "monthly" or "bi-weekly" and so on. Therefore the result can be e.g. "weekly M W F" but never e.g. "weekly W bi-weekly T".
A solution could be this formula which seems to work in my spreadsheet:
=IF(COUNTA(A2:I2)=1,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1),IF(COUNTA(A2:I2)=2,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1),IF(COUNTA(A2:I2)=3,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),3)),1),IF(COUNTA(A2:I2)=4,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),3)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),4)),1),IF(COUNTA(A2:I2)=5,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&" "&LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLA NK(A2:I2))),0)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),2)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),3)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),4)),1)&" "&LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2<>"",COLUMN($A:$I)),5)),1),"")))))