Forum Discussion
Consolidating Data
Can someone help me with a formula or macro to consolidate the following data into one column (K)? I need it to say the frequency with the first letter of the week day to follow. "Weekly T" I am doi...
=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.
- That definitely helps! Do you think there is a way to get the week day in there somehow?
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.
