Consolidating Data

Question

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 doing this manually right now for 200+ rows and I need an easier way. Any help is appreciated!

oliverscheurich · Answer

leightob&nbsp;=INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&nbsp;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.

oliverscheurich · Answer

leightob&nbsp;=INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&amp;" "&amp;LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&nbsp;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 · Answer

leightob&nbsp;=IF(COUNTA(A2:I2)&gt;1,INDEX(A2:I2,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),1))&amp;" "&amp;LEFT(LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),1)),1),1)&amp;" "&amp;INDEX(A2:I2,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),2))&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),2)),1),INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&amp;" "&amp;LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1))This could be a solution if there are one or two visits a week. This formula would become more complicated if there were three or four or five visits a week.&nbsp;However i'm sure that there is an easier solution in Excel versions 2013 and newer. If you work with Office365 or 2021 a possibility can be using formulas like LET and TEXTJOIN.

oliverscheurich · Answer

leightob&nbsp;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".&nbsp;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))&amp;" "&amp;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))&amp;" "&amp;LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),2)),1),IF(COUNTA(A2:I2)=3,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&amp;" "&amp;LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),2)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),3)),1),IF(COUNTA(A2:I2)=4,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&amp;" "&amp;LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLANK(A2:I2))),0)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),2)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),3)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),4)),1),IF(COUNTA(A2:I2)=5,INDEX(A2:I2,MATCH(1,N(NOT(ISBLANK(A2:I2))),0))&amp;" "&amp;LEFT(INDEX($A$1:$I$1,MATCH(1,N(NOT(ISBLA
NK(A2:I2))),0)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),2)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),3)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),4)),1)&amp;" "&amp;LEFT(INDEX($A$1:$I$1,SMALL(IF(A2:I2&lt;&gt;"",COLUMN($A:$I)),5)),1),"")))))&nbsp;

leightob · Answer

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!

Forum Discussion

Consolidating Data

7 Replies

Resources