Placing into the correct name and category

[RepetitiveLady]

Hi everyone,

I have a very repetitive problem here that i've been having for quite some time now. I need to input quantity of items into the existing database that holds all the items names which is sorted by category. Below is a sample of the problem:

Table 1 - Items with quantity that is waiting for input:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="166" width="448"><tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td align="center" height="4" width="14%">5
</td><td height="4" width="14%">Button A White
</td></tr><tr><td align="center" height="4" width="14%">7
</td><td height="4" width="14%">Ribbon B Blue
</td></tr><tr><td align="center" height="4" width="14%">8
</td><td height="4" width="14%">Thread A Black
</td></tr><tr><td align="center" height="4" width="14%">10
</td><td height="4" width="14%">Cloth A White
</td></tr><tr><td align="center" height="4" width="14%">3
</td><td height="4" width="14%">Button B Blue
</td></tr><tr><td align="center" valign="top">4</td><td valign="top">Button C White
</td></tr><tr><td align="center" valign="top">9</td><td valign="top">Ribbon A Pink</td></tr><tr><td align="center" valign="top">15</td><td valign="top">Button A White</td></tr></tbody></table>
Above is just a part of it, the list goes on to over 200 rows long.


Table 2 - Existing database of all products:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="166" width="448"><tbody><tr> <td valign="top">Products
</td><td align="center" valign="top">Quantity
</td> </tr><tr><td valign="top">
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Buttons Category
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Button A White
</td><td align="center" valign="top">20
</td></tr><tr><td valign="top">Button A Black
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Button A Blue
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Button B White
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Button B Black</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Button B Blue
</td><td align="center" valign="top">3
</td></tr><tr><td valign="top">Button C White
</td><td align="center" valign="top">4
</td></tr><tr><td valign="top">Button C Black
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Button C Blue
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Ribbon Category
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Ribbon A White
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Ribbon A Blue
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Ribbon A Pink
</td><td align="center" valign="top">9
</td></tr><tr><td valign="top">Ribbon B White
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Ribbon B Blue
</td><td align="center" valign="top">7
</td></tr><tr><td valign="top">Ribbon B Pink
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Thread Category
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Thread A White
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Thread A Blue
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Thread A Black
</td><td align="center" valign="top">8
</td></tr><tr><td valign="top">
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Cloth Category
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Cloth A White
</td><td align="center" valign="top">10
</td></tr><tr><td valign="top">Cloth A Blue
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Cloth A Black
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Cloth B White
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Cloth B Blue
</td><td align="center" valign="top">
</td></tr><tr><td valign="top">Cloth B Black
</td><td align="center" valign="top">
</td></tr></tbody></table>
Above is just part of the database, the whole database goes to more than 900 rows.


I need to key in all the data from table 1 to table 2.

Explanations for Table 1:
All the data for Table 1 is just random and repeats itself.
For example the "Button A White"

Explanations for Table 2:
The products and categories in "Products" column for Table 2 is fixed. Altering it would mean altering all the data for past years.

I need to do the above everyday. And the list is getting longer and longer. I was told by a friend of mine that there is something called Macro in MS Excel. But i do not know how to use it. I ask him, but he didn't know too and suggested that i post my problem here.

Really appreciate it if someone can help me out.

Cheers
- Cassandra -

​

 

[RepetitiveLady]

Thanks. It works. Just that there is a mistake at the code. MS Excel has automatically corrected it. I think a closing bracket is missing at the end of the codes. I think it should be like this:

=IF(OR(COUNTIF(Sheet1!A:A,Sheet2!A4)=0,Sheet2!A4=""),"",SUMIF(Sheet1!A:A,Sheet2!A4,Sheet1!B:B))

Never mind about that, as long as it works. And you are so kind to help me out.

Thank you so much for your help sanrv1f.