How to find maximum quantity sold with item description in the given data

[tawakal]

PRODUCT DESCRIPTION	QUANTITY	GROSS	DISCOUNT	TOTAL	RETURN QTY	RETURN VALUE	NET SALES QTY	NET SALES VALUE
COMPUTER MADE
AMBER LAWN COMPUTER (SECOND)	39.00	65,550.00	-	65,550.00			39.00	65,550.00
SWISS VOIL COMPUTER 3 PCS (SECOND)	41.00	105,950.00	-	105,950.00	30.00	75,000.00	11.00	30,950.00
AMBER LAWN COMPUTER 3PCS (FRESH)	1,128.00	2,925,694.95	23,688.00	2,902,006.95	40.00	97,110.00	1,088.00	2,804,896.95
SWISS VOIL COMPUTER 3PCS FRESH H.SAMPLE	4.00	13,400.00	-	13,400.00	8.00	24,600.00	(4.00)	(11,200.00)
CUT PCS_AMBER LAWN COMPUTER	41.50	12,450.00	-	12,450.00			41.50	12,450.00
CUT PCS_PLAIN CAMBRIC & LAWN	324.25	21,265.00	-	21,265.00			324.25	21,265.00
CUT PCS_PLAIN SUPREME VOIL	116.00	8,120.00	-	8,120.00			116.00	8,120.00
CUT PCS_SUPREME VOIL COMPUTER					250.75	25,075.00	(250.75)	(25,075.00)
SWISS VOIL COMPUTER 3PCS FRESH D#521	6.00	23,100.00	-	23,100.00			6.00	23,100.00
SWISS VOIL COMPUTER 3PCS FRESH D#981					1.00	3,182.00	(1.00)	(3,182.00)
SWISS VOIL COMPUTER 3PCS FRESH D#798	1.00	3,300.00	-	3,300.00			1.00	3,300.00
SWISS VOIL COMPUTER 3PCS FRESH D#920	1.00	2,700.00	-	2,700.00			1.00	2,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#949	2.00	6,700.00	-	6,700.00			2.00	6,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#962	2.00	6,700.00	-	6,700.00			2.00	6,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#972	2.00	6,700.00	-	6,700.00			2.00	6,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#108	1.00	3,300.00	-	3,300.00			1.00	3,300.00
SWISS VOIL COMPUTER 3PCS FRESH D#139	2.00	6,200.00	-	6,200.00	2.00	6,660.00	-	(460.00)
SWISS VOIL COMPUTER 3PCS FRESH D#183					1.00	3,182.00	(1.00)	(3,182.00)
SWISS VOIL COMPUTER 3PCS FRESH D#191					1.00	3,450.00	(1.00)	(3,450.00)
SWISS VOIL COMPUTER 3PCS FRESH D#186	4.00	13,900.00	-	13,900.00			4.00	13,900.00
SWISS VOIL COMPUTER 3PCS FRESH D#189	2.00	6,750.00	-	6,750.00			2.00	6,750.00
SWISS VOIL COMPUTER 3PCS FRESH D#196	24.00	85,295.00	2,432.00	82,863.00	1.00	3,182.00	23.00	79,681.00
SWISS VOIL COMPUTER 3PCS FRESH D#199	25.00	96,500.00	-	96,500.00			25.00	96,500.00
SWISS VOIL COMPUTER 3PCS FRESH D#232	10.00	37,500.00	-	37,500.00			10.00	37,500.00
SWISS VOIL COMPUTER 3PCS FRESH D#261	40.00	141,640.00	1,672.00	139,968.00	2.00	7,000.00	38.00	132,968.00
SWISS VOIL COMPUTER 3PCS FRESH D#238					1.00	3,263.00	(1.00)	(3,263.00)
SWISS VOIL COMPUTER 3PCS FRESH D#225	28.00	101,850.00	-	101,850.00			28.00	101,850.00
SWISS VOIL COMPUTER 3PCS FRESH D#244	5.00	17,250.00	-	17,250.00			5.00	17,250.00
SWISS VOIL COMPUTER 3PCS FRESH D#275	22.00	79,900.00	-	79,900.00			22.00	79,900.00
SWISS VOIL COMPUTER 3PCS FRESH D#276	31.00	112,900.00	-	112,900.00	2.00	7,100.00	29.00	105,800.00
SWISS VOIL COMPUTER 3PCS FRESH D#262	26.00	90,700.00	-	90,700.00			26.00	90,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#202	19.00	67,870.00	1,672.00	66,198.00	4.00	14,000.00	15.00	52,198.00
SWISS VOIL COMPUTER 3PCS FRESH D#215					6.00	20,106.00	(6.00)	(20,106.00)
SWISS VOIL COMPUTER 3PCS FRESH D#229					11.00	35,948.00	(11.00)	(35,948.00)
SWISS VOIL COMPUTER 3PCS FRESH D#208	1.00	2,700.00	-	2,700.00			1.00	2,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#209	10.00	34,400.00	-	34,400.00			10.00	34,400.00
SWISS VOIL COMPUTER 3PCS FRESH D#210	4.00	13,400.00	-	13,400.00			4.00	13,400.00
SWISS VOIL COMPUTER 3PCS FRESH D#211	1.00	3,300.00	-	3,300.00			1.00	3,300.00
SWISS VOIL COMPUTER 3PCS FRESH D#213	4.00	13,400.00	-	13,400.00			4.00	13,400.00
SWISS VOIL COMPUTER 3PCS FRESH D#221	2.00	6,700.00	-	6,700.00			2.00	6,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#222	1.00	3,300.00	-	3,300.00			1.00	3,300.00
SWISS VOIL COMPUTER 3PCS FRESH D#223	3.00	10,700.00	-	10,700.00			3.00	10,700.00
SWISS VOIL COMPUTER 3PCS FRESH D#219	40.00	148,000.00	-	148,000.00			40.00	148,000.00
SWISS VOIL COMPUTER 3PCS FRESH D#235	2.00	6,600.00	-	6,600.00			2.00	6,600.00
SWISS VOIL COMPUTER 3PCS FRESH D#255	6.00	21,300.00	-	21,300.00			6.00	21,300.00
SWISS VOIL COMPUTER 3PCS FRESH D#254	22.00	78,300.00	-	78,300.00			22.00	78,300.00
SWISS VOIL COMPUTER 3PCS FRESH D#242	8.00	35,800.00	-	35,800.00			8.00	35,800.00
SWISS VOIL COMPUTER 3PCS FRESH D#257	39.00	141,210.00	2,432.00	138,778.00			39.00	138,778.00
SWISS VOIL COMPUTER 3PCS FRESH D#243	8.00	36,000.00	-	36,000.00			8.00	36,000.00
SWISS VOIL COMPUTER 3PCS FRESH D#260	27.00	101,250.00	-	101,250.00			27.00	101,250.00
SWISS VOIL COMPUTER 3PCS FRESH D#274	24.00	85,290.00	1,672.00	83,618.00			24.00	83,618.00
SWISS VOIL COMPUTER 3PCS FRESH D#285	32.00	115,250.00	-	115,250.00			32.00	115,250.00
SWISS VOIL COMPUTER 3PCS FRESH D#283	12.00	42,550.00	-	42,550.00			12.00	42,550.00
SWISS VOIL COMPUTER 3PCS FRESH D#282	39.00	136,840.00	760.00	136,080.00			39.00	136,080.00
SWISS VOIL COMPUTER 3PCS FRESH D#284	15.00	53,950.00	-	53,950.00			15.00	53,950.00
SWISS VOIL COMPUTER 3PCS FRESH D#281	80.00	283,620.00	3,344.00	280,276.00	6.00	21,000.00	74.00	259,276.00
SWISS VOIL COMPUTER 3PCS FRESH D#289	80.00	292,370.00	1,672.00	290,698.00			80.00	290,698.00
SWISS VOIL COMPUTER 3PCS FRESH D#290	23.00	82,200.00	1,672.00	80,528.00			23.00	80,528.00
SWISS VOIL COMPUTER 3PCS FRESH D#298	19.00	67,250.00	-	67,250.00	2.00	7,000.00	17.00	60,250.00
SWISS VOIL COMPUTER 3PCS FRESH D#293	17.00	64,350.00	-	64,350.00			17.00	64,350.00
SWISS VOIL COMPUTER 3PCS FRESH D#291	18.00	69,500.00	-	69,500.00			18.00	69,500.00
SWISS VOIL COMPUTER 3PCS FRESH D#300	20.00	71,900.00	-	71,900.00			20.00	71,900.00
SWISS VOIL COMPUTER 3PCS FRESH D#301	18.00	62,800.00	-	62,800.00			18.00	62,800.00
SWISS VOIL COMPUTER 3PCS FRESH D#295	1.00	3,550.00	-	3,550.00			1.00	3,550.00
COMPUTER MADE Total	2,522.75	6,150,964.95	41,016.00	6,109,948.95	368.75	356,858.00	2,154.00	5,753,090.95
ZAM ZAM MADE
GOLDEN WHITE ALL OVER (FRESH)	13.50	3,645.00	-	3,645.00			13.50	3,645.00
AMBER LAWN SHIFLY SHIRT (SECOND)	68.75	12,991.00	-	12,991.00			68.75	12,991.00
AMBER LAWN SHIFLY SHILWAR (SECOND)	62.50	7,199.00	-	7,199.00			62.50	7,199.00
AMBER LAWN SHIFLY DUPATTAH (SECOND)	68.75	12,409.98	-	12,409.98			68.75	12,409.98
AMBER LAWN SHIFLY SINGLE SHIRT (FRESH)	361.25	107,343.75	-	107,343.75			361.25	107,343.75
LAWN SHIFLY CHICKEN KARI (SECOND)	6.00	12,600.00	-	12,600.00			6.00	12,600.00
AMBER LAWN SHIFLY SHIRT (FRESH)	1,302.25	391,065.50	-	391,065.50	19.25	5,142.50	1,283.00	385,923.00
AMBER LAWN SHIFLY DUPATTAH (FRESH)	1,299.25	376,913.10	-	376,913.10	19.25	4,869.86	1,280.00	372,043.24
AMBER LAWN SHIFLY SHILWAR (FRESH)	1,187.50	150,912.50	-	150,912.50	17.50	2,012.50	1,170.00	148,900.00
PLAIN TABOKE LAWN BARAPANNA (FRESH)	354.00	31,860.00	-	31,860.00			354.00	31,860.00
PLAIN AMBER LAWN (FRESH)	1,278.50	222,604.50	-	222,604.50	104.50	13,062.50	1,174.00	209,542.00
PLAIN SUPREME VOIL BARA PANNA	1,334.75	139,362.50	-	139,362.50			1,334.75	139,362.50
PLAIN CAMBRIC (FRESH)	7,251.50	1,034,931.48	-	1,034,931.48	480.00	68,212.50	6,771.50	966,718.98
PLAIN CAMBRIC & LAWN (SECOND)	290.50	23,240.00	-	23,240.00			290.50	23,240.00
PRINT CAMBRIC WHITE, BLACK, BLUE	5,972.75	1,027,558.45	-	1,027,558.45	200.00	34,700.00	5,772.75	992,858.45
PLAIN SUPREME VOIL_FRAME PACKING	112.00	25,759.90	-	25,759.90			112.00	25,759.90
AMBER LAWN SHIFLI CHICKEN KARI (FRESH)	86.00	227,300.00	-	227,300.00	37.00	92,700.00	49.00	134,600.00
PRINT CAMBRIC WHITE, BLACK, BLUE (SECOND)	439.00	52,680.00	-	52,680.00			439.00	52,680.00
GOLDEN WHITE BORDER (FRESH)	38.75	9,737.50	-	9,737.50	38.50	10,982.50	0.25	(1,245.00)
LAWN SHIFLY EMB SINGLE DUPATTAH (FRESH)	36.00	4,339.90	-	4,339.90			36.00	4,339.90
PRINT CAMBRIC WHITE, ( CUT PIECE)	4.75	332.50	-	332.50			4.75	332.50
LAWN SHIFFLI CHICKEN KARI SINGLE SHIRT ( FRESH )	14.00	17,150.00	-	17,150.00	27.00	27,648.00	(13.00)	(10,498.00)
LAWN SHIFFLI CHICKEN KARI SINGLE SHIRT ( SECOND )	82.00	82,195.00	-	82,195.00	10.00	10,000.00	72.00	72,195.00
PRINT SWISS VOILE ( FRAME PACKING ) SECOND	20.00	2,400.00	-	2,400.00			20.00	2,400.00
LAWN SHIFFLI CHICKEN KARI ( STITCHED PES )	117.00	193,050.00	-	193,050.00			117.00	193,050.00
ZAM ZAM MADE Total	21,801.25	4,169,581.56	-	4,169,581.56	953.00	269,330.36	20,848.25	3,900,251.20
GRAND TOTAL	24,324.00	10,320,546.51	41,016.00	10,279,530.51	1,321.75	626,188.36	23,002.25	9,653,342.15

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[igold]

