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

Get date for condition

trprasad78

trprasad78

Member
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 :)
 

Attachments

  • SAMPLE.xlsx
    11.9 KB · Views: 5
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
 
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.
 

Attachments

  • SAMPLE (4).xlsx
    12 KB · Views: 8
  • upload_2018-3-14_16-41-24.png
    upload_2018-3-14_16-41-24.png
    9 KB · Views: 5
For VFT...2006, How do you get 04-01-2018, shouldn't it be 28-12-2017
 
Hi,

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

Can you confirm ?

Narayan
 
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
 
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
 
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
 
Hi ,

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

I think someone else will respond. Sorry.

Narayan
 
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

upload_2018-3-14_16-39-33-png.50709
 
Last edited by a moderator:
NARAYANK991 said:
Hi ,

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

I think someone else will respond. Sorry.

Narayan
Click to expand...
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
 
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. :)
 
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
 
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
 
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.
 
You must log in or register to reply here.
Back
Top