dannyfromnj
New Member
- Joined
- Jun 4, 2006
- Messages
- 3
***Right click on the following link and "Save As" for an example copy of the spreadsheet discussed below***
http://72.41.176.97/ProductFormatExample001.xls
Hi everybody, hope you are all doing well...
I have a list of products which I need to bulk upload to my database. Each of these products are associated with multiple categories. My supplier is unable (or unwilling) to provide me with an organized list containing all the details necessary for me to upload these products to my database. So... I've resorted to going to his website, where I copying product details from each category. I paste this info into TextPad to clean it up a little, then copy again, and paste it into a spreadsheet system I devised.
This spreadsheet system is 5 sheets wide, some over 10,000 lines deep, each holding bits of information and variables such as category ID and other product related details. I've employed INDEX, HLOOKUP, CONCATENATE and other Excel formulas (see below) which when I paste product details, dynamically creates my product sheet fit for upload. I know absolutely nothing about VBA, and knew nothing of Excel formulas prior to being presented with this challenge. I have a technical background and a mechanically inclined thought process, so, I've made it this far.... but I've hit a brick wall and desperately need your help.
Point of focus is the sheet where all this information converges, more specifically, the sheet where the Product ID and Product Subcategory ID are populated (among other things). The subcategory ID is in this case only one of many. I don't know the other subcategories relating to a specific product until I paste another product list into the sheet system that contains a matching (duplicate) product code. Conditional formatting alerts me to the it's presence, at which point I run into that brick wall I mentioned.
What I need is some process that identifies each duplicate product code value, working top down, that will copy the subcategory value from the "prodcatl3" field in the first duplicate row found, merge it with a leading comma into the "prodcatl3" field of the row where the first instance of that specific product code occurred, then delete that duplicate row... start over, continue until no duplicates found.
I repeat, I know absolutely nothing about VBA in any way shape or form. To be perfectly honest, I'm barely able to join two basic Excel formulas, but trying very hard and learning every day. One thing I am fairly sure of is that VBA is the only way this is ever going to happen. That being said, just break it down for me, as long as you do that and are descriptive... this dog will hunt.
Quick note: I use semi-colon as a delimiter, not comma for formulas and such...
***Right click on the following link and "Save As" for an example copy of the spreadsheet detailed below***
http://72.41.176.97/ProductFormatExample001.xls
Sheet Names: Raw, Format, Constants, Catquery, Prodquery, ImageFileNames (In that order)
Below is specific column info and details for the sheet where magic needs to occur. Formulas given are as they appear in row 2 and may/ do change on subsequent rows depending on the formula.
Sheet Name: Format
Column Letter: A
Purpose: Main category description
Header Text: catdescription
Named Range: FormatcatdescriptionRange
Formula: CONCATENATE(RawModel;" ";RawProdCat)
Column: B
Purpose: Product Code
Header Text: ccode
Named Range: FormatcnameRange
Formula: Raw!G2
Conditional Format: COUNTIF(B:B;B7)>1
Notes: This is where a match, or duplicate would be identified.
Column: C
Purpose: Product name
Header Text: cname
Named Range: FormatcnameRange
Formula: Raw!E2
Column: D
Purpose: Product description
Header Text: cdescription
Named Range: FormatcdescriptionRange
Formula: Raw!F2
Column: E
Purpose: Product price
Header Text: cprice
Named Range: FormatcpriceRange
Formula: Raw!I2*(1+Constants!$B$8)+(Constants!$D$8)
Column: F
Purpose: Product main category
Header Text: ccategory
Named Range: FormatccategoryRange
Formula: IF(ISNA(HLOOKUP(Raw!D2;ConstantsLowercatArray;2;FALSE));"*"&"*"&Raw!D2&"*"&"*"&" Not Listed";(HLOOKUP(Raw!D2;ConstantsLowercatArray;2;FALSE)))
Column: G
Purpose: Product image file name
Header Text: cimageurl
Named Range: FormatcimageurlRange
Formula: ImageFileNames!A2
Column: H
Purpose: Product manufacture name
Header Text: mfg
Named Range: FormatmfgRange
Formula: Raw!A2
Column: I
Purpose: Holds Products subcategory ID's which need to be separated by a comma
Header Text: prodcatl3
Named Range: Formatprodcatl3Range
Formula: INDEX(CatQuerycategoryidRange;MATCH(FormatcatdescriptionRange;CatQuerycatdescriptionRange;0))
Notes: This is the field from which, the held value will need to be merged with the existing value of the first occurrence identified during the match comparison.
Thank you in advance... having people like you willing to share the knowlege is what keeps the rest of us going. Couldn't do it without you... hats off.
Danny
http://72.41.176.97/ProductFormatExample001.xls
Hi everybody, hope you are all doing well...
I have a list of products which I need to bulk upload to my database. Each of these products are associated with multiple categories. My supplier is unable (or unwilling) to provide me with an organized list containing all the details necessary for me to upload these products to my database. So... I've resorted to going to his website, where I copying product details from each category. I paste this info into TextPad to clean it up a little, then copy again, and paste it into a spreadsheet system I devised.
This spreadsheet system is 5 sheets wide, some over 10,000 lines deep, each holding bits of information and variables such as category ID and other product related details. I've employed INDEX, HLOOKUP, CONCATENATE and other Excel formulas (see below) which when I paste product details, dynamically creates my product sheet fit for upload. I know absolutely nothing about VBA, and knew nothing of Excel formulas prior to being presented with this challenge. I have a technical background and a mechanically inclined thought process, so, I've made it this far.... but I've hit a brick wall and desperately need your help.
Point of focus is the sheet where all this information converges, more specifically, the sheet where the Product ID and Product Subcategory ID are populated (among other things). The subcategory ID is in this case only one of many. I don't know the other subcategories relating to a specific product until I paste another product list into the sheet system that contains a matching (duplicate) product code. Conditional formatting alerts me to the it's presence, at which point I run into that brick wall I mentioned.
What I need is some process that identifies each duplicate product code value, working top down, that will copy the subcategory value from the "prodcatl3" field in the first duplicate row found, merge it with a leading comma into the "prodcatl3" field of the row where the first instance of that specific product code occurred, then delete that duplicate row... start over, continue until no duplicates found.
I repeat, I know absolutely nothing about VBA in any way shape or form. To be perfectly honest, I'm barely able to join two basic Excel formulas, but trying very hard and learning every day. One thing I am fairly sure of is that VBA is the only way this is ever going to happen. That being said, just break it down for me, as long as you do that and are descriptive... this dog will hunt.
Quick note: I use semi-colon as a delimiter, not comma for formulas and such...
***Right click on the following link and "Save As" for an example copy of the spreadsheet detailed below***
http://72.41.176.97/ProductFormatExample001.xls
Sheet Names: Raw, Format, Constants, Catquery, Prodquery, ImageFileNames (In that order)
Below is specific column info and details for the sheet where magic needs to occur. Formulas given are as they appear in row 2 and may/ do change on subsequent rows depending on the formula.
Sheet Name: Format
Column Letter: A
Purpose: Main category description
Header Text: catdescription
Named Range: FormatcatdescriptionRange
Formula: CONCATENATE(RawModel;" ";RawProdCat)
Column: B
Purpose: Product Code
Header Text: ccode
Named Range: FormatcnameRange
Formula: Raw!G2
Conditional Format: COUNTIF(B:B;B7)>1
Notes: This is where a match, or duplicate would be identified.
Column: C
Purpose: Product name
Header Text: cname
Named Range: FormatcnameRange
Formula: Raw!E2
Column: D
Purpose: Product description
Header Text: cdescription
Named Range: FormatcdescriptionRange
Formula: Raw!F2
Column: E
Purpose: Product price
Header Text: cprice
Named Range: FormatcpriceRange
Formula: Raw!I2*(1+Constants!$B$8)+(Constants!$D$8)
Column: F
Purpose: Product main category
Header Text: ccategory
Named Range: FormatccategoryRange
Formula: IF(ISNA(HLOOKUP(Raw!D2;ConstantsLowercatArray;2;FALSE));"*"&"*"&Raw!D2&"*"&"*"&" Not Listed";(HLOOKUP(Raw!D2;ConstantsLowercatArray;2;FALSE)))
Column: G
Purpose: Product image file name
Header Text: cimageurl
Named Range: FormatcimageurlRange
Formula: ImageFileNames!A2
Column: H
Purpose: Product manufacture name
Header Text: mfg
Named Range: FormatmfgRange
Formula: Raw!A2
Column: I
Purpose: Holds Products subcategory ID's which need to be separated by a comma
Header Text: prodcatl3
Named Range: Formatprodcatl3Range
Formula: INDEX(CatQuerycategoryidRange;MATCH(FormatcatdescriptionRange;CatQuerycatdescriptionRange;0))
Notes: This is the field from which, the held value will need to be merged with the existing value of the first occurrence identified during the match comparison.
Thank you in advance... having people like you willing to share the knowlege is what keeps the rest of us going. Couldn't do it without you... hats off.
Danny
