Need some help eliminating used element from a huge list & retain only unused ones.




I have attached a spreadsheet file. It contains 2 sheets:
* Master Sheet - contains Element Number [Column A] & Number of each Element [Column B]
* Subset Sheet - contains Elements used [Column B] & Quantity used [Column D]


How can I create a new sheet, that is a copy of the Master Sheet, that has eliminated the elements that have been included in Subset Sheet?


For example, the Master List contains 12 Nos. of the Element 05-10000 [A4, B4]. The Subset indicates that 7 Nos. of 05-10000 [B2, D2] have been utilized.


Therefore, I would like the new sheet to indicate that 5 Nos. of Element 05-10000 remain.


Additionally, the Subset sheet indicates that 1 Nos. of element 05-10100 was used. Therefore, the new sheet must indicate that 0 Nos. of 05-10100 remain.


Do note that the Element Numbers in the Subset are not arranged in sequential order.
Alternately, it is also fine, if the balance quantity can be displayed in a new column of the Master Sheet itself.


Please guide.


TIA

Attached Files

Comparison - Upload.xls (493.0 KB, 11 views)

Try this in M4 of "Master List" and drag down

=$B4-SUMIF(Subset!$B$2:$B$660,'Master List'!$A4,Subset!$D$2:$D$660)

I am getting the error.
Please guide.

Works for me when tested or I wouldn't have suggested this.

Does your region of the world use ; instead of , in formulas ?

Thanks NoSparks. Tweaked it a bit & now it is working. The working expression.