?
Solved

How do I sort my data into subtotals by month by company using Excel 2010?

Posted on 2011-10-20
25
Medium Priority
?
245 Views
Last Modified: 2012-05-12
Hello,

I am a very basic Excel user, and I have a large data set to break into subtotals for a meeting tomorrow.  I am sure that what I am asking to do is relatively simple (a Pivot Table looks like it was designed to do exactly what I need), but I don't have time to learn exactly how to use it, and my initial efforts weren't what I am looking for, so I will ask for help here.

I have a 19 month transaction history for our various clients at all our facilities.  There are two transaction types (receipts and shipments), along with the item in the transaction, the weight of the item, and in the case of a shipment, the receiver of the product (it is sometimes the same company that supplied the item).

My task is to subtotal the transaction types by month for each company.

I can do this by physically manipulating the data (sort, sum, etc.)... however, the entire data set is over 550k rows.

I have attached a sample of the data, for one site over a three month period.  The site transaction history is on the first tab, and one way I envisioned presenting the totals is on the second (I am open to suggestions... this is just what I was thinking as I was hammering things out).

Any help would be appreciated!
0
Comment
Question by:meelnah
  • 15
  • 10
25 Comments
 
LVL 1

Author Comment

by:meelnah
ID: 36999723
sorry.. forgot to include the sample data SampleData.xlsx
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000100
As an update... i have my data looking 'kind of' how I want.  I used a pivot table and was able to break down the transaction data by company and their transaction type for each day.

However, what I really need is to have the transaction data presented in a monthly summary.  I attached an example.  The pivot table is on sheet 1.  This table would work, except that I need the columns to be months rather than individual days, and the numbers in the column would be the company, shipment, and receipt totals for that particular month. SampleData.xlsx SampleData.xlsx
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37000175
Hi Meelnah

Please check the attachment and let me know if this is what you are looking for?

Best regards...
SampleData.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:meelnah
ID: 37000196
not quite... it looks like you are totalling the number of transactions of each type... I need the sum of the weights of these transactions, to find the total number of kgs received for company 1 in August, Sept, etc.

I am sorry if I was unclear.
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37000213
oh ok... I didnt read your latest comment...

Please check the attachment...
SampleDatai.xlsx
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000231
The numbers look correct.

Can you tell me how you derived them?  Did you manually create the formulas?
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37000275
Hi Meelnah,

I have used an array formula on sheet 2...

=SUM(IF((TEXT(Date Column,"mmmm")=Sheet2!C$1)*(Type Column=Sheet2!$B3)*(Company Column=Sheet2!$A$2),Weight Column,0))

=SUM(IF((TEXT(Site1!$A$2:$A$683,"mmmm")=Sheet2!C$1)*(Site1!$B$2:$B$683=Sheet2!$B3)*(Site1!$C$2:$C$683=Sheet2!$A$2),Site1!$E$2:$E$683,0))

Since its an Array Formula you need to hit "Shift + Enter" after you enter the formula so that the formula looks like whats given below in Excel

{=SUM(IF((TEXT(Site1!$A$2:$A$683,"mmmm")=Sheet2!C$1)*(Site1!$B$2:$B$683=Sheet2!$B3)*(Site1!$C$2:$C$683=Sheet2!$A$2),Site1!$E$2:$E$683,0))}

Also please note that the column ranges should be the same across all used columns in the formula.(For example in this case the column range is $A$2:$A$683). The other used columns are B, C and E... Their range should also be from 2:683.

I hope I didnt confuse you...
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000310
"I hope I didn't confuse you..."

you did, but it was through no fault of yours!  I am just not as proficient of an excel user as you!

I can see your formulas, and given time, go through them and understand what you are doing with them--however, the chances of me being able to recreate them next year when I have to repeat this exercise are sliim and none!

Are you familiar with pivot tables?  It appears I can do a similar function as your solution with the tables... i just can't seem to get it formatted correctly.  If I can manage to make the pivot tables work for me, I can possibly recreate the report as necessary, and not have to find someone to assist me with formulas in the future!
0
 
