Excel Questions

[jibu08]

hello everyone, URGENT HELP
need your help in finding a formula..
i have a very large database where i have more than 1000 product and 500 customer .. i need to know what products are listed/sold by each customer .. i have used count-if and count-ifs but it is giving me repeated product ... for eg: luna has 20 product so i need to know 111 customer is selling how many luna product?

Customer code	Product code	Brand	Product
111	10010040 <tbody> </tbody>	Luna <tbody> </tbody>	Luna Green Peas <tbody> </tbody>
111 <tbody> </tbody>	10010050 <tbody> </tbody>	Luna <tbody> </tbody>	Luna Chick Peas <tbody> </tbody>
112 <tbody> </tbody>	10011049 <tbody> </tbody>	ABC <tbody> </tbody>	Dairyland Danish Cheddar Block Coloured <tbody> </tbody>
112 <tbody> </tbody>	10011051 <tbody> </tbody>	ABC	Dairyland Danish Cheddar Block White <tbody> </tbody>
112 <tbody> </tbody>	10036240 <tbody> </tbody>	XXX	Sunbulah Lamb Kebabs <tbody> </tbody>
112 <tbody> </tbody>	10010050 <tbody> </tbody>	Luna	Luna Chick Peas 400 Gm <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[JackDanIce]

Easiest solution is to use a pivot table

 

[jibu08]

i tired but it's not giving me accurate answer.. i need non repeated products according to its brand sold by each customer

 

[Aladin Akyurek]

i tired but it's not giving me accurate answer.. i need non repeated products according to its brand sold by each customer

Are you sure?

Book1
	A	B	C	D	F	G	H	I	J
1	Customer code	Product code	Brand	Product	Count of Product code	Column Labels
2	111	10010040	Luna	Luna Green Peas	Row Labels	ABC	Luna	XXX	Grand Total
3	111	10010050	Luna	Luna Chick Peas	111		2		2
4	112	10011049	ABC	Dairyland Danish Cheddar Block Coloured	112	2	1	1	4
5	112	10011051	ABC	Dairyland Danish Cheddar Block White	Grand Total	2	3	1	6
6	112	10036240	XXX	Sunbulah Lamb Kebabs
7	112	10010050	Luna	Luna Chick Peas 400 Gm
Sheet1

 

[jibu08]

pivot table dont help as i need to know how many products is sold by each customer and also i dont want the products to be repeated... any idea???????????????? i will send you all my database through email ... jibu.7350@gmail.com

 

[Aladin Akyurek]

Your sample is misleading because there are repeats do not occur.

Are you wanting a unique count of products per customer and per brand?

 

[jibu08]

yes.. i need LIKE THIS but i have very large database
customer code Luna ABC XXX

Are you sure?
111 2 - -
112 - 2 2

	A	B	C	D	F	G	H	I	J
1	Customer code	Product code	Brand	Product	Count of Product code	Column Labels
2	111	10010040	Luna	Luna Green Peas	Row Labels	ABC	Luna	XXX	Grand Total
3	111	10010050	Luna	Luna Chick Peas	111		2		2
4	112	10011049	ABC	Dairyland Danish Cheddar Block Coloured	112	2	1	1	4
5	112	10011051	ABC	Dairyland Danish Cheddar Block White	Grand Total	2	3	1	6
6	112	10036240	XXX	Sunbulah Lamb Kebabs
7	112	10010050	Luna	Luna Chick Peas 400 Gm

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[jibu08]

Customer code Luna XXX ABC
111 2 - -
112 - 2 -
I NEED A MAKE LIKE THIS

 

[jibu08]

yes

Your sample is misleading because there are repeats do not occur.

Are you wanting a unique count of products per customer and per brand?

 

[jibu08]

Yes if you can help !!!!