D
dannyfromnj
***SEE ATTACHED FOR AN EXAMPLE COPY OF THE SPREADSHEET
DISCUSSED BELOW***
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...
***SEE ATTACHED FOR AN EXAMPLE COPY OF THE SPREADSHEET
DISCUSSED BELOW***
Sheet Names: Raw, Format, Constants, Catquery, Prodquery,
ImageFileNames (In that order)
Below is specific column info and details for the sheet where the 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
+-------------------------------------------------------------------+
|Filename: ProductFormatExample001.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4906 |
+-------------------------------------------------------------------+
DISCUSSED BELOW***
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 basicExcel 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...
***SEE ATTACHED FOR AN EXAMPLE COPY OF THE SPREADSHEET
DISCUSSED BELOW***
Sheet Names: Raw, Format, Constants, Catquery, Prodquery,
ImageFileNames (In that order)
Below is specific column info and details for the sheet where the 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
+-------------------------------------------------------------------+
|Filename: ProductFormatExample001.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4906 |
+-------------------------------------------------------------------+