Ask Your Question
0

Display Difference Between Values In 2 Sheets

asked 2017-07-02 21:06:46 +0200

slugger gravatar image

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]

https://drive.google.com/file/d/0B5LH...

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-07-02 22:52:09 +0200

Lupp gravatar image

Learn about COUNTIF, COUNTIFS, SUMPRODUCT, ...

See this reworked example.

Basically the described case should be handled with a database. As-if databases made from spreadsheets are insecure anyway and inefficient as soon as they grow up.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-02 21:06:46 +0200

Seen: 32 times

Last updated: Jul 02 '17