Possible to organize all this??

[Guest]

I have been playing with this since 8:30 this morning, and I'm done. I need
help.

Goal:
Sort bank statement information by date and/or two self-assigned sort values
and see totals for various sort types.

Data:
Date
Vendor Name
Cost
Value Big assigned as Budget Priority (numbers 0 -8 for Food, Groceries,
Bills, Rent, etc.)
Value Small is a Vendor Assignment (letters A – U: pizza, Cost Cutters, drug
store, Snapfish) within Value Big.

Example? Burger King is a “0†Budget Priority (Big) with “B†as a Vendor
Assignment (Small).

Desired Outputs:
• Pie chart showing all of Value Smalls broken out by total cost (amount
spent at Burger King, Eckerd, gasoline, for the complete time period)
• Pie chart showing Value Big, broken out by month (amount spent in June on
Food, Bills, etc)
• Chart showing Value Big, as percentage of total expenses for month/quarter
(Food was 50% of debit card purchases in month of July)
• Bar chart comparing two Value Big over three month period, delineated
(Food vs. Misc Expenses, side by side, for June, July and August)
• Descending chart showing Value Small for specific Value Big for each month
(amount of each Groceries entry (multiple entries) for June, July August).

In addition, as if this weren't enough, I'd really like my chart(s) to
update dynamically as I add in future bank statements and code in my
subvalues.

Problem:
Assigning the additional 'descriptor' values to my vendors doesn’t really
link the data together, it just provides a way for me to sort and stare at
the data. I want to be able to say “Let’s see all the Priority 0 items sorted
by Value Small in a graphical representation, with totals.†Or, in a larger
sense, I’d like to say “Let’s see how my Food Value compares to my Bills
Value for the last two months.†Or maybe something like “Let’s see in which
month I ate at Burger King (a Value Small within Value Big) the most.â€

I’ve examined the use of Pivot tables and maybe that’s what I need to use,
but I can’t figure out how to organize my data so that I can USE it.
Thoughts? Is this even possible or should I resign myself to making 80 small
charts for each view I come up with?

 

[Jon Peltier]

I think that pivot tables are the way to go.

Set up your data with columns for each field (Date, Vendor, Cost, Value Big,
Value Small) and one row per transaction. If you're using Excel 2003,
convert this table into a List (Data menu); if you're using Excel 2007,
convert the table to a Table. This conversion means that as the List or
Table expands, the pivot table source will also expand. You still need to
refresh the pivot table to see the added data.

Make a pivot table of this data. Drag the pivot fields around until you get
what you need. Your first bullet requires Value Small in the Rows area, and
Sum of Cost in the Data area. The other bullets require other arrangements;
make another pivot table for each, or copy the first and paste it elsewhere.

You can make pivot charts or regular charts from the pivot tables:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

- Jon

 

[Del Cotter]

Assigning the additional 'descriptor' values to my vendors doesn’t really
link the data together, it just provides a way for me to sort and stare at
the data.

It looks as though you have sorting sorted, but you can't show just the
items you want. Have you looked at AutoFilter? That should let you
select one field by clicking on the value in that field, and the graph
should then show only what's filtered in (if you've got that option set
in Options)

Pivot Charts will also do some things, but you must be sure to have your
data sorted so that the fields are vertical columns with the field names
at the top in some distinctive format, (which it sounds like you've
already achieved if your sorting is good)

I can't seee your problem, which is a shame as you've framed your
question so well and comprehensively.

 

[Guest]

So converting the data to a list enables you to differentiate between
subvalues within a column... Interesting. This definitely adds functionality
to a large quantity of data, and seems to solve most of my problem.

It also seems my subvalues are superfluous for a Pivot Table, but crucial
for a regular old data sort.

Thanks!!!

 

[Guest]

I've played with the sorting and the filtering, and so far the Pivot table is
working well, I just have to figure out which piece of data goes where (I
used to be able to do this fairly well... so much for the riding a bike
analogy!).

The last piece that's eluding me is comparing apples and oranges - Value Big
/monthly/ totals.

I have my complete data table sorted first by Value Big then by Date. I've
totaled the withdrawals by month in another column called Monthly Totals.
[Side note - if I ever re-sort or play with my data table, those columns will
be useless until I sort back into Value-Date order! Is there a better way of
doing that?]

Beside THAT column I have one called "Value Totals" which is the sum of all
the dates for a particular Value Big - June, July, and August Food expenses,
for example. Beyond that column is one last Total Total column that adds up
everything I've spent over the entire time period, regardless of category or
value.

My last effort this evening is trying to get all my Monthly Totals to show
up in the Pivot. I haven't yet figured out how to get an "August Total" and a
"June Total" and so on for each Big or Small value. I just get a listing of
every date that had a transaction over the month. I feel like I want the Row
Field to be the words of each month (no idea how to accomplish that), the
Column Field to be the Monthly Totals but that leaves no Data Items??

Maybe I need to try Link Cells and create a separate Sheet for abbreviated
data to pull a Pivot from?

 

[Del Cotter]

I've played with the sorting and the filtering, and so far the Pivot table is
working well, I just have to figure out which piece of data goes where (I
used to be able to do this fairly well... so much for the riding a bike
analogy!).

The last piece that's eluding me is comparing apples and oranges - Value Big
/monthly/ totals.

There is a way to instruct Pivot Tables and Charts to group monthly even
when your data is in date form; if you right-click on the Date in the
Pivot Table, Select "Group and Outline.. Group" and select "Months" in
the list, it groups all the dates together by month.

That way you don't have to create a special column for Month.

 

[Jon Peltier]

No, the subvalues get filtered in the pivot table. Making it into a list
means the list grows as you add data, so you don't have to redefine the data
range whenever you refresh the pivot table.

- Jon

 

[Jon Peltier]

You should group by both month and year if you don't want this year's July
to merge with last year's. You can group by quarter as well, and show
quarter or month depending on what you are interested in.

- Jon

 

[Guest]

That is BRILLIANT, thank you both so much for your help on this! I never
would have found that feature.

And if I stick Value Big in the Page section and Value Small in the rows,
instead of clicking which Smalls I want to see I can use Page to sort them
out!

God I love Excel. Thank you thank you!