Posts by SanDiego

    Re: $20 USD Script to convert daily XLS file, into usable Qty and Price upload.


    Quote from S M C;773138

    SanDiego, I am sorry, but short on time. Will leave this open to others.




    Thanks again.. Acutally it just occured to meI'm making this too dificult... I can just have my Amazon Template already filled in with the correct SKU and lead time, I just need the pricing and inventory to be empty, I can set forumlas up in those cells to pull in the inventory and pricing in dynamically from the daily generated file.. I'll just make sure to name the headers and columns and then save off the file and upload every time. As long as I save in the same directory and name the headers and rows. I think I don't acutally need to convert anything??

    I have an XLS file that is exported daily that shows the price and current quantity of all products and their variants (sizes)


    I want to convert this to a tab delimited Text file that can be uploaded to [email protected]


    The difficulty is the format that the current inventory management system exports the data in.


    I've included 2 files. (tried to include 4 but I can only do 2 at a time, so I combined all the XLS's into one file with 3 sheets, but they would all be separate files)


    sample-inv-qty.xlsx - includes a sample of what will be exported daily. This is only a small portion of the styles that will be exported. It is a much larger list and will consistently be adding and subtracting styles.


    sample-sku-convert.xlsx - This is a static file to translate the SKU's essentially the final file needs to contain the [email protected] SKU, not mine, [email protected]'s SKU does not exist in my inventory system so I'm assuming I will need an intermediary that will be able to translate them. Whenever we add new styles I will update this file.


    sample-final-file.xlsx - This is just a sample of what I'm assuming it will look like before saving as a text file. I do not need this to actually be created or saved.


    sample-final-file.txt This is a copy of what I'm hoping to get generated, a tab delimited text file suitable to upload to [email protected] for price and inventory updates.


    Column A should be the [email protected] SKU, Columns B,C, and D should be the price that is in column B of sample-inv-qty.xlsx column E should be the quantity pulled from the sample-inv-qty.xlsx file. column F should always be 4, Column G should always be blank


    Now, the tricky part, in the file that is exported (sample-inv-qty.xlsx) Each Row corresponds to a "parent" item. The quantities for each of the (Sizes) is represented in the columns. The first column is the local (Parent) SKU. To get the local SKU I add the size to the end of the parent SKU IE style B8861 in size 9 is actually SKU B8861-09


    There are two different groups of Parent items with 2 different size scales. The two Groups are U1 and W1.


    For all Rows that belong to Group U1, each of the Columns S2-S12 correspond to a Size. The Sizes are 03-13 so the final SKU's will end in -03 through -13 respectively.


    For all rows that belong to Group W1, the sizes are only size 5 - 11, and they are NOT in the same location as the U1 group. In this group, columns are only S3-S9, those columns correspond to sizes -05 through -11.


    So the S4 Column in the U1 Group would be Size -05 but column S4 in the W1 group would be size -06.


    I don't have a preference for how this is handled just hoping it's quick and easy so I can give it to someone else to process who is not familiar with excel, there will probably be up to 1500 rows in the final text file to be uploaded.


    I'm hoping to have software that will do this for me automatically in the near furture, but in the meantime i was hoping to come up with a band-aid for a while?


    I greatly appreciate it.


    THANKS!

    Re: Need to overhaul picklist; break out sizes into rows, correct columns sharing mea


    Quote from StephenR;770320

    Welcome to Ozgrid.


    I'm not sure what you're asking. You're more likely to get a fitting solution if you post a workbook with some sample data and an indication of your desired results.


    Hi Stephen, thanks for the welcome. I apologize if the charts I posted of my existing and desired spreadsheet layouts were not clear enough..... is there a way to upload an spreadsheet without code? that might be easier?


    Essentially, I have an inventory list of products.


    Currently the list is structured so that there is one "Product group" per row, and all sizes and inventory quantity of each size are indicated by which column the items are in and the number in the cel where the item row and size meet.


    I would like instead for each size of each product to exist on their own row with the quantity next to it.


    To complicate things, the columns mean different sizes for women's or men's. They are off by one, so a Men's Medium is a Women's Large.


    I'm hope someone could guide me on how to turn my current spreadsheet at the top of my original post into the preferred version with more rows and one correct size and quantity per row that does not have to be figured out by the "person" looking at the print out.


    I'm guessing I can do like an array, but I don't know how to do that and change the sizing, or if I should do it first or later. or even if that's the right path?

    Hey guys,


    I'm trying to re-organize my inventory report spreadsheet.


    It has two issues.


    1. The Inventory is listed one style per row, and each column represents a size, and the Quanity on hand of that size for that Item.


    I would like it to instead have a new row for each size clearly stating the size next to it, so there is not need to look up and trace down to find the size you're looking at.


    2. The columns share a location in the report, but they mean different sizes depending on Gender. All Female sizes are one up from Men's So A Mans item in the first row is an X-Small, but if it's a Womans item in the first row, it's a Small.
    This leads to lots of confusion, particularly on large lists where the items are towards the middle of the page.


    I'm hoping someone could give me a recommendation on the best strategy to take for this? I'm new to VB, i'm doing this for another department to help them do their job, so I don't have a budget or I would just hire someone so I'm trying to make this happen for them? I appreciate any help I can get.



    So, I've got this:


    Code
    ORD__Item______XSM(smf)_SMM(mdf)_MDM(lgf)____LGM(xlf)____XLM(2xlf)__GEN  <--- this column is the indicator what each columns size is MALE(female)
    101__redwidget_____0_______1__________0__________0___________0______MA
    102__Pinkwidget____0_______0__________2__________0___________0______FE
    102__whiteWidget___1_______0__________0__________1___________0______FE
    103__redwidget_____0_______1__________1__________0___________0______MA


    and I'm trying to get this

    Code
    ORD__Item_________Size____________Qty
    101__redwidget____Mens Small_______1
    102__Pinkwidget___Womens Large_____2
    102__whiteWidget__Womens Small_____1
    102__whiteWidget__Womens Large_____1
    103__redwidget____Mens Small_______1
    103__redwidget____Mens Medium______1