Hi,
I'm preparing a CSV file for a web upload. I have thousands of products and I'm attempting to do this using VBA before I hit it manually. The CSV is a file full of products, SKUs and their attributes such as color and size. Below is a sample:
A B C D E
SKU01 T-Shirt 1 Black S T-Shirt 1 Black S
SKU02 T-Shirt 1 Black M T-Shirt 1 Black M
SKU03 T-Shirt 1 Black L T-Shirt 1 Black L
SKU04 Running Gloves 1 Red M Running Gl Red M
SKU05 Running Gloves 1 Red L Running Gl Red L
SKU06 Running Gloves 1 Grey M Running Gl Grey M
SKU07 Running Gloves 1 Grey L Running Gl Grey L
SKU08 Wooly Hat Wooly Hat
SKU09 Running Socks 1 White S Running So White S
SKU10 Running Socks 1 White M Running So White M
Column A is the SKU, B the Description, C is the first 10 characters of the description, D and E are the product attributes which have been extracted from the description. I basically need the VBA script to generate the following:
A B C D E
SKU01CON T-Shirt 1
SKU01 T-Shirt 1 Black S T-Shirt 1 Black S
SKU02 T-Shirt 1 Black M T-Shirt 1 Black M
SKU03 T-Shirt 1 Black L T-Shirt 1 Black L
SKU04CON Running Gloves 1
SKU04 Running Gloves 1 Red M Running Gl Red M
SKU05 Running Gloves 1 Red L Running Gl Red L
SKU06 Running Gloves 1 Grey M Running Gl Black M
SKU07 Running Gloves 1 Grey L Running Gl Black L
You can see that there have been two additional entries made, this is what I need. The additional product entries group the similar products together.
This is the way I was looking at achieving it:
Move through each row, IF columns D or E <> "" AND column C is <> to the cell above then insert a new row.
Copy the contents of A to the newly generated row whilst concatenating it with "CON"
Copy the contents of B to the newly generated row whilst removing the attributes in D and/or E (with spaces before)
Firstly, would you agree? It's not a solid plan. Secondly, how do I do this in VBA?
I have 7000 product entries to work through, I'm hoping to avoid any manual work. Thanks for your help!
Nick
Q-28372723.xlsm
Open it, then run the macro for FixCSV.
The code itself is:
Open in new window