Matching numbers in an Array and returning values for matched numb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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!
 
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
 
You're up kinda late, aren't ya?

<g>

Biff

Domenic said:
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!
Click to expand...
 
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,SMALL(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!
 
Thanks DOM, pretty close giving it a try,

Domenic said:
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,SMALL(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!
Click to expand...
 
Back
Top