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

Discount to be provided on second entry if made from opposite direction within 24 Hours

Ram Mher

Member
Dear Friends

Please find the attached Sheet and provide the solution.

Thanks in Advance
Regards
rammher
 

Attachments

  • Query.xlsx
    9.2 KB · Views: 7

Thanks a lot for your nice support.

Please find the data sheet and calculate formula in this sheet.
Discount to be provided 50% and final amount should be rounded to nearest 5.


Ur cooperation is appreciable.
 

Attachments

  • RecordLevelTransactionReportChild (3).xlsx
    34.8 KB · Views: 7
Uni
Thanks a lot for your nice support.

Please find the data sheet and calculate formula in this sheet.
Discount to be provided 50% and final amount should be rounded to nearest 5.


Ur cooperation is appreciable.
uniques id in data sheet is Hex Tag no
 
I
Why does this thread make me feel like an employee being given instructions by my boss…
Dear friend
i am extremely sorry and it happened due to my lack of knowledge in writing the mail.

Pleasr forgive me and help me out to find out the solution.
 
Yes, I'll try to get you an answer for you to test.
When I get round to it.
Others my want to jump in.
 
Hi ,

I have not understood anything ; probably if you can explain in detail , I might be able to help.

1. In the uploaded workbook , how many columns are relevant to the problem ?

When you upload a workbook , if a column / row of data is irrelevant to the problem you want solved , please hide it.

2. I have checked out your latest upload with an earlier one , and some of the highlighted cases do not seem to follow the earlier specified logic.

Can you clarify ?

Examples are rows 19 , 53 , 61 , 65 , 77 and 436.

Narayan
 
Yes, I'll try to get you an answer for you to test.
When I get round to it.
Others my want to jump in.

Dear fiend

At the very outset, i am very thankful to you for your obligation.

please help me out in the attached sheet as now i need result in true/false only if second entry seen within 24 hours from opposite direction.
 

Attachments

  • Test Sheet.xlsx
    12.7 KB · Views: 1
Dear
Hi ,

I have not understood anything ; probably if you can explain in detail , I might be able to help.

1. In the uploaded workbook , how many columns are relevant to the problem ?

When you upload a workbook , if a column / row of data is irrelevant to the problem you want solved , please hide it.

2. I have checked out your latest upload with an earlier one , and some of the highlighted cases do not seem to follow the earlier specified logic.

Can you clarify ?

Examples are rows 19 , 53 , 61 , 65 , 77 and 436.

Narayan

Dear friend
Thanks a lot for providing your obligation.

You are absolutely correct as few of the data found missed in the previous sheet therefore please i have attached the updated sheet again in this forum
 
Dear Friend

Great thanks for providing the solution. it is working fine.

Dear Friend
Incorrect result receiving in the attached Sheet. Please do the needful.

Actually time gap from last transaction is more than 24 hours but formula representing "true" instead of "False"
 

Attachments

  • Revised Test Sheet.xlsx
    16.3 KB · Views: 2
Hi ,

This is for finding out the latest entry corresponding to an ID , just prior to the current ID.

For example , suppose we are on row #154 , and the current ID is in C154.

For all entries in column C , from C2:C153 , if there is an identical ID , this retrieves the most recent entry ; thus if C37 , C77 and C123 all had the same ID as the one in C153 , this will retrieve the entry corresponding to C123 , since that is the most recent one.

All this is assuming that the data is sorted ID-wise , time-wise.

Narayan
 
Another shorter and non-array formula solution

1] In D2, formula copy down :

=IFERROR(((LOOKUP(2,1/(C$1:C1=C2),A$1:A1)<>A2)*((B2-LOOKUP(2,1/(C$1:C1=C2),B$1:B1))<1)*(LOOKUP(2,1/(C$1:C1=C2),D$1:D1)<>TRUE))>0,FALSE)

2] See attached file

Regards
Bosco
 

Attachments

  • Test Sheet1.xlsx
    17.7 KB · Views: 5
Another shorter and non-array formula solution

1] In D2, formula copy down :

=IFERROR(((LOOKUP(2,1/(C$1:C1=C2),A$1:A1)<>A2)*((B2-LOOKUP(2,1/(C$1:C1=C2),B$1:B1))<1)*(LOOKUP(2,1/(C$1:C1=C2),D$1:D1)<>TRUE))>0,FALSE)

2] See attached file

Regards
Bosco

Few errors seen therefore please do the needful in attached sheet.
 

Attachments

  • RecordLevelTransactionReportChild.xlsx
    124.2 KB · Views: 3
Back
Top