# Inventory Sorting

#### Beerguy

##### New Member
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### navic

##### Board Regular
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
Code:
``=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)
Code:
``=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?

Replies
3
Views
229
Replies
3
Views
306
Replies
14
Views
829
Replies
6
Views
55
Replies
6
Views
216

Threads
1,108,525
Messages
5,523,377
Members
409,514
Latest member
MarkZuckerberg