| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
| How do I sum up only the numbers in cells containing text and numb | Excel Discussion (Misc queries) | |||
| returning all matching values in column A that have the same value for columnB | Excel Worksheet Functions | |||
| Returning a Value by Matching Two Columns of Data | Excel Worksheet Functions | |||
| Matching Values from an Array | Excel Worksheet Functions | |||
Hybrid Mode
