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.

Get date for condition

Discussion in 'Ask an Excel Question' started by trprasad78, Mar 14, 2018.

  1. trprasad78

    trprasad78 Member

    Messages:
    167
    Hello experts ,
    I need your help for following .

    I have attached xlsx file sheet name = "source" in that 3 fields in "Total","Merge" & Date.
    Total have numbers and zero
    Merge is a ID
    Date : = Date

    from above data fetch summary based on Total field.
    i will explain what i do manually, same i need automatically using formula or macro anything fine for me.
    ex:-
    We need to pick the date
    If we filter Merge ID = VFT012142006
    In "Total" field we need to come from bottom of the record.
    we need to find the record from bottom, which one is zero after the number that number record date we need to pick .
    check the out put i need in "out put " sheet.

    in above example A39 is the number before zero, so the date need to pick is "05-12-17"

    sorry for bad explanation , hope i get solution.
    if you have doubt please revert.
    Thank you :)

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,354
    In your example for VFT012142006 you have date 4/01/2018, that isn't the same as described above ?

    Because there is only 4 VFT Numbers, why not highlight which Records you want eg:
    upload_2018-3-14_18-51-3.png

    then we can work out the logic
  3. trprasad78

    trprasad78 Member

    Messages:
    167
    i have highlighted which date i want in attached file.

    We need to filter VFT012142006 then we need to go from bottom of total field,

    upload_2018-3-14_16-39-33.png

    if next number is zero , it has to pickup the current row / record and get summary like below.

    Attached Files:

  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,354
    For VFT...2006, How do you get 04-01-2018, shouldn't it be 28-12-2017
  5. trprasad78

    trprasad78 Member

    Messages:
    167
    Sorry wrongly i mentioned 04/01/2018 in out put sheet
    below is right one
    upload_2018-3-14_16-46-31.png
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi,

    For VFT012182002 , the correct output should be 04-01-2018 , and not 05-12-2017 as posted.

    Can you confirm ?

    Narayan
  7. trprasad78

    trprasad78 Member

    Messages:
    167
    No correct one is 05-12-2018
    Last edited by a moderator: Mar 14, 2018
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    There is no date such as 05-12-2018 in the dates column.

    Can you clarify ?

    Narayan
  9. trprasad78

    trprasad78 Member

    Messages:
    167
    sorry again 05-12-2017 not 2018
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    Please explain how the date for VFT012182002 is 05-12-2017 , with specific references to the rows where the Total value is 0 , and the first row thereafter is non-zero.

    Narayan
  11. trprasad78

    trprasad78 Member

    Messages:
    167
    If it is no zero in Total value we need to take first record date. (Top one)
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    We are wasting a lot of time.

    The value VFT012182002 has zeros in the Total column , in cells A10 and A38.

    Which zero value should be considered , and which non-zero value should be considered ?

    The non-zero values are in cells A14 , A18 , A22 , A26 , A30 , A34 and A48.

    Narayan
  13. trprasad78

    trprasad78 Member

    Messages:
    167
    Hi Mr Naryan,

    as i mentioned early, we need to filter by VFT012182002 then we get only 1 zero on first record , so we need to consider next record and date should be 05-09-17

    upload_2018-3-14_20-12-59.png
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    I have been discussing the data value VFT012182002 in all my posts.

    I think someone else will respond. Sorry.

    Narayan
  15. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,354
    So its the First entry after the last Zero
    is that correct?
    trprasad78 likes this.
  16. trprasad78

    trprasad78 Member

    Messages:
    167
    But in below scenario from bottom 2 entry is zero.
    3 entry is number, 4th one also number so it has to check 5th column.. its zero so we need to consider the 4th row from bottom so we consider the date as 05-12-2017

    [​IMG]
    Last edited by a moderator: Mar 14, 2018
  17. trprasad78

    trprasad78 Member

    Messages:
    167
    sorry for my mistake again :(

    as i mentioned , we check total field from bottom,
    1st entry is number so checking next column .. its zero.
    so we need to consider bottom 1st column and pick the same column date.
    04-01-18

    upload_2018-3-14_21-4-29.png
  18. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

    Thomas Kuriakose and trprasad78 like this.
  19. trprasad78

    trprasad78 Member

    Messages:
    167
    Great its working fine, Thank you so much for your patient.

    This source data we add records weekly and monthly, hope if i insert table in source data. Out put will change automatically when ever i add data in source sheet.

    Thank you again. :)
  20. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    You will need to change the references based on how far down your data extends.

    If you can convert your data range into a table , then the formulae can be fixed , and will not need to be extended as and when fresh data is added / pasted.

    Narayan
  21. trprasad78

    trprasad78 Member

    Messages:
    167
    In your file, picked wrong date in VFT012142006 as to pickup 05-12-2017 but it had picked up
    04-01-2018

    Please do the need ful

    Attached Files:

  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    Please explain the logic , in English.

    1. What should be done if there is no zero in the Total column ?

    2. What should be done if all the values in the Total column are zeros ?

    3. What should be done if the last value in the Total column is zero ?

    Narayan
  23. trprasad78

    trprasad78 Member

    Messages:
    167
    1. What should be done if there is no zero in the Total column ?

    No zero , consider top first record date.

    2. What should be done if all the values in the Total column are zeros ?
    Out put also zero

    3. What should be done if the last value in the Total column is zero ?

    a) Last value is zero rest above all numbers, then we consider 2nd record from bottom.
    b) Last value is zero also after 4 record again zero, we need to consider 4th record.
  24. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    Please check back later.

    Narayan
  25. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,556
    Hi ,

    See if this is OK.

    If there is a problem in the calculations , upload a workbook which has data that caters to all possible variations.

    Narayan

    Attached Files:

    Thomas Kuriakose and trprasad78 like this.

Share This Page