Return Next Instance

[Busymanjohn]

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?

 

[Naresh Yadav]

Hi,

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

regards
Naresh

 

[Busymanjohn]

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.

 

[Naresh Yadav]

Hi,

pls share some parts of your excel file so that i could check and help accordingly

regards
Naresh

 

[Busymanjohn]

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.

 

[NARAYANK991]

Hi ,

Try this :

=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

 

[Busymanjohn]

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?

 

[NARAYANK991]

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

 

[Busymanjohn]

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.

 

[NARAYANK991]

Hi ,

See if this works on all of your data.

Narayan