Ask Your Question
0

Display Difference Between Values In 2 Sheets [closed]

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

slugger gravatar image

updated 2020-08-09 15:10:30 +0100

Alex Kemp 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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-09 15:10:41.554252

1 Answer

Sort by » oldest newest most voted
0

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

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

Question Tools

1 follower

Stats

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

Seen: 46 times

Last updated: Jul 02 '17