Inventory Sorting

[Beerguy]

Hey everyone!

I have a spread sheet that comes from a 3rd party and it lists ALL the beer packages in a particular store and how many CASES of each were sold. I need a formula that will break the cases into units, but only when necessary. For example:

COLUMN A COLUMN B

BUSCH LIGHT 30 PK CAN 1,191
MICH GOLDEN LT 24 PK CAN 600
COORS LIGHT 18/12 OZ CAN 543
COORS LIGHT 24/16 OZ CAN 512
BUD LIGHT 2/12/12 OZ CAN 243
NG SPOTTED COW 4/6/12 CAN 120
TWISTED TEA 12/24 OZ CAN 81

If the above information was given, it means I sold 81 cases of Twisted Tea 24 ounce cans and 120 cases of Spotted Cow, etc. I need a formula that will take (12/24) and multiply it by 12, (4/6) and multiply it by 4, (2/12) by 12. But, since the 24/16, 18/12, 24 PK and 30 PK are considered cases, I need those left alone. So, Column C would look like this:

Column C
1,191
600
543
512
486 <-- 2/12
480 <-- 4/6
972 <-- 12/24

Can someone please figure that out?

Thank you very much in advance for your help in this matter. This would save me a lot of time manually calculating this out.

Beerguy

 

[navic]

This would save me a lot of time manually calculating this out.

If your data start in 'A2' cell and Cell range 'K2:L8' are helper.
Try this formula in 'C' column

=IF(IFERROR(VLOOKUP(A2,$K$2:$K$8,1,FALSE),B2)=A2,VLOOKUP(A2,$K$2:$L$8,2,FALSE)*B2,IFERROR(VLOOKUP(A2,$K$2:$K$8,1,FALSE),B2))

Helper columns
In 'K2' cell put specific case
In 'L2' cell put this formula (copy down)

=IFERROR(LOOKUP(10^10,--RIGHT(LEFT(K2,FIND("/",K2)-1),{1,2})),"")

	A	B	C	D	E	F	G	H	I	J	K	L
1	Product	QTY	result								helper K (specific case)	helper L
2	BUSCH LIGHT 30 PK CAN	1191	1191								BUD LIGHT 2/12/12 OZ CAN	2
3	MICH GOLDEN LT 24 PK CAN	600	600								NG SPOTTED COW 4/6/12 CAN	4
4	COORS LIGHT 18/12 OZ CAN	543	543								TWISTED TEA 12/24 OZ CAN	12
5	COORS LIGHT 24/16 OZ CAN	512	512
6	BUD LIGHT 2/12/12 OZ CAN	243	486
7	NG SPOTTED COW 4/6/12 CAN	120	480
8	TWISTED TEA 12/24 OZ CAN	81	972

<tbody>
</tbody>

Did it help?