make a list from a table

M

MB

Is it possible to make a list that takes the values from a list if the value
is over 0? I have a table with dates and every day you can list 14 different
moments. Now I want to list those days and moments where the value is over 0.

The table
Date 1.1 2.1 3.1 …
Moment1 10 0 0
Moment2 0 8 8
Moment3 0 0 0
Moment4 8 0 10
…

The new list
1.1 Moment1 10
1.1 Moment4 8
2.1 Moment2 8
3.1 Moment2 8
3.1 Moment4 10
…
 
L

Leo Heuser

MB said:
Is it possible to make a list that takes the values from a list if the
value
is over 0? I have a table with dates and every day you can list 14
different
moments. Now I want to list those days and moments where the value is over
0.

The table
Date 1.1 2.1 3.1 .
Moment1 10 0 0
Moment2 0 8 8
Moment3 0 0 0
Moment4 8 0 10
.

The new list
1.1 Moment1 10
1.1 Moment4 8
2.1 Moment2 8
3.1 Moment2 8
3.1 Moment4 10
.
Click to expand...



MB

Here's one way to do it:
All formulae entered as one line.

I have put your table in A1:D5. Adjust all cell references
to mirror your setup.

In e.g. L2 this formula:

=OFFSET($B$1,, COLUMNS($B$1:$D$1)-SUMPRODUCT(((ROW()-ROW($L$2)+1)<=
COUNTIF(OFFSET($B$2:$B$5,,,,ROW(INDIRECT("1:"&COLUMNS($B$1:$D$1)))),"<>"&0))+0))


In M2 this array formula:

=INDEX($A$2:$A$5, SMALL(IF(OFFSET($B$2:$B$5,,MATCH(L2,$B$1:$D$1,0)-1)<>0,
ROW($B$2:$B$5)-ROW($B$2)+1),COUNTIF($L$2:L2,L2)))

To be entered with <Shift><Ctrl><Enter>, also if edited later.


In N2 this formula:

=INDEX($B$2:$D$5,MATCH(M2,$A$2:$A$5,0),MATCH(L2,$B$1:$D$1,0))


If you start in a cell different from L2, replace $L$2 and L2 by the
new startcell's reference.

Select L2:N2 and copy down with the fill handle (the little square in the
lower right corner of the cell)
Format column L as date.
 
M

MB

Leo Heuser said:
MB

Here's one way to do it:
All formulae entered as one line.

I have put your table in A1:D5. Adjust all cell references
to mirror your setup.

In e.g. L2 this formula:

=OFFSET($B$1,, COLUMNS($B$1:$D$1)-SUMPRODUCT(((ROW()-ROW($L$2)+1)<=
COUNTIF(OFFSET($B$2:$B$5,,,,ROW(INDIRECT("1:"&COLUMNS($B$1:$D$1)))),"<>"&0))+0))


In M2 this array formula:

=INDEX($A$2:$A$5, SMALL(IF(OFFSET($B$2:$B$5,,MATCH(L2,$B$1:$D$1,0)-1)<>0,
ROW($B$2:$B$5)-ROW($B$2)+1),COUNTIF($L$2:L2,L2)))

To be entered with <Shift><Ctrl><Enter>, also if edited later.


In N2 this formula:

=INDEX($B$2:$D$5,MATCH(M2,$A$2:$A$5,0),MATCH(L2,$B$1:$D$1,0))


If you start in a cell different from L2, replace $L$2 and L2 by the
new startcell's reference.
Click to expand...


Thank you for the help!

The first step(L2) works perfectly, but the second step(N2) does not work
and then of course not the third one(M2) as well. I do not understand what
you meen by "entered with <Shift><Ctrl><Enter>".

One problem is that it always list Moment1 even if it is 0.

If I use the same exampel it looks like this:

1.1.2006 Moment1 10
1.1.2006 #NUM! #NUM!
2.1.2006 Moment3 #REF!
3.1.2006 Moment1 #REF!
3.1.2006 #VALUE! #VALUE!

If I put the 1.1 Moment1 as 0 it looks like this:

1.1.2006 Moment1 0
2.1.2006 Moment1 #REF!
3.1.2006 Moment3 #REF!
3.1.2006 #NUM! #NUM!
 
L

Leo Heuser

MB said:
Thank you for the help!
Click to expand...

You're welcome.
The first step(L2) works perfectly, but the second step(N2) does not work
and then of course not the third one(M2) as well. I do not understand what
you meen by "entered with <Shift><Ctrl><Enter>".
Click to expand...

I take it, that with the second step(N2) you mean (M2) etc.

"entered with <Shift><Ctrl><Enter>" means:
Press the <Shift> key and the <Ctrl> key together.
While holding them down, press <Enter>
Release all three keys.
If you have done it correctly, Excel will display the formula
in the formula bar enclosed in braces { } Don't enter these
braces yourself. They are Excel's way of showing, that the
formula is an array formula.

Not doing it correctly will cause the problems you describe.
However, I can't replicate the #REF! error. Please check
the formula in N2 again and check all references.

Let me know how it works.

Leo Heuser
 
M

MB

Ones again thank you!


I have implemented the formulas in my real excel program now and it works
perfectly.

I take it, that with the second step(N2) you mean (M2) etc. Yes

Not doing it correctly will cause the problems you describe.
However, I can't replicate the #REF! error. Please check
the formula in N2 again and check all references.
Click to expand...

You where right, I had a error in my formula.
 
L

Leo Heuser

MB said:
Ones again thank you!


I have implemented the formulas in my real excel program now and it works
perfectly.
Click to expand...
You're welcome. Glad you got it to work :)

Leo Heuser
 
Top