Using File Exchange to Streamline Variation Listing Creation

[shipscript]

Sellers who have manufacturer spreadsheets for item variations have asked for a more efficient process for adding a large number of variations to a listing. File Exchange would be the answer, except with sellers who have no image host.

So I thought I would try a 3-step process for a Single Variation Listing (only one droplist) to ease new sellers into File Exchange. Later I'll try tackling a Multi Variation Listing (several droplists):

A. Create a scheduled variation listing on eBay using the Advanced SYI listing form (not all categories will support variations):

1. Create a variation listing on eBay's SYI form using the Fixed Price format.
2. Add one or more "default" product photos to the listing.
3. Update all of the other listing fields.
4. Create a single variation from the first item in the spreadsheet (SKU, Quantity, Price, Name of the variation) and skip the variation photo.
   http://shipscript.com/ebayhelp/lister_hub/creating_ebay_variations.htm
5. Schedule the listing and post it to obtain an item number.

B. Return to the spreadsheet and modify the headings for File Exchange, eliminating columns that are not usable.

1. Here is my sample raw Spreadsheet Data that must be modified for File Exchange:
   

   Product Name        Quantity  Price  SKU
   TOP-10:Player #10      3      1.99   T10
   TOP-11:Player #11      1      1.99   T11
   TOP-12:Player #12      2      1.99   T12
   TOP-13:Player #13      6      1.99   T13
   TOP-14:Player #14      5      1.99   T14
   TOP-15:Player #15      4      1.99   T15
   TOP-16:Player #16      0      1.99   T16

2. The proper File Exchange headings will be:
   

   Action,ItemID,Relationship,RelationshipDetails,Quantity,StartPrice,CustomLabel

   The first data row under the heading will have the "Revise" Action and the ItemID from the existing scheduled listing. RelationshipDetails will be populated by concatenating the RelationshipDetails of all subsequent rows, and all other fields in that first data row will remain empty.
3. The second and subsequent data rows under the header row will have leave the "Action" and "ItemID" cells empty, but the Relationship will be "Variation".
4. The "RelationshipDetails" column will contain the name of the variation that was created on the listing form, which is "Card" in our example, followed by the droplist title (TOP-11:Player #11), formatted like this:
   

   Card=TOP-11:Player #11

5. Here is the populated File Exchange spreadsheet (formatted with improper spaces to help visualize):
   

   Action  ItemID      Relationship  RelationshipDetails      Quantity  StartPrice  CustomLabel
   Revise  383127989048              Card=TOP-10:Player #10;TOP-11:Player #11;TOP-12:Player #12;TOP-13:Player #13;TOP-14:Player #14;TOP-15:Player #15;TOP-16:Player #16;			
                        Variation    Card=TOP-10:Player #10      3        1.99        T10
                        Variation    Card=TOP-11:Player #11      1        1.99        T11
                        Variation    Card=TOP-12:Player #12      2        1.99        T12
                        Variation    Card=TOP-13:Player #13      6        1.99        T13
                        Variation    Card=TOP-14:Player #14      5        1.99        T14
                        Variation    Card=TOP-15:Player #15      4        1.99        T15
                        Variation    Card=TOP-16:Player #16      0        1.99        T16

6. Upload the new spreadsheet to File Exchange and verify Success in the Results Download file and verify the new droplist items were added to the listing.

C. Revise listing to manually add photos.

1. Revise the scheduled listing, click through the list of variations, and upload one or more photos for each variation if the variations are visibly different.
2. Change the scheduled date to start immediately to eliminate the scheduling fee (fee only applies to stores), or let the listing start at the scheduled time.
3. Save the revisions.

[panola_sports_cards]

Anyone needs help im will to give you some pointers on checklist sports card variations 

[shipscript]

One of the more tedious aspects of preparing a variation spreadsheet for File Exchange will be building the "RelationshipDetails" for the Master record. That field must contain every "RelationshipDetail" from each variation row.

For only a few variations, this can be done by hand. But for long lists, it would be best to automate, and Excel does have some tools to help.

Excel Spreadsheet functions

CONCATENATE
For shorter lists, use Excel's CONCATENATE (or CONCAT) function in the Master record cell, enter individual  cell numbers from the variation rows, and then place a quoted semicolon between each, as shown here:

=CONCATENATE(D3, D4, D5, D6, D7, D8, D9)
=CONCATENATE(D3,";",D4,";",D5,";",D6,";",D7,";",D8,";",D9)

After the cell has been assigned this formula, select the formula slot at the top of the screen and press the F9-key to convert the formula into a simple string, which will look like this:

TOP-10:Player #10;TOP-11:Player #11;TOP-12:Player #12;TOP-13:Player #13;TOP-14:Player #14;TOP-15:Player #15;TOP-16:Player #16

Add the name of the variation in front of the string of text.

Card=TOP-10:Player #10;TOP-11:Player #11;TOP-12:Player #12;TOP-13:Player #13;TOP-14:Player #14;TOP-15:Player #15;TOP-16:Player #16

TRANSPOSE

However, for long lists, the extra editing is pretty tedious. So use Excel's TRANSPOSE function, which turns a vertical column of data into a horizontal array.  Add the Transpose function to the RelationshipDetails cell of the Master record, and highlight the entire column of variation values for inclusion. The code looks like this:

=TRANSPOSE(D3:D9)

After the cell has been assigned this formula, select the formula slot at the top of the screen and press the F9-key to convert the formula into an array of values, which will look like this:

={TOP-10:Player #10,TOP-11:Player #11,TOP-12:Player #12,TOP-13:Player #13,TOP-14:Player #14,TOP-15:Player #15,TOP-16:Player #16}

That array can now be edited to remove the formula (remove the = sign and the curly braces) and to separate the values with semicolons instead of commas. The name of the Attribute can now be added to the front of that string:

Card=TOP-10:Player #10;TOP-11:Player #11;TOP-12:Player #12;TOP-13:Player #13;TOP-14:Player #14;TOP-15:Player #15;TOP-16:Player #16

FINISH EDITING

After the Master record cell has been built and it's formulas have been removed, we can now go into each variation and add the name of the attribute to each of the variation cells:

Card=TOP-10:Player #10
Card=TOP-11:Player #11
Card=TOP-12:Player #12

As one might guess, the variation name could reside in its own column and CONCATENATE could be used in a third column to combine that name with the variation values.

NOTE: Since the Master record uses special punctuation like the vertical pipe ( | ) and the semicolon ( ; ), those symbols should not be found in the variation values that will appear in a droplist (the RelationshipDetails).

[shipscript]

@panola_sports_cards 

If you have used File Exchange or a spreadsheet to create variation listings, or have suggestions for setting up the spreadsheet, please share your experiences and ideas.