Matching numbers in an Array and returning values for matched numb
 

How do I get excel to find a multiple of a specific number in an array or
column and return a defined value for which occurance of the number?

thks TW

Matching numbers in an Array and returning values for matched numb
 

Can you provide a small sample of the data, along with the expected
results?

In article ,
Tiger wrote:

How do I get excel to find a multiple of a specific number in an array or
column and return a defined value for which occurance of the number?

thks TW

Matching numbers in an Array and returning values for matched
 

this is a sample

2007-03-27 04:05:40 1 4:05
2007-03-27 04:21:40 1 0:16
2007-03-27 04:22:40 - -
2007-03-27 04:23:40 - -
2007-03-27 04:24:40 - -
2007-03-27 04:25:40 - -
2007-03-27 04:26:40 - -
2007-03-27 04:27:40 - -
2007-03-27 04:28:40 - -
2007-03-27 04:29:40 - -
2007-03-27 04:30:40 - -
2007-03-27 04:34:40 1 0:04
2007-03-27 04:35:40 - -
2007-03-27 04:36:40 - -

when the number 1 is found return, 4:05,0:16 etc, but have these grouped
togther at the top of the column, as I have a varing array of numbers,
cheersTW
"Domenic" wrote:

Can you provide a small sample of the data, along with the expected
results?

In article ,
Tiger wrote:

How do I get excel to find a multiple of a specific number in an array or
column and return a defined value for which occurance of the number?

thks TW

Matching numbers in an Array and returning values for matched
 

It's unclear whether the date and time is contained in one column or
two. Therefore, try the following...

If Column A contains the date, Column B contains the time, and Column C
contains 1:

D1:

=IF(C1=1,B1,"")

D2, copied down:

=IF(C2=1,B2-B1,"")

If Column A contains both the date and time, and Column B contains 1:

C1:

=IF(B1=1,A1-INT(A1),"")

C2, copied down:

=IF(B2=1,(A2-INT(A2))-(A1-INT(A1)),"")

Hope this helps!

In article ,
Tiger wrote:

this is a sample

2007-03-27 04:05:40 1 4:05
2007-03-27 04:21:40 1 0:16
2007-03-27 04:22:40 - -
2007-03-27 04:23:40 - -
2007-03-27 04:24:40 - -
2007-03-27 04:25:40 - -
2007-03-27 04:26:40 - -
2007-03-27 04:27:40 - -
2007-03-27 04:28:40 - -
2007-03-27 04:29:40 - -
2007-03-27 04:30:40 - -
2007-03-27 04:34:40 1 0:04
2007-03-27 04:35:40 - -
2007-03-27 04:36:40 - -

when the number 1 is found return, 4:05,0:16 etc, but have these grouped
togther at the top of the column, as I have a varing array of numbers,
cheersTW

Matching numbers in an Array and returning values for matched
 

Thnks Domenic,
COLUMN 1 Column 2 Column 3
2007-03-27 04:05:40 1 4:05
2007-03-27 04:21:40 1 0:16
2007-03-27 04:22:40 - -
2007-03-27 04:23:40 - -
2007-03-27 04:24:40 - -
2007-03-27 04:30:40 - -
2007-03-27 04:34:40 1 0:04
2007-03-27 04:35:40 - -
2007-03-27 04:36:40 - -


the columes are already seperated, just a bit hard to see, I am using a
program which returns time values into excel as an array where the length of
column values varies dependend upon operating houors, it also pastes a "1" at
random intervals in the next column, where there is a "1", I want to find
each occurnace and return the value in column 2, hoever need to do this using
an array formula so I end up with this at column 4


4:05
0:16
0:04

i.e no spaces.

cheers

"Domenic" wrote:

It's unclear whether the date and time is contained in one column or
two. Therefore, try the following...

If Column A contains the date, Column B contains the time, and Column C
contains 1:

D1:

=IF(C1=1,B1,"")

D2, copied down:

=IF(C2=1,B2-B1,"")

If Column A contains both the date and time, and Column B contains 1:

C1:

=IF(B1=1,A1-INT(A1),"")

C2, copied down:

=IF(B2=1,(A2-INT(A2))-(A1-INT(A1)),"")

Hope this helps!

In article ,
Tiger wrote:

this is a sample
column 1
2007-03-27 04:05:40 1 4:05
2007-03-27 04:21:40 1 0:16
2007-03-27 04:22:40 - -
2007-03-27 04:23:40 - -
2007-03-27 04:24:40 - -
2007-03-27 04:25:40 - -
2007-03-27 04:26:40 - -
2007-03-27 04:27:40 - -
2007-03-27 04:28:40 - -
2007-03-27 04:29:40 - -
2007-03-27 04:30:40 - -
2007-03-27 04:34:40 1 0:04
2007-03-27 04:35:40 - -
2007-03-27 04:36:40 - -

when the number 1 is found return, 4:05,0:16 etc, but have these grouped
togther at the top of the column, as I have a varing array of numbers,
cheersTW

Matching numbers in an Array and returning values for matched
 

You're up kinda late, aren't ya?

