Bringing Values to Top of Column Formula?

[jwillits]

I assume I need some ARRAY formula, but not sure how to do it. I have blank cells and cells with a value in column C and Column D is the formula I am trying to make. I want to move all values in Column C to the top of Column D in the order that they are in in Column C with blank cells being on the very bottom. Here is an example

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A


PRODUCT[/TD]
[TD]B


DATE[/TD]
[TD]C

KNOWN PURCHASE FORMULA[/TD]
[TD]D

FORMULA TO BRING TO TOP OF LIST[/TD]
[/TR]
[TR]
[TD]APPLE 2224[/TD]
[TD]7/25[/TD]
[TD][/TD]
[TD]EGG1013
[/TD]
[/TR]
[TR]
[TD]EGG 1013[/TD]
[TD]8/23[/TD]
[TD]EGG 1013[/TD]
[TD]PEAR 1321[/TD]
[/TR]
[TR]
[TD]GRAPE 3121[/TD]
[TD]4/22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PEAR 1321[/TD]
[TD]2/22[/TD]
[TD]PEAR 1321[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated.

 

[Misca]

This one might not be the most elegant way to do it but it's the first I came up with:

=INDEX($C$2:$C$15,AGGREGATE(15,6,(NOT(ISBLANK($C$2:$C$15))*ROW($C$2:$C$15)/--NOT(ISBLANK($C$2:$C$15))),ROWS($C$2:C2))-1)

INDEX returns the value from the given range and the AGGREGATE is used as SMALL to return the smallest row number where the value is not blank. The division part is used to cause 0/0 errors to get rid of the zeroes. Since the AGGREGATE is returning row numbers and my INDEX range is not starting from the top of the C column, I need to subtract the header row from my row number. I'm taking the easy way out here by entering the header row number here. Usually you'd use yet another ROW or ROWS for that too.

 

[jwillits]

This one might not be the most elegant way to do it but it's the first I came up with:

=INDEX($C$2:$C$15,AGGREGATE(15,6,(NOT(ISBLANK($C$2:$C$15))*ROW($C$2:$C$15)/--NOT(ISBLANK($C$2:$C$15))),ROWS($C$2:C2))-1)

INDEX returns the value from the given range and the AGGREGATE is used as SMALL to return the smallest row number where the value is not blank. The division part is used to cause 0/0 errors to get rid of the zeroes. Since the AGGREGATE is returning row numbers and my INDEX range is not starting from the top of the C column, I need to subtract the header row from my row number. I'm taking the easy way out here by entering the header row number here. Usually you'd use yet another ROW or ROWS for that too.

Hmm, when I plug the column I am using into this formula, it is putting the value right beside it instead of bringing it to the top of the column. Where am I going wrong?

 

[Misca]

Can't tell exactly without seeing the case.

Make sure the ranges are locked correctly i.e. the "$" signs are in right places: All the other ranges are completely locked but on the ROWS function only the first row is locked). Enter the formula to your D2 and copy down. If your data is set up exactly as it is in your example the formula seems to work correctly.

 

[jwillits]

Can't tell exactly without seeing the case.

Make sure the ranges are locked correctly i.e. the "$" signs are in right places: All the other ranges are completely locked but on the ROWS function only the first row is locked). Enter the formula to your D2 and copy down. If your data is set up exactly as it is in your example the formula seems to work correctly.

This is the exact formula I am using.

=INDEX($N$2:$N$999,AGGREGATE(15,6,(NOT(ISBLANK($N$2:$N$999))*ROW($N$2:$N$999)/--NOT(ISBLANK($N$2:$N$999))),ROWS($N$2:N2))-1)

All I did was change the C's in your formula to N's because in my spreadsheet the values I want to pull from are in column N. Also, I change the range to 999 as depicted by the pink in my formula. Ideally, I would want the entire column, N:N, but I can just make the range 2:100000 if necessary. The only number changing as i drag down is what is listed in red, as it changes from N2, N3, N4, and so on. Do you see any issues in this formula I am using or what I am doing?

 

[Misca]

Your formula is absolutely correct and works like a charm. But I guess I finally figured out the reason why it's not working on your workbook but works well on mine: You have formulas in your cells. When the cells have formulas they are not blank even if the value shows "". This means the ISBLANK returns FALSE for every cell. The easiest workaround would be to check that the LEN of the cell value is >0. Because this returns TRUE for every row we're looking for instead of every row we're not interested in as was the case with ISBLANK we can get rid of the NOTs as well.

So try

=INDEX($N$2:$N$999,AGGREGATE(15,6,((LEN($N$2:$N$999)>0)*ROW($N$2:$N$999)/--(LEN($N$2:$N$999)>0)),ROWS($N$2:N2))-1)

 

[jwillits]

Your formula is absolutely correct and works like a charm. But I guess I finally figured out the reason why it's not working on your workbook but works well on mine: You have formulas in your cells. When the cells have formulas they are not blank even if the value shows "". This means the ISBLANK returns FALSE for every cell. The easiest workaround would be to check that the LEN of the cell value is >0. Because this returns TRUE for every row we're looking for instead of every row we're not interested in as was the case with ISBLANK we can get rid of the NOTs as well.

So try

=INDEX($N$2:$N$999,AGGREGATE(15,6,((LEN($N$2:$N$999)>0)*ROW($N$2:$N$999)/--(LEN($N$2:$N$999)>0)),ROWS($N$2:N2))-1)

Ah, yep, learned something new. This one works perfect. Thanks very much.