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
€¦

"MB" skrev i en meddelelse
...
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
.




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:"&COLU MNS($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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Leo Heuser" wrote:

"MB" skrev i en meddelelse
...
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
.




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:"&COLU MNS($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.


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!

"MB" skrev i en meddelelse
...



Thank you for the help!

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".

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


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!

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.

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

"MB" skrev i en meddelelse
...
Ones again thank you!


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


You're welcome. Glad you got it to work :-)

Leo Heuser