Compare Inventory vs. Valid Parts Configurations

Hi all!

Let's say I have a huge pile of parts and I want to make a list of quantities, part numbers, descriptions in Excel. Next, I want to paste in long lists of all kinds of parts that form whole assemblies, maybe on different tabs. (Call them assembly 1, assembly 2, etc.)

What I want Excel to do is to compare the parts and quantities on hand and display a screen with all of the valid assemblies that I might be able to make, with a percentage scored list (100% complete at the top, 99%, etc., all the way down to, say, 5% completeness.)

I've never used Excel much, is it the best program to do something like this? Or is there an existing piece of software that would do it better? I Googled various things but found nothing. (I am talking about tens of thousands of parts and probably hundreds of possible valid assemblies, so I don't know if excel can handle that or not.)

Also, if anyone could point me to forum threads that discuss something similar that would be great also (I don't know enough yet to search for the right terms, unfortunately.)

Thanks a lot for any help!

Thanks a lot!

I am wondering, however, if I can have it look at the pasted-in lists in my assemblies tab as individual entities without having to create a separate formula for each one (as there will be thousands of different assemblies). I have attached a rough idea of the 3 tab sheet I am working on as an exercise. Maybe you can see what I am trying to do.

Any help with function examples relating to the attached would be most helpful. (xls file within the zip)

The third tab is where I am hoping the main action will take place with the completeness percentages.