Compile non-"empty" cells into one Column

[par0016]

Excel Question.xls


I have a spread sheet that breaks down 5 separate sections into a set number of increments. All the sections are different sizes and all the increments can be different as well. The way that I have the spreadsheet set up is that if you change the length of a section the column underneath will adjust itself by breaking into the right number of "cuts" based on the increments. For example in column D (Section A), if I changed the length from 9 to 15, the column underneath would extend to display the numbers 1-15 at the increment of 1. If I then change the increment to 3 the column below will show 3, 6, 9, 12, and 15. The gist of this spreadsheet is that the size of each column changes based on the increment and length inputs; the rest of the columns still have formulas but show as being blank.

My dilemma is that I now want to compile each of these columns (Section A-Section E) into one large column. I also want this 1 large column to adjust itself as each column changes. I would like to be able to do this with a formula and I do not want there to be any "empty" cells between results in the large final column. (there can be "empty" cells with formulas underneath the results, just not between the results.
I inputted what should be seen in the large column when the spreadsheet is finished. If you need a better explanation or more information let me know. If anybody has any ideas or has a solution I would be very thankful. (No macros please)

[Richard Buttrey]

Hi,

What have you got against a macro? This is one case where a macro would be far simpler than a very complex compound formula.

[par0016]

Iv'e never written a macros before and I don't have the time to learn how to write them/ use them. FOrmulas I understand and I am pressed for time.

[bebo021999]

Try this in J7 and copy down:

Please Login or Register  to view this content.

Does it work?

[Richard Buttrey]

@bebo

Nice one. I hadn't thought of that one. Well done.

[par0016]

That works great. But what if the numbers aren't linear? I will need to use this in some situations when the columns won't be linear.

[Richard Buttrey]

Please explain what YOU mean by linear. An example workbook would be useful.

[par0016]

Excel Question.xls

I also messed up with my original spreadsheet. This one is updated. Before I had the length corresponding to the number of rows in the column instead of the actual value of the length. So if the length is 22 that means that the column will not go past a value of 22 more than the previous column.

[bebo021999]

May be:
=IF(ROW(A1)>=COUNT($D$7:$H$32)+2,"",SMALL($D$7:$H$32,ROW(A1)))

[par0016]

Here is the non-linear example: Excel Question Non-Linear Example.xls


Each column now starts at a value that is a fraction of the previous column's end value

[bebo021999]

In your latest file, J column has not been updated? What 'd you like for J column?

[par0016]

Even if you can come up with a way to stack the 5 columns on top of eachother (including the blank spaces), I can use that as an intermediate step as I know how to eliminate the blank spaces.

[par0016]

Column A stacked on Column B stacked on Column C stacked on Column D stacked on Column E:

0
1
2
3
4
5
6
7
8
9
10
6
8
10
12
14
16
18
20
22
24
26
28
30
32
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
13
17
21
25
29
33
37
41
45
49
56
63
70
77
84

[par0016]

OK so I came up with a way to stack the columns on top of eachother using the formula:


=OFFSET(D$7,MOD(ROWS($D$1:$D6)-ROWS($J$1:$J$6),ROWS(D$7:D$32)),INT((ROWS($D$1:$D6)-ROWS($J$1:$J$6))/ROWS($D$7:D$32)))

Any ideas on maybe how to modify the formula to eliminate the blank spaces?

If not I can work with what I have but eliminating the intermediate step would be prefereable.

Thanks everyone for the help btw, I really appreciate it!

Updated Spreadsheet:
Excel Question Non-Linear Example.xls

[par0016]

I changed the formula in J to be slightly simpler: =OFFSET(D$7,MOD(ROWS($D7:$D$7)-1,ROWS(D$7:D$32)),INT((ROWS($D7:$D$7)-1)/ROWS($D$7:D$32)))

If somebody can help me change the section highlighted in red to actual be the number of rows in the 1st column and then switch to the number of rows in the 2nd column, and then 3rd column etc. when the time is right, I think we will have it. Any ideas?

[WHER]

If you don't mind a different approach, see attachment.

[Richard Buttrey]

@Wher
Nice idea.
You may need to build in a rounding correction when the increment in any of the first 4 columns are odd numbers (apart from 1).

I think the difficulty is going to be incorporating an INDIRECT() function to use the number of rows in each column which can be obtained by using a helper formula in D6

Please Login or Register  to view this content.

and copying that across to H6

If par0016 does eventually want a macro approach, then with the additional helper formula mentioned above the following macro will do it.

Please Login or Register  to view this content.