Need a method to collate non-blank cells

[javelin98]

Hello, all. I have a column of cells that contain calculated data, and some of them, by nature of the calculations, are blank. I want to use the results as the source for a Data Validation drop-down box, but without any blank rows in it, so I am trying to find a way that I can create a new column (let's call it Column B) containing only the non-blank results of the first column (Column A), then base the Data Validation source on that.

Is there some kind of algorithm I can use that would scan Column A and select/return the value for the first non-blank cell of Column A in cell B1, then select/return the next non-blank cell in cell B2, etc., until all the non-blank cells are nicely butted up against each other in Column B, ready for use as a Data Validation source? I've come up pretty blank on this one so far, so I appreciate any help anyone can provide.

Thanks,
Andreas

[Domenic]

Try the following...

B1, copied down until you get a #NUM! error:

=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)-ROW($A$1)+1),ROWS($B$1:B1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

[Domenic]

Sorry, I forgot that you wanted the list used as a source for your data validation list. Therefore, try the following instead...

First, enter the following formula in B1 and copy down...

=IF(ROW()-ROW($B$1)+1<=COUNTIF($A$1:$A$100,">0"),INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)-ROW($A$1)+1),ROWS($B$1:B1))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Then, define the following reference...

Insert > Name > Define

Name: List

Refers to:

=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

Lastly, enter the following as your source for your data validation list...

Data > Validation > List > Source:

=List

Hope this helps!

[javelin98]

Hmmm... didn't seem to want to work. That's a heck of a formula; I'll have to disect it and see if I can figure out what the deal is.

Thanks for your help!
Andreas

[Domenic]

If you'd like I can email you a sample. If your email address is not on file, you can send it to me via Private Message.

[javelin98]

So, what does the "9.99999999999999E+307" refer to?

[javelin98]

If you'd like I can email you a sample. If your email address is not on file, you can send it to me via Private Message.

That'd be great!

javelin98 [at] lycos.com

Thanks a lot for your help!
Andreas

[Domenic]

I think I know what the problem is. I assumed that Column A contained numerical values. If it contains text values, the formulas would change as follows...

B1, copied down:

=IF(ROW()-ROW($B$1)+1<=COUNTIF($A$1:$A$100,"<>"),INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)-ROW($A$1)+1),ROWS($B$1:B1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Then, define the following reference...

Insert > Name > Define

Name: List

Refers to:

=Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(2,1/(Sheet1!$B1:$B65535<>"")))

Lastly, enter the following as your source for your data validation list...

Data > Validation > List > Source:

=List

Also, I've emailed you a sample for you to look at. Post back if you need further help.

[javelin98]

Okay, the formula works well at putting the values from the dispersed list into adjacent cells, but for some reason I get the #NUM! error in the rest of the cells in the "collated" list. Here's the formula (which starts in cell BA1 and was pasted downwards into cell BA19):

{=IF(ROW()-ROW($BA$1)+1<=COUNTIF($AZ$1:$AZ$100,"<>"),INDEX($AZ$1:$AZ$100,SMALL(IF($AZ$1:$AZ$100<>"",ROW($AZ$1:$AZ$100)-ROW($AZ$1)+1),ROWS($BA$1:$BA1))),"")}

With the non-collated list occupying cells AZ1 through AZ19.

I'm really not sure what I'm missing here. There's gotta be something really simple that I'm just overlooking.

Thanks,
Andreas

[Domenic]

What type of values do AZ1:AZ19 contain, text or numerical? And are they a result of a formula? If so, can you also provide a sample of the formula you're using?

[javelin98]

They should all be text. The first three cells have a simple formula, based on:

=IF('Step 2b - Secondary Design'!$B$4="","",'Step 2b - Secondary Design'!$B$4)

And the following sixteen are based on the following:

=IF('Step 2a - Primary Design'!$C$3="","",IF('Step 2a - Primary Design'!$C$4>10,"",'Step 2a - Primary Design'!$C$3))

So it's nothing terribly complicated; I included the IF statements primarily to avoid getting a result of 0 if the target cell were empty. Is it the empty brackets causing the problem?

Thanks for your help,
Andreas

[Domenic]

Try the following...

BB1:

=SUMPRODUCT(--(AZ1:AZ100<>""))

BA1, copied down:

=IF(ROW()-ROW($BA$1)+1<=$BB$1,INDEX($AZ$1:$AZ$100,SMALL(IF($AZ$1:$AZ$100<>"",ROW($AZ$1:$AZ$100)-ROW($AZ$1)+1),ROWS($BA$1:$BA1))),"")}

Hope this helps!

[javelin98]

Sweet! That did it! It'll take me days to understand the logic of what you did just there, but it most certainly worked, so I'm not arguing with it. Thanks for all your help, Domenic!