lookup value in sheet, and remove row if matching

Hi everyone! I am new, and happy to have joined this excellent forum.

I have been trying to figure this out but falling short of a working solution.

I have 3 very basic worksheets Sheet1(GoodsIn), Sheet2(GoodsOut) and Sheet3(Current).

The goods are represented by an ID number, the problem is the ID number duplicates as it can go out of stock and come back into stock causing my VLOOKUP/filter solution to falter. I am using the following formula '=IF(ISERROR(VLOOKUP(B2,Out!B:B,1,FALSE)),"In","Out")' to compare values from sheet 2 and display an 'IN' or 'OUT' message in the column next to the 'ID Number' column.

As an example: if ID Number 11999 comes in, it updates to Sheet3 via '=In!B1'. When ID Number 11999 is entered into Sheet2, the VLOOKUP correctly displays 'OUT'. When ID Number 11999 returns, unfortunately the VLOOKUP stops are the first value, never changing its status from 'OUT'.

All three sheets will be increasing, so I ruled out using a range. There are also date fields in both in and out, but couldnt figure out how to Lookup the ID Number then compare dates with the most recent date removing the ID Number from sheet3.

I've been hunting around but was unable to find a solution to ignore the first duplicate and go to the last duplicate found using VLOOKUP.

Any suggestions for a better method for my project would be greatly appreciated.

Thank you for you responses, in advance.

Nach

Hello Pierre,

Thank you for your reply, sorry I did mean to upload a sample earlier. Hope this helps identify my problem.

samplestocksheet.xlsx

Nach

Hi Pierre,

Thanks again for your reply. What I am trying to achieve is a bit different to a standard inventory in the respect that the ID Number represents a unique unit that there is only one of. I guess we could look at it similar to car rental, in the fact that every car has its own number plate and it would leave the ware house to be rented, but then return after its rental.

Does this make more sense?

Thanks,
Nach

Very, very clever - thank you very much

Originally Posted by FDibbins

Here's another suggestion.

If you put all the data onto 1 sheet, in columns A and B, instead of IN and OUT sheets like you have, then in column C, copied down, you could use this to determine IN or OUT...

=IF(MOD(COUNTIF($B$2:B2,B2),2)=1,"In","Out")

Thank you for that Pierre. That's a very good suggestion to help me then filter out what is physically in stock.

Originally Posted by p24leclerc

FDibbins, very clever.
It would probably be a good thing to create a table of available units besides columns A,B and C to show directly the state of each units (In or OUT). Instead of looking into the long data of column B, in would be readily available in that table. Your formula will then be changed to this
=IF(MOD(COUNTIF($B:$B,F2),2)=1,"In","Out")
to take in account all data.

FDibbins - The ID Number can be repeated throughout the list as it comes in and out, when I apply a filter to show only 'in' units it doesnt take into account if the unit has later gone 'Out', if you get me? So even though the status may have changed to 'Out' when I filter to see the 'In's it will show the previous 'In' entry.

Originally Posted by FDibbins

Here's another suggestion.

If you put all the data onto 1 sheet, in columns A and B, instead of IN and OUT sheets like you have, then in column C, copied down, you could use this to determine IN or OUT...

=IF(MOD(COUNTIF($B$2:B2,B2),2)=1,"In","Out")

Hi FDibbins,

Thanks again for replying.

I find that formula counts the ID number as an 'In' even if it was going out.

Your original suggestion of =IF(MOD(COUNTIF($B$2:B2,B2),2)=1,"In","Out") is very clever and is what I am looking for. Leading on from that, i need to be able to see what is physically in stock, so I need a way to filter the 'ins' and 'outs' to automatically show what is physically in stock.

so as an example, 11999 comes in on the 5th, but then goes back out on the 6th. I want to see what is currently 'In' the warehouse, so I filter the 'status' column to show only 'In's, this would show 11999 as being 'In' even though it went out on the 6th :

Date.....|..ID Number..|..Status..
5/02/13.|....11999.....|..In..
6/02/13.|....11999.....|..Out..

Hope you can help?

Originally Posted by FDibbins

wouyld something like this help? it adds the progressive amount of "ins" on that item number...
=IF(MOD(COUNTIF($B$2:B2,B2),2)=1,"In "&COUNTIF($B$2:B2,B2),"Out")

Hi Visha,

Thanks for you reply. This is another solution to what I am looking to achieve, but i need a quick way to then filter or report the status of a unit to show if it is currently 'in'.

Originally Posted by visha_1984

Hi,Nacho

pls check attacment if helpful pls revert.

Hi Pierre,

That is extremely clever and exactly what I was looking to achieve. If it doesn't take too long - do you mind talking me through the way your formula works?

Originally Posted by p24leclerc

Here is what I would suggest you (See attached file)
With the IN tab, you record units movements by date. The use of FDibbins formula will give you an indication of the actual condition of this unit (if the operation is either to put in IN out OUT).
In the Current tab, you'll have the list of all your units in column A (unique number only).
Then, the formula in column B will tell you if that unit is actually IN out OUT. You can filter this list and it won't affect the result.
Hope this help

Hi Guys,

Thanks for the help with this, my stocksheet has been working great since you made your suggestions. I very much appreciate it.

I have encountered a snag - I have decided to display the information in a pivot table from the main In/Out sheet (see attached.

What I am having a trouble with is getting excel to perform a accurate count on what models are actually in stock. If you see the attached, you will see that excel counts the amount of times the model has moved in as opposed to how many models are actually in stock.

I'm guessing I am unable to do this using the pivot table features. Can anyone suggest a solution to count the models that are in, and disregard duplicates to show just 1 item?

Hope this makes sense.

Nach