Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a list of products in spreadsheet format.

The following is a simplified version of the spreadsheet:

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.

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:

  1. Some Skus may have multiple matches representing products with multiple options.
  2. Some Skus may have 0 matches.
  3. 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).


share|improve this question

closed as off topic by studiohack Feb 28 '13 at 16:46

Questions on Super User are expected to relate to computer software or computer hardware within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

Screenshot would help better, and this is Stackoverflow question. – Mowgli Feb 28 '13 at 16:11
Actually @Mowgli we deal with Excel spreadsheet questions here on SuperUser – Brad Patton Feb 28 '13 at 16:18
@BradPatton I thought he would be lot more help on Stackoverflow compare to here. – Mowgli Feb 28 '13 at 16:31