1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Ram Mher, Aug 11, 2017.

  1. Ram Mher

    Ram Mher Member

    Messages:
    111
    Dear Friends

    Please find the attached Sheet and provide the solution.

    Thanks in Advance
    Regards
    rammher

    Attached Files:

  2. p45cal

    p45cal Well-Known Member

    Messages:
    763
    Attached

    Attached Files:

  3. Ram Mher

    Ram Mher Member

    Messages:
    111
    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.

    Attached Files:

  4. Ram Mher

    Ram Mher Member

    Messages:
    111
    Uni
    uniques id in data sheet is Hex Tag no
  5. p45cal

    p45cal Well-Known Member

    Messages:
    763
    Why does this thread make me feel like an employee being given instructions by my boss…
    NARAYANK991 likes this.
  6. Ram Mher

    Ram Mher Member

    Messages:
    111
    I
    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.
  7. Ram Mher

    Ram Mher Member

    Messages:
    111
    P
    Friends I need your help in finding the solution.pls......
  8. p45cal

    p45cal Well-Known Member

    Messages:
    763
    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.
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,069
    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
  10. Ram Mher

    Ram Mher Member

    Messages:
    111
    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.

    Attached Files:

  11. Ram Mher

    Ram Mher Member

    Messages:
    111
    Dear
    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
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,069
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Ram Mher likes this.
  13. Ram Mher

    Ram Mher Member

    Messages:
    111
    Dear Friend

    Great thanks for providing the solution. it is working fine.
  14. Ram Mher

    Ram Mher Member

    Messages:
    111
    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"

    Attached Files:

  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,069
    Hi ,

    See this file.

    Narayan

    Attached Files:

  16. Ram Mher

    Ram Mher Member

    Messages:
    111
    Thanks a lot friend and this is working fine.

    please help me in evaluation of below string as i have no idea for this function:-
    INDEX(A$2:A153, MAX(IF(C$2:C153 = C154, ROW(C$2:C153) - MIN(ROW(C$2:C153)) + 1)))
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,069
    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
  18. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,164
    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

    Attached Files:

  19. Ram Mher

    Ram Mher Member

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

    Attached Files:

  20. Ram Mher

    Ram Mher Member

    Messages:
    111
    I
    I again checked the data and found completely correct. No need to make amendments.

    Thanks friend for great favour. really appreciable support provided by you

Share This Page