Excel 問題 RRS feed

  • 問題

  • 本人是在綱購生意的分析員, 需要每天download 3 個 很大spreadsheet from service provider- TMALL , Cai Niao (logistic provider) and payment - Ailpay , 超過3 M 一個file, over 7000 line per file, 這3 個 file只有 Order Number是唯一可以在3 個 file找到相同的, 其他分別裝置不同的資料

    (1) Order File  (main product code and sales data)

    (2) Order Logistic File ( order movement , delivery cost,  tax cost , )

    (3) Order Payment File ( payment status , amount )

    Because (2) order logistic file , will create many line with same order number due to the order movement changing. ie. depart warehouse, during transit , in custom , etc. and sometime , we can receive data in payment file but can not find that order number in (1) and (2) ,

    Purpose for these file matching,

    (1) for recognise the payment vs bank received data , therefore I need to vlookup and transform the data  from text to value  from these 3 files, ensure these data can find in each files , ensure service provider are not given a wrong data to us.

    (2) extract the delivery cost and tax cost from logistic file  vs our accural budget, becasue we have signed agreement with service provider in X% of these cost, so we need to check whether these % is corrected.

    (3) we will upload payment file to SAP  after the data confirm that is corrected, ensure recognise the sales in our own ERP..

    My question :

    any other excel function can simpify the steps?

    ie. download a csv file  then transform the data to value, then vlookup 3 files in separate spreadsheet , if any error find the solution

    then create a spreadsheet to delivery cost and tax cost vs accural budget by different catageroy.


    2018年8月3日 下午 02:25