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...


[TABLE="width: 1216"]
<tbody>[TR]
[TD]ITEM CODE
[/TD]
[TD]AMT PER U/M ARRAY 1 OF 6
[/TD]
[TD]AMT PER U/M ARRAY 2 OF 6
[/TD]
[TD]AMT PER U/M ARRAY 3 OF 6
[/TD]
[TD]AMT PER U/M ARRAY 4 OF 6
[/TD]
[TD]AMT PER U/M ARRAY 5 OF 6
[/TD]
[TD]AMT PER U/M ARRAY 6 OF 6
[/TD]
[TD]1ST U/M 1 OF 6
[/TD]
[TD]1ST U/M 2 OF 6
[/TD]
[TD]1ST U/M 3 OF 6
[/TD]
[TD]1ST U/M 4 OF 6
[/TD]
[TD]1ST U/M 5 OF 6
[/TD]
[TD]1ST U/M 6 OF 6
[/TD]
[TD]2ND U/M 1 OF 6
[/TD]
[TD]2ND U/M 2 OF 6
[/TD]
[TD]2ND U/M 3 OF 6
[/TD]
[TD]2ND U/M 4 OF 6
[/TD]
[TD]2ND U/M 5 OF 6
[/TD]
[TD]2ND U/M 6 OF 6
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]45
[/TD]
[TD]0.25
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]PC
[/TD]
[TD]PC
[/TD]
[TD]LB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EA
[/TD]
[TD]CT
[/TD]
[TD]PC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12.86
[/TD]
[TD]7
[/TD]
[TD]53.5
[/TD]
[TD]48
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]SF
[/TD]
[TD]PC
[/TD]
[TD]LB
[/TD]
[TD]CT
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]PA
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]94
[/TD]
[TD]40
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]LB
[/TD]
[TD]EA
[/TD]
[TD]PC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EA
[/TD]
[TD]PA
[/TD]
[TD]EA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]11
[/TD]
[TD]11
[/TD]
[TD]3.27
[/TD]
[TD]54
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]PC
[/TD]
[TD]SF
[/TD]
[TD]LB
[/TD]
[TD]CT
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]SF
[/TD]
[TD]PA
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]4.5
[/TD]
[TD]50
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]PC
[/TD]
[TD]LB
[/TD]
[TD]PC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EA
[/TD]
[TD]PC
[/TD]
[TD]PA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]15
[/TD]
[TD]15
[/TD]
[TD]21
[/TD]
[TD]36
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]SF
[/TD]
[TD]PC
[/TD]
[TD]LB
[/TD]
[TD]CT
[/TD]
[TD]PC
[/TD]
[TD][/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]PA
[/TD]
[TD]SH
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1
[/TD]
[TD]1.5
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]PC
[/TD]
[TD]LB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EA
[/TD]
[TD]PC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]4
[/TD]
[TD]42
[/TD]
[TD]36
[/TD]
[TD]1512
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]EA
[/TD]
[TD]LB
[/TD]
[TD]CT
[/TD]
[TD]LB
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]PA
[/TD]
[TD]PA
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]11.63
[/TD]
[TD]3
[/TD]
[TD]55.66
[/TD]
[TD]40
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]SF
[/TD]
[TD]PC
[/TD]
[TD]LB
[/TD]
[TD]CT
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]CT
[/TD]
[TD]PA
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]PC
[/TD]
[TD]LB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EA
[/TD]
[TD]PC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)


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


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.