Create a list of values from multiple columns 1

[chasethewind]

Hello, I have been trying to create a single list (sorted lowest to highest) of numeric values from 3 different columns and place it in a single column. I do not want to use any macros (VBA). Is this even possible. Place values from B,C,D into A. B,C,D might change so is it possible to automatically add those to A?

A B C D
1 1 6 8
2 2 7 12
3 3 19
4 4
5 5
6
7
8
12
19

 

[macropod]

Hi chasethewind,

Try: =SMALL(B$1$5,ROW())
in A1 and copied down as far as needed.


Cheers
[MS MVP - Word]

 

[chasethewind]

Ok, I have it working, but In my formulas I have some cells that have NA() (on purpose) and it only recognizes these cells. Any suggestions?

 

[macropod]

Hi chasethewind,

Try: =SMALL(IF(ISNUMBER(B$1$5),B$1$5,""),ROW())
as an array formula (input with Ctrl-Shift-Enter).

Note that this will exclude any text cells and other error types too.


Cheers
[MS MVP - Word]

 

[chasethewind]

It refuses to work. I have Column B,C,D and needs to show up in "A". I double checked the formula syntax and it doesn't seem to like it. I tried to switch the "True" and "False" result, but that didn't work. A formula is producing the #N/A values, otherwise it would result in incorrect values. Thank you helping me, any additional input would be appriciated.

Thanks,

Cory

A B C D
1 1 1 14 37
2 2 2 15 38
3 3 3 16 39
4 4 4 #N/A 40
5 12 12 #N/A 50
6 14 #N/A #N/A 60
7 15 #N/A #N/A #N/A
8 16 #N/A #N/A #N/A
9 37 etc... #N/A #N/A
10 38 #N/A etc...
11 39 #N/A
12 40 etc...
13 50
14 60

 

[macropod]

Hi Cory,

It works for me. With the data in your last post, extending the formula's range to D12 returns:
1
2
3
4
12
14
15
16
37
38
39
40
50
60
#NUM!
#NUM!
etc

I can't see how the formula could be generating #N/A results. If the 'numbers' were actually text, I'd expect #NUM! and, if the formula wasn't input as an array formula, you'd be getting #VALUE!.


Cheers
[MS MVP - Word]

 

[SkipVought]

WHY, "In my formulas I have some cells that have NA() (on purpose?"

What is your purpose for forcing an NA error?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chasethewind]

Yes I do get the same results. I thought it wasn't working because of the errors. I guess I will try to deal with the #nums . Thanks for all the help. I couldn't even do this before, so coodo's to progress.

The n/a error is not recognized in my chart.

Guess I will ask. Is there a way to not have a chart recognize any errors? If so all problems are solved.

 

[macropod]

Hi Cory,

You can eliminate the #NUM! results by complicating the formula still further:
=IF(ROW()>COUNT(B$1$12),"",SMALL(IF(ISNUMBER(B$1$12),B$1$12,""),ROW()))
However, it would be better if you could eliminate the #NA expressions. As Skip asked, why are you putting them there in the first place?


Cheers
[MS MVP - Word]

 

[chasethewind]

I placed the N/A there because my chart would recognize "" as a zero value. If I placed a N/A it would not. I will monkey with it some more.

Thanks,

My technical level in this stuff >>> "Script Baby

 

[SkipVought]

The option in Tools > Chart is to plot EMPTY cells as ZERO or not plotted.

Unfortumately a FORMULA that returns "" is not EMPTY.

I'd suggest using a query result to plot, rather than using formulas, if this is the dilema.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

I've pink-starred tools>options>chart because it's an option too few people know about, and too many of us (me included) have wasted too many hours jumping through hoops to avoid mysterious interpretation of blank cells as zero in charts.

Perhaps someone in microsoft wishes they'd made the default "ignore" instead of "treat as zero" - who knows.

Thanks for a useful tek-tips card-index entry!