Split up quantity grouped items

[Alan Coleman]

Hello, can anyone help me with this little problem.

On the attached file, items can be entered one by one without qty. Th
Green table takes out duplicates then the purple table takes ou
spaces. Entering one by one makes it easier for loading vans becaus
you can check off each item. This method although is too slow fo
entering a lot of item with alot of qty. I'm having trouble writing th
lines to reverse the process. I want to enter data as the purple tabl
but then for it to be split up like the grey table. Is this possible t
be done with out a macro?

Thanks everyone who reads this

Attachment filename: excelhelp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=49476

 

[David McRitchie]

Hi Alan,
Count item per total of that of itemsitems em

You could simply sort your list on Item (column A)
Select Column A then use Ctrl+A to select all cells
You can probably use the sort toolbar icon, as it should
separate header if not then Data, Sort, ...

Col A is the Item
Col B is the Qty (per order)
Col C the # of # of ## counts
Col D the ## of # of ## counts
Col E total Qty of an item for all orders per truck

Code the following on line 2 (titles on row 1)
C2: =Countif($A$2:$A2,$A2)
D2: =Countif($A:$A,$A2)
E2: =IF(COUNTIF($A$2:$A2,$A2)=COUNTIF($A:$A,$A2),
SUMIF(A$2:A2,A2,B$2:B2),"")

The formulas will work whether you sorted on Column A
or not. For instance if you loaded the truck based
on destination, size, weight or other criteria.

You can see your example in
SUMIF Example
http://www.mvps.org/dmcritchie/excel/sumif.htm

 

[Debra Dalgleish]

Using your sample workbook:

Enter the following formulas:

In O2: =IF(Q1=R1,MAX($O$1:O1)+1,MAX($O$1:O1))
In P2: =IF(Q1=R1,INDEX($H$3:$H$8,O1+1),INDEX($H$3:$H$8,O1))
In Q2: =IF(P2=P1,Q1+1,1)
In R2: =VLOOKUP(P2,$H$3:$I$8,2,0)

Select cells O2:R2, and copy down as far as required. You could wrap the
formulas with IF formulas to prevent the #REF! errors.

Leave the heading cells blank, or insert another blank row at the top,
hide row 2 and insert the heading text in row 1