Inventory Sign Out Quantity Issue

[MVE_Colby]

Hello, I have something I am trying to solve and have been searching all over. 

I have a sheet made to help keep track of stuff taken from the warehouse. We have a scanner that pulls from a sheet that has prices and that all works fine. The Issue I am having is having the quantity determined based off of what is scanned. I was able to get it set to fill to "1" if it pulls a proper part number from the list. I cant find a way to tell it to merge its current quantity value and the one under it IF they meet two conditions: First one being that the part number is the same, Second being if the Equipment number is the same. 

For Example this screen shot when you scan it twice it puts two rows. I need it to read the two conditions of same "Barcode scan" and same "job number" so it can merge the quantity of 1+1

But it also needs to know if the value of the two merged is indeed merged so it can clear the second barcode scan so it doesn't repeat endlessly.

[#2]

@MVE_Colby 

=VSTACK(A1:G1,

FILTER(

HSTACK(

A2:B11,

MAP(A2:A11,F2:F11,LAMBDA(barcode,job,SUMIFS(C2:C11,A2:A11,barcode,F2:F11,job))),

D2:D11,
MAP(A2:A11,F2:F11,LAMBDA(barcode,job,SUMIFS(E2:E11,A2:A11,barcode,F2:F11,job))),

F2:G11),

G2:G11<>"")

)

Does this formula return the intended result?

[#3]

Sadly not, it needs to update on the fly as you are scanning and putting the job number in. I am messing with VBA codes trying to get that to do what I need. From what I can find normal excel formulas can't clear a spot if it does match. That or just make a separate application for the sign out and make it export properly into excel.

[#4]

@MVE_Colby 

You are correct.  For a formula to change user input would violate a fundamental principle of Excel (that of immutability) as it would any other functional programming language.  For a formula to work you have to create a new copy of the data.

VBA (as an imperative programming language) will allow you to make such changes on the fly.  The downside is that you then have no record of the original input so any data entry or processing error will no longer be visible.  If you have other data cleaning steps you need to keep track of the order in which they are applied (not an impossible task, but not one Excel is set up to do) because the result will be different.

[#5]

https://www.sqlitetutorial.net/sqlite-replace-statement/

if saved in a database e.g. sqlite,you can achieve this one sql statement.