make a list from a table

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" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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:"&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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Leo Heuser" wrote:

> "MB" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > 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:"&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.


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" <[email protected]> skrev i en meddelelse
news:[email protected]...
>
>
>
> 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" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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