I have a list of products in spreadsheet format.
The following is a simplified version of the spreadsheet:
sku,name,price,description 1) ABC,Abc gizmo,12,Buy this Abc gizmo 2) ABCG,Abc gizmo Green,13,Buy this Abc gizmo 3) ABCR,Abc gizmo Red,14,Buy this Abc gizmo
Goal: Merge product 2 and 3 into a new column on product 1. Our website will then display products 2 and 3 as custom drop down menu options of product 1. Product 1 will be the base product, and its options would be: 'Green' and 'Red'
Here is a sample of the format I need to end up with, but I'm not entirely sure how to get there.
sku,name,price,description,custom_column ABC,Abc gizmo,12,Buy this Abc gizmo,G $13:fixed:1:ABCG|R $14:fixed:1:ABCR
The breakdown of
custom_column is as follows:
[name of custom value]:[fixed|percent]:[price of option]:[sku] [name of custom value] = Text, Sku's suffix + full price of option [fixed|percent] = Text, either "fixed" or "percent" this represents whether the option's price is fixed or a percent of the main product price. [price of option] = Number, [option's price]-[base product's price] in example above line 2 price was 13, after subtraction it should be 1. [sku] = Text, the full sku found at the beginning of line 2 and 3.
The spreadsheet has many products (1000's) and many of them can be reformatted into custom options of a base product. The Sku would be the determiner.
By eyeballing the spreadsheet it seems that the Skus have the following pattern:
MN123456XYZ = [1-2 characters][followed by 6 digits][followed by 1-3 characters]
A few other things to consider:
- Some Skus may have multiple matches representing products with multiple options.
- Some Skus may have 0 matches.
- Some Skus with 0 matches may still have a suffix in the SKU.
The simplest way would be best, I'm not an excellent excel user (pun).