You are bidding on 2 excel data formatting automation spreadsheets. I think these can be done with excel macros and visual basic, but that’s beyond my skills. I have some complicated data formatting that needs to be done on csv files I download from our website. I hope to automate it so I don’t have to do so much copy and pasting. These are reformatting csv files I export from our wig sales website with volusion shopping cart, so I can upload the files back to the database. I need to format them as follows:
1st spreadsheet (see attached files [login to view URL], [login to view URL], [login to view URL])
I download columns productcode, catID, optionid, optiondesc (see [login to view URL])
Column k is just a copy of optionid with “-“ instead of “/” to fit the jpg filenames.
The rest of the columns are just html code to make some color choice charts with pictures.
I want all of these condensed into one field for each productcode (“AS_ANGELA” in sample file)
Attachment [login to view URL] is a sample of what I want in each product field. The <table> tag on top, and </table> tag on bottom must also be included, and the “<tr>” tag must be after every rows (column “M” in [login to view URL]).
So the code in [login to view URL] must then be placed in a column field called extinfo (see attachment [login to view URL]). Each product code has (10-50 options). So AS_ANGELA would be 1 row, with the “extinfo” field containing everything in 2.txt. Other productcodes would each have 1 row.
I can then upload the resulting spreadsheet back up to our web site database.
2nd Excel formatting project:
See attachment: [login to view URL]
This one has the column names: id,optioncatid,optionsdesc,applytoproductcodes,ArrangeOptionsBy
ID is the product option ID, optioncat is the product category, options desc is a description (usually a color number), applytoproductcodes is the product code the option is used for, and ArrangeOptionsby is the order the options for each product appear in the drop down menu on the website.
The problem is each product can have as many as 50 options, and each product has a different combination of options. With 20,000 products, there are over 400,000options which is straining our server. I need to condense it so each option applies to multiple product codes.
The applytoproductcodes field can have multiple product codes, but is limited to 255 characters. I need to reuse each ID for as many product codes such as “BJ_Amy” and “BJ_Charli” that will fit in 255 characters. For common colors such as 1 or 2, several option fields called 1 or 2, ect. will be needed. Excel will need to not go over 255 characters, and create as many options as needed to fit all the productcodes that use that color. The catID must stay the same. Some colors are rare and may only have a couple applytoproductcodes.
I will try to clarify it better if you have any questions, and I can send larger samples if needed.
Thank you!
Greetings, can do you task easily. Expert in writing macros in Excel. Let me know if you are interested. Can provide you sample macros of previously related projects.
This absolutely can be done with VBA (An Excel Macro) I have entensive experience with data manipulation and would be happy to get this done quickly and efficently...