Can Excel solve my problem or do I need Access / Python?

Hi everyone,

I've spent a few days looking at how to solve a problem, firstly in Excel an then Access and now I'm more confused than ever! I've been looking into this for 3 days and I'm no further forward. Please bear with me, it is quite long winded.

Its for a university project and I need to extract an answer from working on the data in order to move forward.

I can work out this number by manually working through a number of steps but I need to do it for 100 different price points on weekly data for a 3 months which is not viable manually.

These are the steps:

This is a sample of the data;

EVENT_ID ITEM_ID COUNTRY TRANSACTION_TIME PRICE NUMBER_TRANS VOLUME
107802200 5931325 GB 26/12/2012 15:45:00 670 4 0.14
107802200 5931325 GB 26/12/2012 15:45:00 880 3 0.18
107802200 5975903 GB 26/12/2012 15:45:00 3 64 2259.2
107802200 5975903 GB 26/12/2012 15:45:00 3 7 100.12

The data is then filtered to a price, ie 2.

EVENT_ID ITEM_ID COUNTRY TRANSACTION_TIME PRICE NUMBER_TRANS VOLUME
107802200 5261525 GB 26/12/2012 15:45:00 2 37 1601.52
107802200 5921132 GB 26/12/2012 15:45:00 2 46 833.5
107802076 4056023 GB 26/12/2012 12:35:00 2 7 446.44
107802078 3799249 GB 26/12/2012 13:10:00 2 55 1964.1

I then copy this visible data to a new worksheet.

I then insert a Pivot Table based on EVENT_ID and drag it in to the VALUES area so that I now know how many transactions there were at each event at the price level of 2.0 i.e.

Row Labels COUNT
107802076 1
107802078 3
107802080 1

The business only makes money when the COUNT is more than 1.

I then copy these values to another cell so I just have the values and not a formula from the Pivot Table.

I then apply a formula to show the profit earned for each EVENT_ID

https://dl.dropbox.com/u/86643329/screen.JPG

Here's an image showing the worksheet and the formula. The formula for B1 is

=100/(B2-1)

B2 is the filter price from previously, in this case 2.0.

I then total column F to show the profit at price 2.0

My problem is this; I need to do these steps for every price from 1.01 to 2.00 ie 1.01, 1.02, 1.03 to have a final Excel document showing

PRICE PROFIT
1.01 XXX
1.02 XXX
1.03 XXX

Is there a way to do this in Excel? Or do I need to try something else?

Many thanks,

Mark

Also cross posted here;
http://www.mrexcel.com/forum/excel-q...ml#post3360825

See if this is what you are after. The results are put in columns J - N.

https://www.dropbox.com/s/q9fgdqo0io...ecember26.xlsm

abousetta

I'll have a look later today and get back to you. I wanted make sure I was on the right track before adding formulas.

abousetta

Have a look at the new attachment (https://www.dropbox.com/s/x0zup8xvsr...ember26-5.xlsm). I added a series of formulas that should provide you with the data you need. You might want to double check the outliers to make sure they are correct.

Let me know if this works for you.

abousetta

First part was as before and used scripting dictionary to count the number of unique keys.

Then (out of simplicity) I added the formulas to the row of the newly formed columns and dragged down.

Last step of course was just to create the a line graph to visualize the results.

abousetta

P.S. Below is the vba code I used:

Please Login or Register  to view this content.

It ran fine on my computer and it also worked when I did it manually. In any case, if it happens again, you can either do it manually by highlighting all of Column J >> Data >> Text to Columns >> Other >> pipe character (|) [which is shift + forward slash]. Or you can create a single line sub to run:

Please Login or Register  to view this content.

abousetta