Hi tawakal,

Welcome to the MrExcel Forum.

You may have to be a little more precise in your requirement. I have looked at your data and there are no duplicate (similar but not duplicate) Product Descriptions. Therefore the maximum sold is the quantity listed.

If you want to find the max sold within a group of similar Product Descriptions, please provide us the criteria we should use to group the similar descriptions.

 

[Toadstool]

tawakal,

As igold said it would help if you provided more guidance but let me make some major assumptions and see how close I am...

Option 1
The simplest way would be to select the given data then use Data, Sort to sort highest to lowest. You may see some Totals and Grand Totals but then you'd see your highest selling Product Description.

Option 2
Let me try another assumption with a formula solution: You want to see the highest selling for a product category (COMPUTER MADE or ZAM ZAM MADE. COMPUTER MADE category quantities run from cell B4 down to B67 so I could find an unused cell and enter into it
=MAX(B4:B67) to get the maximum quantity of 1128.
Now I find another unused cell and enter
=INDEX(A4:A67,MATCH(MAX(B4:B67),B4:B67,0)) to get the description of that maximum 1128 sale.
If you want to do the same for ZAM ZAM MADE then you'd use the cell range A72:A96 and B72:B96

Option 3
Now let me make a huge assumption. This appears to be from a computer generated report, so the number of rows for a category will change as products are added/removed and you may even add a category if you start selling another range (e.g. OMKAR MADE). I assume you want to just take your latest computer generated report and paste it into an Excel worksheet (let me call it "MyData" for example) and have Excel answer your question.
I will further assume that you want the maximum quantity sold for each category, that if two products in the same category have the same quantity sold then you're OK with just seeing the first found.

Here is my suggested solution and I know I could have combined some columns into the formulae but I wanted to make it easier to see what's happening. I'm not repeating your data as I'm just assuming it's in a sheet called MyData and starts at cell A1

I'm also using an Excel table so if you add another Product Category it automatically copies down the formulae.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Product Category	Description Starts	Description Ends	Range Desc	Quantity Starts	Quantity Ends	Range Qty	Max Quantity	Max Description			1. PRODUCT DESCRIPTION
2	COMPUTER MADE	$A$4	$A$67	MyData!$A$4:$A$67	$B$4	$B$67	MyData!$B$4:$B$67	1128	AMBER LAWN COMPUTER 3PCS (FRESH)			MyData!
3	ZAM ZAM MADE	$A$72	$A$96	MyData!$A$72:$A$96	$B$72	$B$96	MyData!$B$72:$B$96	7251.5	PLAIN CAMBRIC (FRESH)

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B2 =ADDRESS(MATCH(A2,MyData!A:A,0)+1,1) C2 =ADDRESS(MATCH(A2&" Total",MyData!A:A,0)-1,1) D2 =$L$2&B2&":"&C2 E2 =SUBSTITUTE(B2,"$A","$B") F2 =SUBSTITUTE(C2,"$A","$B") G2 =$L$2&E2&":"&F2 H2 =MAX(INDIRECT(G2)) I2 =INDEX(INDIRECT(D2),MATCH(H2,INDIRECT(G2),0)) L1 =MyData!A1 L2 =MID(FORMULATEXT(L1),2,(SEARCH("!",FORMULATEXT(L1))-1)) <tbody> </tbody>

<tbody>
</tbody>

Let me explain:

• L1 just points to the data sheet cell A1 so that L2 can extract the sheet name (in case anybody renames it).
• B and C2 get the addresses to search by looking for this Product Category in the data and adding 1 so it gets past the heading. C2 does the same for Product Category "Total" so it find the last row and subtracts 1 to avoid searching the totals row.
• D2 wraps those start/end addresses around ":" to get the range.
• E2, F2 and G2 do the same for the Quantity column.
• H2 searches the quantity range for the product category and returns the maximum from the "QUANTITY" column.
• I2 INDEXes into the PRODUCT DESCRIPTION retrieving it when a match is found in column B with the max quantity.

So how close was I?

Regards,
Toadstool