Hey guys, I'm doing a data consolidation of multiple worksheets which contain some stock inventory that we manage. Now I've read a lot about data consolidation online and have done some practice, although from my research I'm finding out that MS Excel does not like to handle text on sum consolidations.

The list which I'm trying to consolidate has the following column fields:
Part number, description, project, quantity

I've made it so each of the worksheets which I'd like to consolidate, have the same name fields and are organized in the name order and manner. I've specified excel to handle the part numbers as "Text", so it would not mistake the part numbers for quantitative values and I've given the cells which I'd like to consolidate a name for each range. Up to this point everything is ok, the problem that I'm having is that:

1. When I try to consolidate the data, excel does not know what to do with the description of each part number, so it adds up the quantity of each part, if multiples are listed, although it leaves a blank for where the description would go.
2. Another problem is that throughout the multiple lists organized, the descriptions do not always match. Some are in English, some in other languages. I'd like excel to accept the first match for the description and merge the quantity in stock based upon the total quantities throughout all consolidated lists.


If anyone can help, I would greatly appreciate it. I've been racking my brain trying to come up with different solutions to handle this. I really like how excel has the "Create links to source data" feature so we can keep track as to who has which inventory.

Thanks once again and I look forward to your responses!