LVL 1

Accepted Solution

by:
VenuChakkoth earned 2000 total points
ID: 37000360
Hi Meelnah,

Sorry that I couldnt explain myself better... I have redone the same using pivot tables... I made one change to the data by adding a column named Month to get the respective month for the dates.

Please let me know if this works...
SampleData.xlsx
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000520
That's it!

I was able to come up with almost the same thing, except my columns listed the days of the transactions rather than the totals by month.

The best I could do was group the columns by month... which works fine except that my data set spans 18 months, and the grouping didn't list the months by year... i ended up with only 12 columns instead of 18.

How did you overcome this when you set up the pivot table?  I am sure I'm missing something easy...
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37000572
Hi Meelnah,

I had to add a column named Month on the data sheet(converting the dates into their respective month) to get the pivot table in the desired format.
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000625
ok.. i considered that, but thought there may be an easier way with the pivot table.  

I believe this would work for me, except that my data set spans 18 months.  When I tried something similar with the entire set, I found that rather than seeing 'August 2010, Sept 2010, ... Sept 2011, Oct 2011), the table give me a 12 month period, and it looks like it combines August of 2010 with August of 2011 and puts them in the same 'August' column.

How would I make the table recognize that Aug 2010 is different than Aug 2011?
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37000656
In the data sheet you will find a formula to convert date into month. Change it to =text(a1, "mmm-yyyy") where a1 is the cell that refers to the date. And refresh the Pivot.

I am not sure of any other tweaks on the Pivot. Other experts may know better ways of achieving this.
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000666
You've been a great help, VenuChakkoth... I really appreciate it!
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000717
I spoke too soon!

I used the formula change to make the 'Month' column display MMM-YYYY.  However, when I rerun the Pivot Table, it still displays the 'Month' columns as August, September, etc. rather than Aug-2011, Sep-2011 like is in my Month column...

any ideas?
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37000833
Hi Meelnah,

That should have worked... I am not sure what went wrong. Please refer the attached spreadsheet.

Thanks & Regards..
SampleData.xlsx
0
 
LVL 1

Author Comment

by:meelnah
ID: 37000843
ok... i will back out of excel and try it again.

Once more, my thanks!
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37000847
You are welcome. Glad that I could be of help...
0
 
LVL 1

Author Comment

by:meelnah
ID: 37001177
sorry to keep bugging you, but have one more small 'bug'.

I set up the pivot table to use the new column I created (called 'Pivot Date' - this is the one in the mmm-yyyy format) as the column label, which looks great, except that it is alphabetizing the columns rather than arranging them in chronological order.

Is this because I used the =TEXT(B2,"mmm,yyyy") formula, so excel sees the values as text, rather than as a date?

I tried setting the column format to DATE, but this doesn't seem to help, either.
0
 
LVL 1

Author Comment

by:meelnah
ID: 37001244
I can see that I can modify the output of the pivot date column to be 1-2010 rather than Jan-2010, and the columns are then sequenced properly.  However, I would prefer to have the columns named Jan-2010 rather than 1-2010... is there perhaps a way to rename the pivot table column header after the table is created?
0
 
LVL 1

Author Comment

by:meelnah
ID: 37001264
If you'd prefer, I can ask this specific question again... you have given me quite a lot of your time already!
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37001420
Hi Meelnah,

No worries... I had to convet the =Text() formula into values. Then it arranged the months in chronological order. The cell format of dates on the Pivot will also have to be changed to "mmm-yy" format.

Please see the attachment.

Thanks...
SampleData.xlsx
0
 
LVL 1

Author Comment

by:meelnah
ID: 37001434
ok.. the 'VALUE' entry in the formula makes excel look at what the cell represents, rather than just the text within the cell then?
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 37001462
thats right...
0
 
LVL 1

Author Comment

by:meelnah
ID: 37001531
Thanks again!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question