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

Return Next Instance

Busymanjohn

Member
Hi, looking for some help on the following ,,,, I have 2 reports, which shows me data on when stock arrives and when it moves out ( dates ) and I currently use VLOOKUP to look at the part and location ( concatenated ) to show me when stock moves out and subsequently calculate how many days that part spent in stock. The problem is that there are instances when the part number and the location appear multiple times ( see the example file ) and then the number of days in stock can sometimes be a negative number ( which is obviously incorrect ), is there a way to return the next instance of that part and location concatenate if the number of days in stock is a negative number?
 

Attachments

  • Sample 3.xlsx
    12.4 KB · Views: 5
Hi,

pls check excel file if it is works for you...highlighted in green color

regards
Naresh
 

Attachments

  • Sample 33.xlsx
    12.5 KB · Views: 9
Hi Naresh, this works when the parts are one row apart, but if the parts have rows in between then it doesn't quite work.
 
Hi Naresh, file shared ,,,, let me try and explain a little more ,,, the report is pulled from a system and shows the part and location of stock in and out ,,,, a positive number in the quantity field signifies stock in, a negative number signifies stock moving out, I tend to split the report into 2 ,,, one showing stock in ( positive quantities ) and one showing stock out ( negative quantities ), the problem arises when I want to check the number of days from stock in to stock out ,,,,, it needs to be against the part and location, the example file shows the report as it is pulled from the system and scroll down you will see how I split it out and the cells highlighted show the result using LOOKUP, you will see 2 parts highlighted in ORANGE that are returning the wrong stock out date and subsequently the incorrect number of days in stock.
 

Attachments

  • Copy of Sample 33.xlsx
    18.5 KB · Views: 4
Hi Narayan, that seems to work fine ,, one question though, in instances where there is no stock moving out ,,, so has no date, the formula returns 00/01/1900 default ,,, can these be returned as blanks instead?
 
Hi ,

Put this check before the formula , as in :

=IF(COUNTIFS($B$5:$B$6, B12, $E$5:$E$6, ">=" & E12) = 0, "", MIN(IF($B$5:$B$6=B12,IF(($E$5:$E$6 - E12) >= 0,$E$5:$E$6))))

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan
 
Hi Narayan, sorry it's been a few days since I got back to this, the formula works just fine, thank you for that .. a quick question though, if the same part number_location appears more than once in the report ( which does happen ), how can we ensure that the formula returns the correct date for stock out ,,,, an example in the attached file, cell H57 shows the correct date for the instance where the part number_location appears more than once.
 

Attachments

  • Copy of Sample 33.xlsx
    18.9 KB · Views: 2
Back
Top