Sales Report - Display the order total...

[Ben1985]

Hey again,

I have a Amazon sales report we generate and I'd like the totals from each other displayed in a separate column. The totals are currently amongst the items so I can't clearly identify the totals.

The example sheet shoes what I'm after a lot better than words I think.

Thank you in advance for any help.

Ben

AmazonOrderReport.xlsx

[arlu1201]

Will your data always have a blank row separating each entry? And will the total be in that blank row?

[Ben1985]

Hi,

Yes, there will always be a blank. The total is in the blank row and the items on the invoice will be listed below.

Thank you.

Sorry, I'll also add that the sales report isn't just Amazon's, it will be a mixture of order (Web / Amazon / Ebay) etc.

Thanks,

[arlu1201]

Irrespective of who's sales report it is, you want the totals for all the data to show up right?

[Ben1985]

Yes. But, the goal of what I want to do is have a summary sheet on sheet2. So it'll Display:

Amazon Orders =
How many =
Average Basket =
TTL orders ££ =

And then the same for Ebay orders etc etc.

Thank you,

[arlu1201]

This code will transfer the totals to column Q. It also removes the value from column H.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[Ben1985]

Hi,

Thanks for the code, and it works well. I was hoping for a forumla based solution so I could adapt it and get the information from it that I wanted for above summary. With the VBA version I can't break our different types (amazon, ebay etc etc).

Thanks,
Ben

[arlu1201]

If you want the summary that you mentioned in post 5, you can get it with formulae and pivots if required.

The code can help you clear the column and put the total in Q. Even the summary can be automated once you have the formulae ready.

[Ben1985]

Hi,

Thanks for the code, and it works well. I was hoping for a forumla based solution so I could adapt it and get the information from it that I wanted for above summary. With the VBA version I can't break our different types (amazon, ebay etc etc).

Thanks,
Ben

[Ben1985]

Ar.. Not sure I know how to summarise it via a pivot table.

Thanks for you help.

If you want the summary that you mentioned in post 5, you can get it with formulae and pivots if required.

The code can help you clear the column and put the total in Q. Even the summary can be automated once you have the formulae ready.

[arlu1201]

For you to be able to use a pivot table, your data will need to be formatted properly.

For e.g. your data will need headers and you should not include totals in between the rows of data.

[Ben1985]

Thanks.

Is there not a formula that could do the same as the code? I'm rubbish with where to go with VB and pivots.

[arlu1201]

I have already given you directions on how to use the code.

When you get the raw data into your file, just run the macro. It will put in the totals for you.

Then we can work out some formulae for you to arrive at the summary you need. Once you have the formulae, you can add it to the code as well. So you have everything done in one click.

How do you want the summary to show up? Can you upload a sample showing it?

[Ben1985]

Apologies, it's me not being very clear.

I've attached a sample sheet of what the summary page would ideally look like.

Thank you,

AmazonOrderReport123.xlsx

[arlu1201]

How do you calculate TTL and Average Basket?

[Ben1985]

If I had a list of all the order totals from the different platforms (Amazon / Web etc) I could add up to get the total of spend on that platform. Average would be adding the totals up and dividing by the number of orders. Sorry; not very good on Excel.

[arlu1201]

But you want the TTL per platform right? How do you calculate TTL? And does it stand for Total?

[Ben1985]

Sorry, my bad. Yeah TTL stands for total. If it had a formula that totalled all the Amazon orders and so on I could calculate the rest from that.

That's what I mean when I don't think that macro will work.

[arlu1201]

You can use a sumif formula to get the totals for each group, however, for that your data will need to be properly formatted, as i said before. You cannot have the total lines in between your data as this will double the values.