• 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.

Lookup Second last date for ID with multiple entries

Status
Not open for further replies.

shahzad_afzal

New Member
Dear;
See attached file; I need to sort this by activity date... For the next instance of the same ID; I need to get the date from activity end of the last instance...
Please review and support...
 

Attachments

  • Test Case.xlsx
    16.2 KB · Views: 3
can you explain the logic of getting Activity End ? how you get Activity end = 15/3/2020 5:13:53 am for first instance of ABC2000078276, New Addition, and how come the next instance become Upgrade / Downgrade

Once you explain clearly, you can use Power Query to transform to the format
 
Maybe,

In "Sheet 1" M4 formula copied down :

=IFERROR(LOOKUP(9^9,$Q$4:$Q$51/($N$4:$N$51=H4)/($O$4:$O$51="Active")),"not found")

Regards
 
Last edited:
Hi,

or
=IFERROR(INDEX($Q$4:$Q$51,MATCH(2, 1/($O$4:$O$51="Active")/($N$4:$N$51=H4))),"not found")
CTRL-SHIFT-ENTER copy down

Regards
 

Attachments

  • Test Case_Chris1.xlsx
    16.3 KB · Views: 5
can you explain the logic of getting Activity End ? how you get Activity end = 15/3/2020 5:13:53 am for first instance of ABC2000078276, New Addition, and how come the next instance become Upgrade / Downgrade

Once you explain clearly, you can use Power Query to transform to the format
Dear;
Activity End is entered manually... Whenever any change occurs; e.g.... A new customer joined, "New Activity"; then customer upgrade or downgrade services; customer went into lock for some time or customer terminated... Each activity has a start and end date...
Change of status; I will manually add date from the network log in the activity end column, after this; will add a new row which is date sorted and add next status of the SAN ID...
This row will get date + 1 sec from the previous activity end date of the same SAN...

Hope this clarifies...
 
Hi,

or
=IFERROR(INDEX($Q$4:$Q$51,MATCH(2, 1/($O$4:$O$51="Active")/($N$4:$N$51=H4))),"not found")
CTRL-SHIFT-ENTER copy down

Regards
Hi;
Thanks for sharing...
This only capturing if last status is active... Upgrade / Downgrade can also be last status or even lock can be last status before going to termination...
 
Hi, don't get you, please give us a final output of the correct activity end date that you want, or you just create another few table to replace "Active:" to other status (Upgrade / Downgrade , lock)
 
Hi, don't get you, please give us a final output of the correct activity end date that you want, or you just create another few table to replace "Active:" to other status (Upgrade / Downgrade , lock)
Hi; Activity end date is entered manually... Activity 1: New Addition, when customer like to upgrade / downgrade, lock, terminate; we will first close the first activity manually by adding date in activity date column... From this date; we need to derive the next date time of next activity start... If customer next activity is locked; system will get date from last instance and add one sec and will start customer activity which is locked...
 
Status
Not open for further replies.
Back
Top