Inventory Sorting

Beerguy

New Member
Joined
Dec 30, 2015
Messages
14
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
 

Some videos you may like

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
Joined
Jun 14, 2015
Messages
249
Office Version
  1. 2013
Platform
  1. Windows
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})),"")

ABCDEFGHIJKL
1ProductQTYresulthelper K (specific case)helper L
2BUSCH LIGHT 30 PK CAN11911191BUD LIGHT 2/12/12 OZ CAN2
3MICH GOLDEN LT 24 PK CAN600600NG SPOTTED COW 4/6/12 CAN4
4COORS LIGHT 18/12 OZ CAN543543TWISTED TEA 12/24 OZ CAN12
5COORS LIGHT 24/16 OZ CAN512512
6BUD LIGHT 2/12/12 OZ CAN243486
7NG SPOTTED COW 4/6/12 CAN120480
8TWISTED TEA 12/24 OZ CAN81972

<tbody>
</tbody>


Did it help?
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top