Seemingly Complicated Excel Question

[ebase131]

Good afternoon everyone,

I am not new to Excel, but am still learning the depth of its power. I am trying to use a series of formulas and/or macros and/or VBA (?) to essentially create an update-able spreadsheet.

Basically, there is a spreadsheet at my work that is MANUALLY updated each month with sales quantities and dollar amounts per customer per item bought. We sell a variety of things, but the number of things IS limited (about 50 different items total) and the number of customers increases by perhaps 3 or 4 per month (approximately 200+ customers currently).

We use a program that can spit out (in excel format) a data table with the customer and what item(s) they purchased and how much of each and for what amount, but this still then needs to be manually added to previous totals for each month for all customers/items in the overall sales spreadsheet. The annoying and time-consuming aspect, as you can imagine, is having to manually input the total sales quantities and dollar amounts for each material for each of the customers. It's a smaller company, but I was recently brought on and believe this is one of many things this company is lacking to help streamline some processes.

Specifically, I would like to be able to take the data from this separate program, paste that into a spreadsheet, and have Excel then automatically update the overall sales for the current month as well as the total sales for each customer and their respective items bought/quantities/etc. One big problem I keep running into is that there would need to be a way to add rows (which I don't know how to do with formulas) when a new customer is identified by the formula and plug that customer in the rest of the customers in alphabetical order, as well as be able to identify when a current customer has ordered a new product (thus requiring a new row added for that customer).

Obviously there are a lot of detail and things I'd like to add, but if I could just learn how to do the aforementioned sorting/adding I think I could apply those methods for more details and make it work for the company. Of course there are things like sorting the data with filter, etc., but I want this to be completely automatic once I paste the raw data into a spreadsheet. Ideally, once that is done, the entire overall sales numbers and data are updated immediately.

Please let me know if anyone has any ideas on how to best attack this problem. I have tried a formula such as this:

=IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B2, $A$2:$A$20), 0)),"")

to sift out and sort unique companies (they are spit out in the raw data as multiple entries if they made multiple purchases of multiple products), but this doesn't deal with many other of the problems that one would face in trying to achieve what I'm going for.

I know this is a lot of information all at once, and there is some I left out to save time, so please let me know any questions I can answer to clear up what I am actually looking for.

Thank you very much for any help anyone can provide.

Sincerely,

Eric

 

[Damon Ostrander]

Hi Eric,

What you describe should be fairly easy to accomplish with a VBA macro. Reading the data from the other program should be very easy, and things like filtering, sorting, and adding rows is quite easy in VBA. However, it is impossible to give you specific code for accomplishing what you need without a thorough description of the layout of the two worksheets (or workbooks) and what the final product should look like. I recommend that you post examples of the two workbooks and the final product somewhere where we can download them.

Damon

 

[Titian]

Your general description of what you are attempting to achieve suggests that a Pivot Table would provide the results.

So long as transactions are recorded at row level with all required fields present in the row e.g. Customer #, Name, Date, Invoice #, Item Description, Quantity, Unit Price, Total Sales Value, Unit Cost etc, etc - the PT can be constructed easily and updated easily when new items are added to the this database (list).

If you are not familiar with PT's perhaps now is a good time to invest some learning time which will save you so much time in future.

Good luck

 

[ebase131]

Thank you for the quick response.
I have made a simple example excel sheet but am unsure how to share this effectively as I haven't done this before. Is there an easy way to allow you to download this?

I have also tried the PivotTable idea, but I think my limited knowledge and/or the nature of the tables wouldn't allow for the type of automated updating I'm looking for.

Hi Eric,

What you describe should be fairly easy to accomplish with a VBA macro. Reading the data from the other program should be very easy, and things like filtering, sorting, and adding rows is quite easy in VBA. However, it is impossible to give you specific code for accomplishing what you need without a thorough description of the layout of the two worksheets (or workbooks) and what the final product should look like. I recommend that you post examples of the two workbooks and the final product somewhere where we can download them.

Damon

 

[ebase131]

Anyone have any suggestions? I would greatly appreciate it.

-Eric