How to return the sum of multiple matches

[2kool4skool]

HI, and thanks in advance to everyone who takes the time to reply this post.



I have been struggling with coming up with a system up update my store inventory for several months.
so I have decided to separate the most crucial aspect of my inventory system and give a simplistic example of the problem.

I would like to know how many of eack we can sell. as long as our supplier has it, we can sell it. here is our product database

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:230pt" border="0" cellpadding="0" cellspacing="0" width="230"> <colgroup><col style="mso-width-source:userset;mso-width-alt:5290;width:124pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:4522;width:106pt" width="106"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;width:124pt" height="15" width="124">OUR product database</td> <td class="xl64" style="border-left:none;width:106pt" width="106">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">product ID</td> <td class="xl64" style="border-top:none;border-left:none">qty available to sell</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} .xl66 {border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl67 {font-weight:700; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext;} .xl68 {border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext;} .xl69 {border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext;} .xl70 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} --</style>

here is our suppliers' inventory list

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:244pt" border="0" cellpadding="0" cellspacing="0" width="244"> <colgroup><col style="mso-width-source:userset;mso-width-alt:3157;width:74pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:4394;width:103pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:2858;width:67pt" width="67"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;width:74pt" height="15" width="74">supplier's inventory list</td> <td class="xl64" style="border-left:none;width:103pt" width="103">
</td> <td class="xl64" style="border-left:none;width:67pt" width="67">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">supplier code</td> <td class="xl64" style="border-top:none;border-left:none">supplier product ID</td> <td class="xl64" style="border-top:none;border-left:none">qty on hand</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Valencia Sweet</td> <td class="xl64" style="border-top:none;border-left:none" align="right">24</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Red Delicious</td> <td class="xl64" style="border-top:none;border-left:none" align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Berry Made</td> <td class="xl64" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Big Reds</td> <td class="xl64" style="border-top:none;border-left:none" align="right">80</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Florida best</td> <td class="xl64" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Washington Pride</td> <td class="xl64" style="border-top:none;border-left:none" align="right">600</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Carlsbad Select</td> <td class="xl64" style="border-top:none;border-left:none" align="right">29</td> </tr> </tbody></table>

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl66 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl67 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} .xl68 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;} .xl69 {font-weight:700; border:.5pt solid windowtext;} --></style>

and here is a table that I had to make which translates each of the supplier's names for each type of fruit into our product ID for it.
it is updated as new types of fruit become available.

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:311pt" border="0" cellpadding="0" cellspacing="0" width="311"> <colgroup><col style="mso-width-source:userset;mso-width-alt:6058;width:142pt" width="142"> <col style="mso-width-source:userset;mso-width-alt:3200;width:75pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:4010;width:94pt" width="94"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;width:142pt" height="15" width="142">alternate product IDs table</td> <td class="xl64" style="border-left:none;width:75pt" width="75">
</td> <td class="xl64" style="border-left:none;width:94pt" width="94">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">OUR IDs</td> <td class="xl64" style="border-top:none;border-left:none">Supplier code</td> <td class="xl64" style="border-top:none;border-left:none">supplier SKU</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Valencia Sweet</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Florida best</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Red Delicious</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Washington Pride</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Berry Made</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Carlsbad Select</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Big Reds</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Berry Made</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Carlsbad Select</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Big Reds</td> </tr> </tbody></table>


I know how to do Vlookups and that's about it. I've learned a lot from these forums, but not enough to figure this one out.
if I use a Vlookup it will only return the result for the first alternate ID match.

so for instance, on the suppliers' inventory table, I can try and enter a vlookup formula on the suppliers" inventory table
to match the supplier product ID to our ID from the alternate ID table, and then I could enter a vlookup formula on our
product table to lookup the inventory, but the vlookup on the supplier table can only return 1 result at a time,
so it would return the match of "Strawberry" for "Berry Made", but it wouldn't return "Strawberry Double Pack".

I could try entering a vlookup formula on the alternate ID table and return the inventory value from the supliers' inventory
table, and then enter another vlookup on our product ID table to return the inventory from the alternate ID table but same
problem, it would only return the inventory of the first match (it would give me the value for "Valencia Sweet" but not "Florida Best".

I don't know enough to proceed. I have heard of pivot tables but I don't even know enough to know whether or not that would help me.
I'm really stuck here and I would appreciate it if someone knew how to solve this problem.
thanks,
Steve

 

[Peter_SSs]

Hi Peter
thanks for the tip
I'm not sure if those will work in 2011 Mac but if not I can load up 2010 in windows and try it from there.

it think maybe the last one
http://www.mrexcel.com/forum/showpost.php?p=2198045&postcount=2
will work on 2011 mac
I was just putting borders around the cells and then copying and pasting into firefox.
Steve

Yes, I'm not sure if the HTML Makers work with Macs. Also, with the "borders" method I think you may need to be using Internet Explorer so that may also be an issue. Do some testing in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[2kool4skool]

ok, thanks for the link.
loving this forum. I've watched the podcasts for over a year, but I just decided to give the forums a try and everyone on here is so great. a wonderful combination of expertise and egalitarianism. they are often mutually exclusive on forums, but not here.