extract non-zero values in range 1

[jges]

I have a table of values that are the result of calculations. The results are of the form:
[box]0.064574031 0 0 0 0
0.007874031 0.080874031 0 0 0
0 0.024174031 0.097174031 0 0
0 0 0.040474031 0 0
0 0 0 0.056774031 0
0 0 0 7.40315E-05 0.073074031
0 0 0 0 0.016374031
[/box

 

[jges]

oops, hit post instead of preview

The data is of the form:

0.064574031	0	        0	        0	        0
0.007874031	0.080874031	0	        0	        0
0	        0.024174031	0.097174031	0	        0
0	        0	        0.040474031	0	        0
0	        0	        0	        0.056774031	0
0	        0	        0	        7.40315E-05	0.073074031
0	        0	        0	        0	        0.016374031

I'd like to extract the non-zero values into a single column. Take the non-zero values in column 2 and append them to the non-zero values in column 1, etc etc

As the inputs change, there may be more non-zero values in each column, but the overall form (values near the diagonal) will remain the same.

 

[SkipVought]

what application?

how do you need the results?

Skip,

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

 

[kjv1611]

Also, is there any correlation to the rows/columns, or is ever instance its own record?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[jges]

Sorry, application is Excel (2010).

I'd like to get all the non-zero values column by column to create a single column of results. I have another formula that I need to apply to and then create a graph of the final results.

 

[SkipVought]

Take the non-zero values in column 2 and append them to the non-zero values in column 1, etc etc

APPEND?

So this is a non-arithmetic problem?

Is there only 5 columns?

Here's a stab: In an adjacent column in row1...
[tt]
=IF(A1=0,"",A1)&IF(B1=0,"",B1)&IF(C1=0,"",C1)&IF(D1=0,"",D1)&IF(E1=0,"",E1)
[/tt]
my results
[tt]
0.064574031
0.0078740310.080874031
0.0241740310.097174031
0.040474031
0.056774031
0.00007403150.073074031
0.016374031
[/tt]


Skip,

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

 

[jges]

Append may not be the proper term, but it is the closest I could come up with.
My workbook currently has 22 columns and 28 rows of such data. The output I'm looking for would be:
0.064574031
0.007874031
0.080874031
0.024174031
0.097174031
0.040474031
.
.
.
0.016374031

I plan on using the non-zero values in another formula then graphing the results.

kjv1611: I'm designing a mechanism, each value represents the distance between 2 points that I'm interested in; a zero value means the 2 points won't interact for the given inputs.

 

[SkipVought]

Is this a one time exersize or will it be repeated?

Does the order of the data matter?

Skip,

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

 

[SkipVought]

Take a look at faq68-5287.

I modified your example to look like this...
[tt]
seq Head1 Head2 Head3 Head4 Head5
a 0.064574031 0 0 0 0
b 0.007874031 0.080874031 0 0 0
c 0 0.024174031 0.097174031 0 0
d 0 0 0.040474031 0 0
e 0 0 0 0.056774031 0
f 0 0 0 7.40E-05 0.073074031
g 0 0 0 0 0.016374031
[/tt]
Then use the faq68-5287 to generate this...
[tt]
Row Column Value
a Head1 0.064574031
a Head2 0
a Head3 0
a Head4 0
a Head5 0
b Head1 0.007874031
b Head2 0.080874031
b Head3 0
b Head4 0
b Head5 0
c Head1 0
c Head2 0.024174031
c Head3 0.097174031
c Head4 0
c Head5 0
d Head1 0
d Head2 0
d Head3 0.040474031
d Head4 0
d Head5 0
e Head1 0
e Head2 0
e Head3 0
e Head4 0.056774031
e Head5 0
f Head1 0
f Head2 0
f Head3 0
f Head4 7.40315E-05
f Head5 0.073074031
g Head1 0
g Head2 0
g Head3 0
g Head4 0
g Head5 0.016374031
[/tt]
and then just filter the ZEROS and DELETE

Skip,

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

 

[jges]

Order of the data is not critically important.

The pivot table looks promising, but in step 5 of the FAQ, the [Layout...] button is greyed out for me. I added the row and column headers per your example. When selecting the range for the pivot table wizard, I selected all the data and headers. I chose the zero page fields option (I also tried one page field, but [Layout...] was still unavailable). Where did I go wrong?

 

[SkipVought]

the [Layout...] button is greyed out for me.

[Layout...] was just to give you a warm fuzzy that you are in the right place.

While in the [Layout...] you are to...

Drag the Row and Column buttons OFF the Layout -- [OK] -- [Finish]

Skip,

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

 

[jges]

I never get the option to change the layout.

I get to the wizard dialog:
PivotTable and PivotChart Wizard - step 3 of 3
which asks "Where do you want to put the pivot table report?"
I can choose new sheet or existing sheet. There are buttons along the bottom: Layout, Options, Cancel, Back, Next, and Finish; of which only Cancel, Back, and Finish are enabled.

 

[SkipVought]

CHOOSE a place to put the PT and then [FINISH]

Skip,

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

 

[jges]

Got it.
Thanks for all the help, Skip!