unique across three conditions, then sumif

[icestationzbra]

hi folks,

please help me with this.

	A	B	C	D	E	F
1	ID	Item	Quantity	Unit of Measure	Vials	Lab
2	1	B421009	1.5	g	1	lab1
3	2	B421009	1	g	1	lab1
4	3	B421009	1.2	g	2	lab2
5	4	B421009	1.2	g	2	lab3
6	5	B421009	100	mg	1	lab1
7	6	1421009	n/a	ea	1	lab1
8	7	1421009	n/a	ea	1	lab2
9	8	1421009	n/a	ea	1	lab3

this table above needs to be condensed to this format below:

	H	I	J	K	L
1	ID	Item	Quantity	Unit of Measure	Vials
2	1	B421009	100	mg	1
3	2	B421009	1	g	1
4	3	B421009	1.2	g	4
5	4	B421009	1.5	g	1
6	5	1421009	n/a	ea	3

if it presents some difficulty, then i am not particular about "g" coming before "mg" or otherwise, the "1" part number coming before "B" part number etc., as long as they are all nice grouped together (by part number). the quantities should be in an ascending order, as much as possible for easy deduction. rest of the logic is explained in the tables.

please let me know if you have any question.

formula and macro welcome.

do not want helper column, advanced filtering, pivot tables solutions.

[mikeTRON]

Why not use a pivot table?

[kelleytr]

It can be done without VBA, but of course with VBA also.

The easiest method is to begin by Advanced Filtering the following columns: Item, Quantity, Units of Measure.

Just in case you need a refresher, Advanced Filter is located in Data ribbon, then click "Advanced".

In the box that appears make sure to select "Copy to another location", then for the List Range manually drag your cursor over the range of beginning with Items through Unit of Measure (include headers).

The next part will be to create the Vials calc, this is done using a sumifs formula.
The sumifs formula I used looks like this: =SUMIFS($E$2:$E$9,$B$2:$B$9,I2,$C$2:$C$9,J2,$D$2:$D$9,K2)

You may need to update this to the cells that are relevant to you, either that or look at my attached workbooks as an example.

Finally, go back and add a simple formula that creates unique consecutive numbers for your "ID".

Hope this helps..

[mikeTRON]

Yeah that method works, but it is not something I would want to REdo. I still think a pivot table could work, wouldn't it?