Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF question complicated
Hi there hoping someone can help.
My documents looks something like this. Begin End C this formula will be put in c1 1 1 If(a11,b1<"",b1 else go down the column until you find a number and put it in C1 so that it 2 ***I want to be able to but begin and end time on the same line so I can manipulate the data later. 3 4 2 5 1 6 7 3 8 2 9 10 11 4 Hope this is clear it would need to look like this Begin End C 1 1 2 2 3 4 2 5 1 3 6 7 3 8 2 4 9 10 11 4 Thank you for reading this.... CD |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF question complicated
Assume that the source data posted is within A1:B11
In C1: =COUNTIF($A$1:A1,"<") In D1: =COUNTIF($C$1:C1,C1) In E1, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =IF(D1=1,INDEX(B$1:B$11,MAX(IF(C$1:C$11=C1,ROW(C$1 :C$11)))),"") Copy C1:E1 down to E11. This "pulls" up col B's data (End) and aligns it exactly as sought with col A's data (Begin) in col E. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- wrote in message ... Hi there hoping someone can help. My documents looks something like this. Begin End C this formula will be put in c1 1 1 If(a11,b1<"",b1 else go down the column until you find a number and put it in C1 so that it 2 ***I want to be able to but begin and end time on the same line so I can manipulate the data later. 3 4 2 5 1 6 7 3 8 2 9 10 11 4 Hope this is clear it would need to look like this Begin End C 1 1 2 2 3 4 2 5 1 3 6 7 3 8 2 4 9 10 11 4 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF question complicated
On Jan 22, 8:20*pm, "Max" wrote:
Assume that the source data posted is within A1:B11 In C1: =COUNTIF($A$1:A1,"<") In D1: =COUNTIF($C$1:C1,C1) In E1, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =IF(D1=1,INDEX(B$1:B$11,MAX(IF(C$1:C$11=C1,ROW(C$1 :C$11)))),"") Copy C1:E1 down to E11. This "pulls" up col B's data (End) and aligns it exactly as sought with col A's data (Begin) in col E. -- Max Singaporehttp://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik wrote in message ... Hi there hoping someone can help. My documents looks something like this. * * * Begin * * End * * * * * *C *this formula will be put in c1 1 * * 1 * * * * * * * * * * * * *If(a11,b1<"",b1 else go down the column until you find a number and put it in C1 so that it 2 * * * * * * * * * * * * * * * * ***I want to be able to but begin and end time on the same line so I can manipulate the data later. 3 4 * * * * * * * * * 2 5 * * *1 6 7 * * * * * * * * * 3 8 * * *2 9 10 11 * * * * * * * * *4 Hope this is clear it would need to look like this * * * Begin * * End * * * * * *C 1 * * 1 * * * * * * * * * * * * * * * 2 2 3 4 * * * * * * * * * 2 5 * * *1 * * * * * * * * * * * * * * *3 6 7 * * * * * * * * * 3 8 * * *2 * * * * * * * * * * * * * * *4 9 10 11 * * * * * * * * *4- Hide quoted text - - Show quoted text - Thank you sooo much you are a genius.... It works I didn't thinkt his was possible. Thanks again for your time. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF question complicated
Welcome, and thanks for feeding back.
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- wrote Thank you sooo much you are a genius.... It works I didn't think this was possible. Thanks again for your time. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF question complicated
Hello,
Array-enter into C1: =IF(ISBLANK(A1),"",INDEX(B2:B$65536,MATCH(FALSE,IS BLANK(B2:B$65536), 0))) Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated FV Question | Excel Worksheet Functions | |||
Complicated question, for me anyway! :) | Excel Worksheet Functions | |||
Complicated question | Excel Discussion (Misc queries) | |||
Complicated Question | Excel Worksheet Functions | |||
Complicated question... | Excel Worksheet Functions |