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 -

​

 

[xld]

you should be able to do it with a formula.

Assuming that Table 2 is on Sheet2 columns A:B, and Table 1 on Sheet1 Columns A:B, in B4 of Table 2 enter

=SUMIF(Sheet1!A:A,Sheet2!A4,Sheet1!B:B)

and just copy down.

 

[RepetitiveLady]

i don't quite understand, as i know nothing in Excel. I tried what u told me, but i couldn't get any result from it. Do u mind explaining every step to me?

 

[RepetitiveLady]

Sorry to be such a nuisance, i'm slow at understanding things.
I've tried it again, it seems that i've placed Table 1 into Sheet2 and Table 2 into Sheet1. Silly me.
Now i've placed it correctly, i'm still getting 0 for all the rows in Column B of Sheet2.

Please help...

Thanks.

 

[sanrv1f]

Hello and welcome to the board,

here is the input sheet,

Excel Workbook
	A	B
1	Quantity	Product
2	5	ButtonAWhite
3	7	RibbonBBlue
4	8	ThreadABlack
5	10	ClothAWhite
6	3	ButtonBBlue
7	4	ButtonCWhite
8	9	RibbonAPink
9	15	ButtonAWhite
Sheet1

and here is the database sheet

Excel Workbook
	A	B
1	Products	Quantity
2
3	ButtonsCategory
4	ButtonAWhite	20
5	ButtonABlack	0
6	ButtonABlue	0
7	ButtonBWhite	0
8	ButtonBBlack	0
9	ButtonBBlue	3
10	ButtonCWhite	4
11	ButtonCBlack	0
12	ButtonCBlue	0
13
14	RibbonCategory
15	RibbonAWhite	0
16	RibbonABlue	0
17	RibbonAPink	9
18	RibbonBWhite	0
19	RibbonBBlue	7
20	RibbonBPink	0
21
22	ThreadCategory
23	ThreadAWhite	0
24	ThreadABlue	0
25	ThreadABlack	8
Sheet2

Cell Formulas
Range	Formula
B4	=SUMIF(Sheet1!B:B,Sheet2!A4,Sheet1!A:A)
B5	=SUMIF(Sheet1!B:B,Sheet2!A5,Sheet1!A:A)
B6	=SUMIF(Sheet1!B:B,Sheet2!A6,Sheet1!A:A)
B7	=SUMIF(Sheet1!B:B,Sheet2!A7,Sheet1!A:A)
B8	=SUMIF(Sheet1!B:B,Sheet2!A8,Sheet1!A:A)
B9	=SUMIF(Sheet1!B:B,Sheet2!A9,Sheet1!A:A)
B10	=SUMIF(Sheet1!B:B,Sheet2!A10,Sheet1!A:A)
B11	=SUMIF(Sheet1!B:B,Sheet2!A11,Sheet1!A:A)
B12	=SUMIF(Sheet1!B:B,Sheet2!A12,Sheet1!A:A)
B15	=SUMIF(Sheet1!B:B,Sheet2!A15,Sheet1!A:A)
B16	=SUMIF(Sheet1!B:B,Sheet2!A16,Sheet1!A:A)
B17	=SUMIF(Sheet1!B:B,Sheet2!A17,Sheet1!A:A)
B18	=SUMIF(Sheet1!B:B,Sheet2!A18,Sheet1!A:A)
B19	=SUMIF(Sheet1!B:B,Sheet2!A19,Sheet1!A:A)
B20	=SUMIF(Sheet1!B:B,Sheet2!A20,Sheet1!A:A)
B23	=SUMIF(Sheet1!B:B,Sheet2!A23,Sheet1!A:A)
B24	=SUMIF(Sheet1!B:B,Sheet2!A24,Sheet1!A:A)
B25	=SUMIF(Sheet1!B:B,Sheet2!A25,Sheet1!A:A)

 

[RepetitiveLady]

Thank you so much. I've finally got it. Oh, i'm so happy.

Can i ask 1 more thing?
How do you remove all the 0 without removing those 0 behind the number 10?

 

[xld]

Try

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

 

[RepetitiveLady]

Thanks xld.

Your new code got rid of all the "0" and did the job at the same time.

 

[RepetitiveLady]

One more problem, this code here

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

Even though it removes the 0 at the rows where there is no item for that day, it generates 0 at empty rows. Example:

Excel Workbook
	A	B
1	Products	Quantity
2		0
3	Buttons Category
4	Button A White	20
5	Button A Black
6	Button A Blue
7	Button B White
8	Button B Black
9	Button B Blue	3
10	Button C White	4
11	Button C Black
12	Button C Blue
13		0
14	Ribbon Category
15	Ribbon A White
16	Ribbon A Blue
17	Ribbon A Pink	9
18	Ribbon B White
19	Ribbon B Blue	7
20	Ribbon B Pink
21		0
22	Thread Category
23	Thread A White
24	Thread A Blue
25	Thread A Black	8
...

As you can see, there are 0 on rows where column A is empty.

Can you do something about that?

 

[sanrv1f]

try this

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