Convert Unit of Measure

[Seanlasser]

Hello


I need some help with either a formula or VBA macro to solve a conversion issue in a packaging table for tile. (Sample below) The challenge ahead of me is to convert all sales data to the same unit of measure so I can rank the number of pieces sold. the issue that makes this complicated is that there is a packaging table which affects all items, in total there are more than 150,000 lines and in the packaging table and items are not uniformly listed with conversion factors. This table is years of accumulation of many peoples efforts, therefore lies the struggle. for example, as you can see form the section below item 1 list the weight first and item 2 list the SF/CT and item 4 list PC/CT. the way you read this table is first the number factor (1of6) and then look for the units that match that number 1st U/M 1/6 / 2nd U/M 1/6 to get the full ratio. To get a full picture of how an item equates to other you would follow that logic thru 2nd, 3rd, etc. conversion factors. furthermore if a 12x24 tile is 2 SF per PC and a 4x4 tile is 0.111 SF per PC if I sold 10 SF of each item I really would have sold 5 PC of the tile that is 12x24 and 90 PCs of the one that is 4x4, so even though the total volume is the same the smaller tile is way more popular and I should have a lot more of them on hand...

ITEM CODE	AMT PER U/M ARRAY 1 OF 6	AMT PER U/M ARRAY 2 OF 6	AMT PER U/M ARRAY 3 OF 6	AMT PER U/M ARRAY 4 OF 6	AMT PER U/M ARRAY 5 OF 6	AMT PER U/M ARRAY 6 OF 6	1ST U/M 1 OF 6	1ST U/M 2 OF 6	1ST U/M 3 OF 6	1ST U/M 4 OF 6	1ST U/M 5 OF 6	1ST U/M 6 OF 6	2ND U/M 1 OF 6	2ND U/M 2 OF 6	2ND U/M 3 OF 6	2ND U/M 4 OF 6	2ND U/M 5 OF 6	2ND U/M 6 OF 6
1	1	45	0.25	0	0	0	PC	PC	LB				EA	CT	PC
2	12.86	7	53.5	48	0	0	SF	PC	LB	CT			CT	CT	CT	PA
3	94	40	1	0	0	0	LB	EA	PC				EA	PA	EA
4	11	11	3.27	54	0	0	PC	SF	LB	CT			CT	CT	SF	PA
5	1	4.5	50	0	0	0	PC	LB	PC				EA	PC	PA
6	15	15	21	36	1	0	SF	PC	LB	CT	PC		CT	CT	CT	PA	SH
7	1	1.5	0	0	0	0	PC	LB					EA	PC
8	4	42	36	1512	0	0	EA	LB	CT	LB			CT	CT	PA	PA
9	11.63	3	55.66	40	0	0	SF	PC	LB	CT			CT	CT	CT	PA
10	1	4	0	0	0	0	PC	LB					EA	PC

<tbody>
</tbody>

Above is the packaging table as you can see there are 6 options for each item. SF= square feet, PC=piece, EA= each, PA=pallet, ETC...1 is always the weight (LB) and I am not concerned with that at this time. Ideally I would like to be able to apply some formula or VBA to the sales numbers below so that all items are represented in number of PC (pieces) from column 'J' (QTY Sold)

ITEM#	LOT#	ITEM DESCRIPTION	ITEM DESCRIPTION 2	WAREHOUSE	SALES FROM	SALES TO	DURATION	Wks/Mths	Qty Sold	Qty Returned	UOM	# OF INVOICES	AVERAGE USAGE	Per Wk or Mth	AVG # OF INVOICES	HIGHEST QTY SOLD	ACTIVITY QTY	Per Wk or Mth2	QTY ONHAND	UOM3	QTY ALLOCATED	QTY AVAILABLE	QTY SCHEDULED	NET AVAILABLE
1				SAM	10117	110317	43.29	WK	0	0	SH	0	0	WK	0	0	0	0	0	SH	0	0	0	0
2				SAM	10117	110317	43.29	WK	12	0	PC	4	0.28	WK	0	5	12	0.28	0	PC	0	0	0	0
3				SAM	10117	110317	43.29	WK	32	0	PC	7	0.74	WK	0	12	32	0.74	0	PC	0	0	0	0
4				SAM	10117	110317	43.29	WK	24	0	SH	5	0.55	WK	0	11	24	0.55	0	SH	0	0	0	0
5				SAM	10117	110317	43.29	WK	23	0	SH	4	0.53	WK	0	13	23	0.53	2	SH	0	2	0	2
6				SAM	10117	110317	43.29	WK	149	0	PC	57	3.44	WK	1	24	146	3.37	1	PC	0	1	160	161
7				SAM	10117	110317	43.29	WK	53.46	0	SF	40	1.24	WK	0	19.56	92.58	2.14	15.19	SF	0	15.19	0	15.19
8	S001			SAM	10117	110317	43.29	WK	9.78	0	SF	1	0.23	WK	0	9.78	9.78	0.23	0	SF	0	0	0	0
9				SAM	10117	110317	43.29	WK	61.34	0	SF	68	1.42	WK	1	9.78	81.38	1.88	12.97	SF	0.48	12.49	0	12.49
10	S001			SAM	10117	110317	43.29	WK	9.78	0	SF	1	0.23	WK	0	9.78	9.78	0.23	0	SF	0	0	0	0
11				SAM	10117	110317	43.29	WK	76.04	0	SF	70	1.76	WK	1	19.56	95.6	2.21	10.44	SF	0	10.44	0	10.44
12	S002			SAM	10117	110317	43.29	WK	9.78	0	SF	1	0.23	WK	0	9.78	9.78	0.23	0	SF	0	0	0	0
13				SAM	10117	110317	43.29	WK	57.69	0	SF	76	1.33	WK	1	9.78	77.25	1.78	19.12	SF	0	19.12	0	19.12
14	S002			SAM	10117	110317	43.29	WK	78.24	0	SF	1	1.81	WK	0	78.24	78.24	1.81	0	SF	0	0	0	0
15				SAM	10117	110317	43.29	WK	37	0	PC	11	0.85	WK	0	13	37	0.85	37	PC	0	37	0	37

<tbody>
</tbody>

I would be happy to fill in anything that I forgot or answer any questions for information that isn't clear.

Thank you in advance for your help.