Looking to update prices but not changing descriptions.

I want to change the prices but I don't want to change the description. i may change the weights but i tried using EZ populate but if i leave the desc blank it cleans it out. I want to upload an excel sheet with product name and number and price and that is it.

Paul Heisler:

I want to change the prices but I don't want to change the description. i may change the weights but i tried using EZ populate but if i leave the desc blank it cleans it out. I want to upload an excel sheet with product name and number and price and that is it.

Currently, Easy Populate will delete the description if the description is not included. It is a known issue that is also being worked on. Is there some reason that the description can not remain a part of the uploaded file?

I should clarify seeing that some information was not included by either of us. I was referring to version 4 of EP.

A further question though is what was meant by product number? EP4 requires the model number to correlate the input to the product.

mc12345678:

I should clarify seeing that some information was not included by either of us. I was referring to version 4 of EP.

A further question though is what was meant by product number? EP4 requires the model number to correlate the input to the product.

I am using ep4.0.22 beta 6-19-12

The description field is set up and does not change. the prices change yearly. the file i get from my vendor does not have a very good decription so i have to manually edit it and spend hours doing it i do not want to have to go through this every time i need to do a price change. The files are several hundred pieces in some case thousands.:D

Paul Heisler:

I am using ep4.0.22 beta 6-19-12

The description field is set up and does not change. the prices change yearly. the file i get from my vendor does not have a very good decription so i have to manually edit it and spend hours doing it i do not want to have to go through this every time i need to do a price change. The files are several hundred pieces in some case thousands.:D

Based on that I can understand the desire to reduce thetime involved. Is there a spreadsheet method that could simplify things? Ie, sort the new data, match it with the old, and "merge" the two where applicable.? I mean there are a number of ways to manipulate the data in the spreadsheet with minimal effort. This is not to say that the other issue couldn't / shouldn't be addressed.

mc12345678:

Based on that I can understand the desire to reduce thetime involved. Is there a spreadsheet method that could simplify things? Ie, sort the new data, match it with the old, and "merge" the two where applicable.? I mean there are a number of ways to manipulate the data in the spreadsheet with minimal effort. This is not to say that the other issue couldn't / shouldn't be addressed.
I have tried using excel macros to compare and adjust prices but that takes forever and usually ends up erroring out. If you know of a faster and easier way i am all ears i appreciate the input.

Paul Heisler:

I have tried using excel macros to compare and adjust prices but that takes forever and usually ends up erroring out. If you know of a faster and easier way i am all ears i appreciate the input.

Well, typically excel is not the suggested program to create the csv file, but regardless the thoughts I have would apply I think to any spreadsheet.

So as I understnd, there is a file that is provided that has some identifying information that can be matched with a similar record ideally thefield being used as the model number. From here I can think of a few different paths to go:can copy the columns of data from the new file into the upload file to not overwrite existing data, then sort both lists on the model number, add a column that uses an if statement such that is blank if both model numbers in a row are the same and not blank if they are different. As you scroll down through the list, either the left or the right side is moved down appropriately until all records are in one way or another covered. Another column can be created to identify which items should be copied from the new to the old based on an item existing in both sets of data. Then delet the rows that do not have data in boththe left and right sides. Then copy just the pertinent data from the new to the old.

Okay, so that seems like a lot to do, but is one way to keep track/manage the data movement. I thought there might be a sort of search function that could be used to look in a range of data (column) for a value and return the number of rows down or something from the top of the start of the range. This value could be placed in a column of it's own, then in a second new column if the result indicates that the value was found in both, the new value is listed in the cell otherwise the old value. Then this second new column is copied and paste special (values) in the old data.

Provided there is a unique model number for every item in the new data then the data will line up. There are ways to validate that simple assertion (pivot table for one), but like I said there are a number of ways to do the "comparison/setup. Hopefully helped to some extent. Of course again, ideally may just fix the description issue. :) last time. I looked at it, it wasn't that it was complicated, but that there's a few places to consider/dig into because of the design assertion that the description would basically always be included for particular upload types.

So, in simple excel terms:

Created a new column for prices that will be copied and then the values pasted over the existing prices in tht column I put the following code:

=IFERROR(VLOOKUP({cell with original model#},{entire table of new data},{column number of new price},FALSE),{cell with old price})

Where:
{cell with original model#} for example would be replaced in whole with a cell number such as A2.
{entire table of new data} would be in a format such as: Z$2::AC$5000 to include all of the new data that has 4 columns with 4999 items in. The new list and model number (or the value being used to compare) is in the far left column.
The column number is only a number not a cell reference. The number relates to the columns as a part of the table selected above. So, the column to the right of the first column would be column 2, the last column (right side) would be column 4.

The value of FALSE is used to return the associated value of the existing new entry, if the new data does not include the searched item, then an error is returned and through the above code, the default price is used; however, an alternative may be desired such as to disable the product or to delete it from the database (though I don't out-right recommend deletion as it will have an effect on historical lookup information).

Essentially then, the new price column would be copied and using paste special the values to replace the originals, delete any new/unnecessary information, then upload the prices to reflect the new prices of items and existing prices of items not in the new list.

This process does not specifically identify new items or the deletion of items, but a similar approach can be taken to identify those and to make other associated changes.

In open office, the vlookup command is also available; however, the iferror is not. So could be setup something like if(iserror({test}),{cell of old value},{test})

Where {test} in this case is the vlookup function from above/before.