How to stack data from multiple columns on a different sheet

[michellemb]

Hello,

I am trying to create a tool to manage tasks according to date.

I have several non-adjacent columns of dates on sheet "MGMT". I would like to stack those columns (G, J, M, P, R) into one column on sheet "SA" and have the dates sorted in chronological order. I have tried a few formulas and only get "NAME?" to populate. I'm not experienced in VBA so would prefer a formula, if possible. I'm a novice and I am grateful for any assistance.

The attached file has sample data. I also have a row of what I would like the file to do.

Thank you,

Michelle

[Pete_UK]

Some of the cell references in your sample file are confusing, but if I understand correctly you can use the following formulae in the SA sheet in the cells stated:

B4: =INDEX(MGMT!B:R,INT((ROWS($1:1)-1)/5)+2,IF(MOD(ROWS($1:1)-1,5)=0,1,MOD(ROWS($1:1)-1,5)*3+3))

C4: =INDEX(MGMT!C:C,INT((ROWS($1:1)-1)/5)+2)

D4: =INDEX(MGMT!$B$1:$R$1,IF(MOD(ROWS($1:1)-1,5)=0,1,MOD(ROWS($1:1)-1,5)*3+3))

Copy these down as required.

Hope this helps.

Pete

[michellemb]

I'm sorry for the confusion. It was late when I posted this after hours of hitting a wall. I updated the file and hope that it is a little clearer.

For now, I will try your suggestion.

Thank you so much.

[michellemb]

Thank you so much!!!! This works. My last question for the formula in B4. How do I have the data automatically sort into chronological order as data is populates?

Thank you!

[Glenn Kennedy]

In B4:

=IFERROR(INDEX((MGMT!$B$2:$B$9,MGMT!$G$2:$G$9,MGMT!$J$2:$J$9,MGMT!$M$2:$M$9,MGMT!$P$2:$P$9),1+MOD((ROWS($1:1)-1),COUNTA(MGMT!$B$2:$B$9)),,1+INT((ROWS($1:1)-1)/COUNTA(MGMT!$B$2:$B$9))),"")

... assuming that by date order you wanted column B, then column G, then column J, etc.

Has this messed up the rest of the data (I did not check).

[michellemb]

The date column is doing exactly what I want it to do. Thank you!!!

Unfortunately, the other data in Column C and D does not match now.

[Glenn Kennedy]

Bummer. I'll look at them in a moment!

[Glenn Kennedy]

C4:
=if(b4="","",index(mgmt!$c$2:$c$9,1+mod((rows($1:1)-1),counta(mgmt!$b$2:$b$9))))

d4:
=if(b4="","",index((mgmt!$b$1,mgmt!$g$1,mgmt!$j$1,mgmt!$m$1,mgmt!$p$1),,,1+int((rows($1:1)-1)/counta(mgmt!$b$2:$b$9))))

[michellemb]

This was perfect. Thank you so much. I am truly grateful.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.