What's the best way to consolidate information from this table?

[line1260skr]

What would be the best way to consolidate the information below?


I need to sum the input quantities for similar input items (selected from data validation)across each Item_#. The problem I am having is that each input is being selected from a large list using data validation. The totals will essentially be used to total the quantities needed for each item and for streamlining that data to a purchase ordering system. I am not sure if a pivot, vba, sumif, or what is the answer. Can someone help me out?


Item_1 input1 input1_qty input2 input2_qty input3 input3_qty

Item_2 input1 input1_qty input2 input2_qty input3 input3_qty

Item_3 input1 input1_qty input2 input2_qty input3 input3_qty

Item_4 input1 input1_qty input2 input2_qty input3 input3_qty

 

[line1260skr]

Solved with vba and multiple ranges

 

[SirJB7]

Hi, line1260skr!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


It would be nice if you post your solution so as to people who might read this topic could also access to a way of solving it. If needed please refer to second green sticky post for uploading guidelines. Thank you!


Regards!