List of values from multiple columns

[chasethewind]

How do I create a sequential list from the values shown? The NA's will have generate a number as well if a condition is met elsewhere. I need to keep the NA's (just because).

Thanks Cory

Column A Column B Column C
3 8 6
4 14 9
9 32 12
23 #n/a 15
14 #n/a #n/a
#n/a #n/a #n/a
#n/a #n/a #n/a
#n/a #n/a #n/a

 

[chasethewind]

No macros either.

 

[chasethewind]

Sorry... Excel 2007

 

[Scott24x7]

What do you mean by "A sequential list"?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Hi,

'Just because' is not a professional valid reason!

Please state a valid reason!

Skip,

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

 

[Gavona]

Copy to a single column then sort it?
In detail:

Click to select cell B1
Ctrl-Shft-DownArrow to select all the cells n the column
Ctrl-x to cut to the clipboard

Click to select cell A1
Ctrl-DownArrow
DownArrow
Ctrl-v to paste

Click to select cell C1
Ctrl-Shft-DownArrow to select all the cells n the column
Ctrl-x to cut to the clipboard
Click to select cell A1
Ctrl-DownArrow
DownArrow
Ctrl-v to paste

Click in a single cell in column A
Data, Sort

Gavin

 

[chasethewind]

Sorry for the confusion. I have looked everywhere and cannot find a solution to my problem. This would seem like an easy task (possible already a command in Excel).

No sorting.

3
4
9
23
14
8
14
32
6
9
12
15
...

 

[SkipVought]

No. Actually, Excel features works on TABLE structures, where a COLUMN of data ar values of a certain kind that would be DIFFERENT than data in another column.

Skip,

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

 

[chasethewind]

I think "Skip" helped me with formula and it works great to sort the data, but "Small" is what needs changed now. I do not know what to use to not sort from smallest to largest.


=IF(ROW(Q5)>COUNT(Q$6:S$194),NA(),SMALL(IF(ISNUMBER(Q$6:S$194),Q$6:S$194,""),ROW(Q5)))

Thanks

 

[SkipVought]

Data > Sort -- DESCENDING.

I do not understand the problem!

Skip,

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

 

[SkipVought]

SORT is not performed with a formula!

Skip,

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

 

[SkipVought]

Here's a set of formulae that work...
[tt]
E2: =IF(E1+1>ROWS(SourceData),1,E1+1)
F2: =IF(E2=1,F1+1,F1)
G2: =INDEX(SourceData,E2,F2)
[/tt]
result
[tt]
1 1 3
2 1 4
3 1 9
4 1 23
5 1 14
6 1 #N/A
7 1 #N/A
8 1 #N/A
1 2 8
2 2 14
3 2 32
4 2 #N/A
5 2 #N/A
6 2 #N/A
7 2 #N/A
8 2 #N/A
1 3 6
2 3 9
3 3 12
4 3 15
5 3 #N/A
6 3 #N/A
7 3 #N/A
8 3 #N/A
[/tt]
COPY column G. Edit > Paste Special -- VALUES.

Then sort column G descending.




Skip,

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

 

[chasethewind]

Patience,

I will try to expain the situation differently

I have 3 column with numbers in them. I want to take the column A values place them in (lets say in column D), then I want to take the values from Column B and append them to the results (values from column A) in column D, take the values from Column C and append them again to column D.

Column A - 1(A1),3,7,2,4
Column B - 2(B1),5,4
Column C - 6(C1),7,9,10

Results in Column D - 1(D1),3,7,2,4,2,5,4,6,7,9,10


(PLACE IN D1)

=IF(ROW(A1)>COUNT(A$1:C5),NA(),SMALL(IF(ISNUMBER(A$1:C5),A$1:C5,""),ROW(A1))) --- CNTL SHIFT ENTER

In the above formula you provided (thank you) the "small" is sorting from smallest to largest. That works great for the use I wanted it for. Now I just want to place the values "as is", with no sorting. I tried to just delete "Small" from the formula, but I recieve a formula error. Give it a try you will see what I mean.

 

[chasethewind]

Fantastic!

I must have been typing while you where posting. Your formulas are exactly what is needed. Thank you for helping Skip.

Cory,