Identify and count reoccurring numbers in cells

[markdoughty]

Urrmmmmhh...here goes

I have a product file spreadsheet.Which contains details of all products held in stock. We have multiple sites so MY product file is a collation of all sites. Therefore a product may appear multiple times as it may be stored at more than one site. New products can be added at anytime without my knowledge.

Column A is the product code, Column B is the product description. Column C is the qty sold.

I need to:-

• look at column A and establish every occurrence of a product code, including newly added products.
• to summarize the number of occurrences of the codes.
• copy the description from column B
• Summarise the qty sold from column C
• And display an overall summary of A, B, C.

The aim being to identify the top selling products for the group.

I have been using the code below for " NON CHANGEABLE " data, and, can probably modify this for the second part of the calculation.

But have no idea where to start in regard to finding occurrences of over 4500 numbers to which new numbers can be added at any time.

=SUMIFS('Exported Data'!S:S,'Exported Data'!E:E,"Timberworld NORTH EAST",'Exported Data'!I:I,"clearance")

Any ideas would be a great help.

Thank you

 

[texasalynn]

sounds more like a pivot table would give you what you need

 

[Akashwani]

Hi,

The following is sample data and example results based on that data.....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Product Code	Description	Qty Sold		No of Occ	Product Code	Description	Total Qty Sold
2	ID12345	Red Paint	10		2	ID12345	Red Paint	110
3	ID12346	Blue Paint	20		3	ID12346	Blue Paint	300
4	ID12347	Green Paint	30		1	ID12347	Green Paint	30
5	ID12348	Orange Paint	40		2	ID12348	Orange Paint	180
6	ID12349	Yellow Paint	50		2	ID12349	Yellow Paint	200
7	ID12350	Pink Paint	60		3	ID12350	Pink Paint	340
8	ID12351	Black Paint	70		2	ID12351	Black Paint	200
9	ID12352	White Paint	80		2	ID12352	White Paint	260
10	ID12353	Brown Paint	90		1	ID12353	Brown Paint	90
11	ID12345	Red Paint	100
12	ID12346	Blue Paint	110			PIVOT TABLE
13	ID12350	Pink Paint	120				Values
14	ID12351	Black Paint	130		Product Code	Description	Count of Qty Sold	Sum of Qty Sold2
15	ID12348	Orange Paint	140		ID12345	Red Paint	2	110
16	ID12349	Yellow Paint	150		ID12346	Blue Paint	3	300
17	ID12350	Pink Paint	160		ID12347	Green Paint	1	30
18	ID12346	Blue Paint	170		ID12348	Orange Paint	2	180
19	ID12352	White Paint	180		ID12349	Yellow Paint	2	200
20					ID12350	Pink Paint	3	340
21					ID12351	Black Paint	2	200
22					ID12352	White Paint	2	260
23					ID12353	Brown Paint	1	90
24					Grand Total		18	1710
25
Sheet7

The formula in F2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
All other formulas need to be copied down.
You will obviously need to change the cell references to suit your layout.

You can also get the same results by using a Pivot Table!

I hope this helps.

Ak

 

[markdoughty]

Thanks Guys....

Akashwani you have definitely got the right idea. I will take a look and come back to you on this.

Thanks Again....