Re-format & categorise product data in Excel

We are building an online catalogue of beauty products and require a person who is highly skilled in Excel to sort, re-format & categorise the data for import. The source data is a single Excel spreadsheet containing the beauty products of 10 major online retailers.

In total there are 61,361 rows of product information split as follows:

Retailer 1 (4872)
Retailer 2 (9799)
Retailer 3 (4440)
Retailer 4 (12167)
Retailer 5 (3614)
Retailer 6 (1678)
Retailer 7 (9602)
Retailer 8 (2465)
Retailer 9 (8394)
Retailer 10 (4330)

Considerations:

- Many of the retailers sell the same brands and products, so the same product appears many times through the source file.
- Some products have variants, such as different colours (swatches) or sizes (i.e. 50ml, 100ml, etc). Some retailers treat these are distinctly separate products, whilst others treat these as variants of the same product.
- 41,366 of the products have barcode numbers (EAN) which can be used as a unique identifier to find other instances of this product.
- The source data contains products for men, which need to be removed (usually the product name will include "men" or "homme")
- We estimate there are roughly 35-40,000 unique products.


Our output requirements are as follows:

- Single row for each product variant
- colour or size variants need to be listed as variants in the necessary column
- If the item is sold by multiple retailers, their information needs to be added to the necessary columns against that row rather than a row of its own
- Each product categorised against our category sheet (provided). The source data includes the category classification the retailer uses, which can be used as a guide.
- Product descriptions need to be encapsulated in HTML paragraph tags ( ). Some retailers have a noticeable pattern, such as 2 or 4 spaces in the product description to denote a new line/paragraph.


We have produced a template Excel file for you to use that demonstrates the format we require and includes some example data.

The columns in our template are as follows:

brand_name - The name of the brand in its correct format
brand_label - The name of the brand in lower case with spaces replaced with underscores ('_')
product_name - The name of the product, without the brand name or any additional information (i.e. size, suitable for, etc)
product_label - The name of the product as above, but in lower case with spaces replaced with underscores ('_')
category - Which product category does this product fit into (categories taken from the product_category spread
headline - If available from description text or from original product name, few words on product (i.e. 'suitable for dry skin'). No more than 60 characters
description - Product description text
variant_name - Text name of the variant (i.e. NW10, Blue, 100ml)
variant_label - The variant name as above but in lower case with spaces replaced with underscores ('_')
ean - the ean/isbn barcode number (if available, as provided in the source sheet)
mpn - the manufacturer product code/number (if available, as provided in the source sheet)

Then for each retailer we have the following columns:

buy_url - The URL to link to that specific product on that specific retailers website
merchant_image_url - The URL to the retailers image of that specific product
price - The price the retailer has set for that specific product
merchant_id - The retailers merchant number (same for all products from that retailers)
network_product_id - the unique reference number for that specific product from that specific retailer