• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populate column based on 2 other columns

R

rdepuydt

New Member
Is there a way to populate cells for the below? I need the third column to populate with the time associated with the first column. So when that first column changes,
it will pick up the time that is in the second column and put that value in the third column.


1047-CORY DEMPSEY44055 Fill with 11:54 AM
1047-CORY DEMPSEY44055 Fill with 11:54 AM
1047-CORY DEMPSEY4405511:54 AMFill with 11:54 AM
1047-CORY DEMPSEY44055 Fill with 11:54 AM
1047-CORY DEMPSEY44055 Fill with 11:54 AM
1047-CORY DEMPSEY44055 Fill with 11:54 AM
1047-CORY DEMPSEY44055 Fill with 11:54 AM
1047-CORY DEMPSEY44055 Fill with 11:54 AM
1047-CORY DEMPSEY44056 Fil with 10:35 AM
1047-CORY DEMPSEY44056 Fil with 10:35 AM
1047-CORY DEMPSEY44056 Fil with 10:35 AM
1047-CORY DEMPSEY4405610:35 AMFil with 10:35 AM
1047-CORY DEMPSEY44056 Fil with 10:35 AM
1047-CORY DEMPSEY44056 Fil with 10:35 AM
1047-CORY DEMPSEY44056 Fil with 10:35 AM
1047-CORY DEMPSEY44056 Fil with 10:35 AM
 
Hi,

Maybe try =MOD(INDEX($P$11:$P$658,MATCH(C11&TRUE,$C$11:$C$658&$O$11:$O$658,0)),1)
 

Attachments

  • Copy of Copy for Chandoo.xlsx
    516.5 KB · Views: 6
GraH - Guido said:
Hi,

Maybe try =MOD(INDEX($P$11:$P$658,MATCH(C11&TRUE,$C$11:$C$658&$O$11:$O$658,0)),1)
Click to expand...

Why am I not able to duplicate this formula into another range of cells? I'm trying to duplicate this formula into column T and come up with #VALUE
 

Attachments

  • Chandoo copy.xlsx
    486.2 KB · Views: 5
Hi,
Did you run the formula evaluation. Often that reveals at which point the formula fails.
I don't have Excel available at the moment, so I cannot look in your file. Notice Mod was used in the previous formula to return only the time part from the datetime value. What if you delete that function at it's arguments?
 
GraH - Guido said:
Hi,
Did you run the formula evaluation. Often that reveals at which point the formula fails.
I don't have Excel available at the moment, so I cannot look in your file. Notice Mod was used in the previous formula to return only the time part from the datetime value. What if you delete that function at it's arguments?
Click to expand...
I did both of these things and can't find anything. When you have access to Excel, could you please take a look?
 
Hi,
When I opened your file I got =MOD(INDEX($P$11:$P$658;MATCH(C11&TRUE;@$C$11:$C$658&@$O$11:$O$658;0));1)
Remove those "@". I reckon you are on Office 365. It has to do with implicit intersection behaviour that has changed with the new calculation engine. I just do not understand why it ended up in the first place in this formula. Dynamic Array Functions and formula are relatively new for me as is the new behaviour of the calculation engine.
 
You must log in or register to reply here.
Back
Top