<g

Biff

"Domenic" wrote in message
...
It's unclear whether the date and time is contained in one column or
two. Therefore, try the following...

If Column A contains the date, Column B contains the time, and Column C
contains 1:

D1:

=IF(C1=1,B1,"")

D2, copied down:

=IF(C2=1,B2-B1,"")

If Column A contains both the date and time, and Column B contains 1:

C1:

=IF(B1=1,A1-INT(A1),"")

C2, copied down:

=IF(B2=1,(A2-INT(A2))-(A1-INT(A1)),"")

Hope this helps!

In article ,
Tiger wrote:

this is a sample

2007-03-27 04:05:40 1 4:05
2007-03-27 04:21:40 1 0:16
2007-03-27 04:22:40 - -
2007-03-27 04:23:40 - -
2007-03-27 04:24:40 - -
2007-03-27 04:25:40 - -
2007-03-27 04:26:40 - -
2007-03-27 04:27:40 - -
2007-03-27 04:28:40 - -
2007-03-27 04:29:40 - -
2007-03-27 04:30:40 - -
2007-03-27 04:34:40 1 0:04
2007-03-27 04:35:40 - -
2007-03-27 04:36:40 - -

when the number 1 is found return, 4:05,0:16 etc, but have these grouped
togther at the top of the column, as I have a varing array of numbers,
cheersTW

Matching numbers in an Array and returning values for matched
 

Yup! Way to late for my liking... :)

In article ,
"T. Valko" wrote:

You're up kinda late, aren't ya?

<g

Biff

Matching numbers in an Array and returning values for matched
 

Let's assume the following...

A2:A15 contains the date

B2:B15 contains the time

C2:C15 contains the 1

If B1 can remain empty, try...

D2:

=COUNT(C2:C15)

E2, copied down:

=IF(ROWS($E$2:E2)<=$D$2,INDEX($B$2:$B$15-$B$1:$B$14,SMALL(IF($C$2:$C$15=1
,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($E$2:E2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

If B1 contains a column header and can't remain empty, try...

D2:

=COUNT(C2:C15)

E2, copied down:

=IF(ROWS($E$2:E2)<=$D$2,SUM(N(OFFSET($B$2:$B$15,SM ALL(IF($C$2:$C$15=1,ROW
($C$2:$C$15)-ROW($C$2)+1),ROWS($E$2:E2))-{1,2},0,1))*{1,-1}),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Tiger wrote:

Thnks Domenic,
COLUMN 1 Column 2 Column 3
2007-03-27 04:05:40 1 4:05
2007-03-27 04:21:40 1 0:16
2007-03-27 04:22:40 - -
2007-03-27 04:23:40 - -
2007-03-27 04:24:40 - -
2007-03-27 04:30:40 - -
2007-03-27 04:34:40 1 0:04
2007-03-27 04:35:40 - -
2007-03-27 04:36:40 - -


the columes are already seperated, just a bit hard to see, I am using a
program which returns time values into excel as an array where the length of
column values varies dependend upon operating houors, it also pastes a "1" at
random intervals in the next column, where there is a "1", I want to find
each occurnace and return the value in column 2, hoever need to do this using
an array formula so I end up with this at column 4


4:05
0:16
0:04

i.e no spaces.

cheers

Matching numbers in an Array and returning values for matched
 

Thanks DOM, pretty close giving it a try,

"Domenic" wrote:

Let's assume the following...

A2:A15 contains the date

B2:B15 contains the time

C2:C15 contains the 1

If B1 can remain empty, try...

D2:

=COUNT(C2:C15)

E2, copied down:

=IF(ROWS($E$2:E2)<=$D$2,INDEX($B$2:$B$15-$B$1:$B$14,SMALL(IF($C$2:$C$15=1
,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($E$2:E2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

If B1 contains a column header and can't remain empty, try...

D2:

=COUNT(C2:C15)

E2, copied down:

=IF(ROWS($E$2:E2)<=$D$2,SUM(N(OFFSET($B$2:$B$15,SM ALL(IF($C$2:$C$15=1,ROW
($C$2:$C$15)-ROW($C$2)+1),ROWS($E$2:E2))-{1,2},0,1))*{1,-1}),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
Tiger wrote:

Thnks Domenic,
COLUMN 1 Column 2 Column 3
2007-03-27 04:05:40 1 4:05
2007-03-27 04:21:40 1 0:16
2007-03-27 04:22:40 - -
2007-03-27 04:23:40 - -
2007-03-27 04:24:40 - -
2007-03-27 04:30:40 - -
2007-03-27 04:34:40 1 0:04
2007-03-27 04:35:40 - -
2007-03-27 04:36:40 - -


the columes are already seperated, just a bit hard to see, I am using a
program which returns time values into excel as an array where the length of
column values varies dependend upon operating houors, it also pastes a "1" at
random intervals in the next column, where there is a "1", I want to find
each occurnace and return the value in column 2, hoever need to do this using
an array formula so I end up with this at column 4


4:05
0:16
0:04

i.e no spaces.

cheers