CMA Community

1. Rudolph Cheong
   Member

   Reputation:3

    

   We use Pay by Scan with a major customer and get about 5000 invoices from their locations daily which have to be enterred on Excel manually. Is there a software/program that could consolidate the PO's by stores so that at the end of the season, we can see what stores scanned and which didn't

   Posted 1 year ago #

2. F Scott Wilson
   Member

   Reputation:279

    

   You shouldn't need another program to do this. Pivot tables in Excel should be able to cull that information. so long as it's part of the data being manually entered. If you have to generate a monthly spreadsheet, I would suggest a master spreadsheet that holds all of the activity for the year. That way, you don't have to assemble it at year-end, and can analyze it at need throughout the year.

   Posted 1 year ago #

3. Rudolph Cheong
   Member

   Reputation:3

    

   I do not understand this Pivot Tables in Excel.
   The problem is that Home depot sends a page from each store every day with
   the scans for that day.
   The only way to record them is manually looking at what was scanned from
   which store and record it.

   How would Pivot tables in excel help?

   Thanks,

   Rudolph

   Posted 1 year ago #

4. F Scott Wilson
   Member

   Reputation:279

    

   What I was thinking was that, since the data has to be manually entered into Excel, it would be easier to have Excel do the work. That's why I suggested using Pivot Tables. Even a regular sort would be useful. Incidentally, I have only used Pivot Tables once or twice, but found them very useful. I don't, however, know how to set them up.

   Is there a reason Home Depot doesn't submit their lists in an electronic format? It sounds like your company is a fairly big vendor, and it would make sense for them to make some sort of accommodation, IMHO, to make processing easier.

   Posted 1 year ago #

5. DMarc CBA
   Member

   Reputation:80

    

   Pivot Tables in Excel are useful for comparing 1 report to another, I have a low level of experience and it took a lot of trial and error but I use it to compare previous paydex scores with the new scores. Copy (don't cut from your original)each report into 1 Excel file onto different pages, add a few extra sheets. IF the column amount is a number in text format and you need to calculate the values like a number, you will have to copy it and Paste Special (Value, Add) it into another column before you start to make the pivot table. Label the Columns that you want to "compare" with different names (ie Jun 2010 and the other July 2010) so you can distinguish them. You may also want to delete any columns you don't care about before you start the Pivot.

   When you are ready choose Data, Pivot Table, Multiple Consolidation Ranges, Pivot Table, Next, Create a single page for me, Next. This will bring you to Range, then you can copy the range on 1 sheet, then choose Add, copy the range on 2nd sheet, then Add, choose Next. Click on a blank sheet and go to cell A1, choose Layout. Then double click on Count of Value, then choose Sum, OK, Finish.
   You get a combined sheet and you may want to hide (can't delete them now) any columns you don't need to look at. From there you can do some conditional formating to make it subtract one column from another and bold/change font color/change background color etc to help you focus on the data you actually need to see. I would suggest making some smaller tables and playing with them until you get a bit morecomfortable with it BEFORE trying to tackle a big project otherwise it can be frustrating to have to keep starting over.

   Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.