Price Book Update Using AGD Mass Import/Export Tool

Price Book Update Using AGD Mass Import/Export Tool

Steps to Complete Export and Import

First, if the Documation Price Book you are using is new, the Product Codes need to be updated to Convert to Number with the green arrow throughout the Workbook.

Then save the column after as GENERAL.

Once you have converted the Price Book, resave it for reference.

Go to Agent Dealer:

1. Choose the Price Book you need to work on.
2. Export price book you are working on from the List View in AGD using the Price Book List and the tool.
3. Open Excel workbook.
4. Save file as a backup copy; then re-Save file as one to edit for new numbers and import-different names
5. Change the Manufacturer Code and Product Code columns to GENERAL format and right justified.   NOTE: there are two separate sheets for Ricoh price books, the HW and the PS sheets
6. At Cost column, do VLOOKUP formula
   1. Will do one VLOOKUP from the Professional Services tab on the price book being worked then
   2. Will do another VLOOKUP from the Hardware tab on the price book being worked
      • Lookup Value = F2  (manufacturer code column)
      • Table Array = (manuf code on price book file, highlight entire column)
      • Col Index Num = (highlight the # of columns over to the column with the price you want)   ex:  6
      • Range Lookup = False
7. Copy the formula down the entire cost column using the “+” sign on the corner of the cell
8. Sort the cost column by the cost column to group all costs and blanks together
9. Mark ACTIVE = TRUE for any items that come back with a cost
10. Copy any positive cost returns to the Rep Cost column also
11. Add in the start and end dates for the price book for TRUE cost line items
12. Do a quality check on the data to confirm it is pulling the correct information
13. Clean up the cost column
14. For the PS costs, then copy these lines over to a blank sheet to hold them while you work on the HW sheet
15. Do the same process for the HW costs
16. Sort the data, mark as TRUE, do a quality check, clean up data
17. For blank costs, put FALSE and put 0.01 under Rep Cost
18. For blank items that are FALSE, and the end date in both the start/end date columns
19. Don’t forget to copy back the PS lines that are on the blank sheet and re-sort the sheet
20. Do some quality checks on the False items
21. Do not change any of the Active True/False items for the item itself, only for cost