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})),"")

[TABLE="width: 844"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Product[/TD]
[TD]QTY[/TD]
[TD]result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]helper K (specific case)[/TD]
[TD]helper L[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]BUSCH LIGHT 30 PK CAN[/TD]
[TD="align: right"]1191[/TD]
[TD="align: right"]1191[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BUD LIGHT 2/12/12 OZ CAN[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]MICH GOLDEN LT 24 PK CAN[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NG SPOTTED COW 4/6/12 CAN[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]COORS LIGHT 18/12 OZ CAN[/TD]
[TD="align: right"]543[/TD]
[TD="align: right"]543[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TWISTED TEA 12/24 OZ CAN[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]COORS LIGHT 24/16 OZ CAN[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]512[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]BUD LIGHT 2/12/12 OZ CAN[/TD]
[TD="align: right"]243[/TD]
[TD="align: right"]486[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]NG SPOTTED COW 4/6/12 CAN[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]480[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]TWISTED TEA 12/24 OZ CAN[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]972[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Did it help?