|Feb 20, 2013, 07:45 AM||#1|
Excel for Mac - how can I do this...?
I need to find out how much I have paid in mortgage payments over the last three years (amongst other things).
I've downloaded three years of banks statements and compiled them into one spreadsheet.
Is it possible to search/only show the cells that are my mortgage payments rather than going through manually and finding all 36 transactions?
|Feb 20, 2013, 07:55 AM||#3|
Ideally I want Excel to find all 36 entries, display only these entries to then allow me to SUM the 36 amounts in order to give me the total.
|Feb 20, 2013, 08:06 AM||#5|
Use a SUMIF statement
If the description of the mortgage payment is the same, or even if the first few letters are the same, you can do the following:
1) in a new column, use the LEFT function to extract out the first few letters of the description
2) then use the SUMIF function to add up any values in the column with your mortgage payments. make sure to use the help guide to see exactly how to use the SUMIF. it's pretty straightforward.
|Feb 20, 2013, 08:37 AM||#6|
|Feb 20, 2013, 08:56 AM||#8|
B - Description
C - Money in
D - Money Out
E - Balance
In column B my mortgage payments all start "HFX Home" and a number follows it (the number is different each time).
There are 1,816 transactions in total, so it's tricky finding these 36.
The amount paid also changes slightly each time.
|Feb 20, 2013, 09:07 AM||#9|
Newer Excel 2010+ (I think) can do smart sorting, so import the entire document into Excel (assuming it is a text file). Then sort Column B and select the "HFX Home" entries from Column E. There's probably a macro to be written there...
On that note: if you have access to MATLAB or know your way around C++/Java, you could write a simple string parser that just stores values that match the Description column. Then transfer those values to excel.
If that is too much, then just spend 5 minutes to copy-past old school and get it done.
15" MBP, 2.8 GHz, 4 GB RAM, 500 GB HD
20" iMac G5, 2 Ghz, 1 GB RAM, 250 GB HDD
Purple iMac G3 333 MHz, 96 MB RAM, 6 GB
|Feb 20, 2013, 10:09 AM||#10|
Here is one way:
17" 2011 MacBook Pro, 2.3 GHz i7 Quad, 16GB DDR3, Intel 520 240GB SSD
11" 2012 MacBook Air, 1.7 GHz i5 Duo, 4GB, 128GB
4th. Gen. iPad, 16GB, WiFi 4th. Gen. TC, 2GB
|Feb 20, 2013, 12:48 PM||#11|
|Feb 20, 2013, 02:00 PM||#12|
Excel - alternatives
A couple more alternatives.
1. Filter on Column B - a funnel will appear in the Header B. Click the funnel and de select all. Select HFX Home and just this data will appear. either highlight and then copy & paste to a new sheet for a permanent record, or just highlight the HFX figures and the total will appear the grey footer!
Screenshot below (top left):
2. Other way and this is what Excel is good for - is Pivot Table.
Highlight all 1800 lines of data and go to Data - Pivot Table in the menu.
It will show you what data you have chosen at the top and select option to open the Pivot Table on a new worksheet. (top Right)
in the box that appears place the description in the row box - by dragging the top descriptor. Then Money Out descriptor into the values field and a table appears as per my photo. If the descriptor in A3 says Count of or Summary of, right click on A3 and select Field settings in the menu and choose Sum. (Bottom Left)
Every descriptor will be grouped and its total shown.....great for analysing the whole 1800 lines of your statement. Find all sorts of trends.
Again a coup[le of screen shots attached. You can see that the Pivot table has grouped everything nicely...
Pivot Table is how I do this.....
Excel help will be good if you get stuck.
27" i5 16gb 2011; 15" i7 MBP 8gb 512gb SSD 2011; iPads & iPhones
Fujifilm XE-1 & Fujifilm X-T1 - 500px search = http://500px.com/adenmidge
Last edited by LongSticks; Feb 20, 2013 at 02:02 PM. Reason: additions
|Thread Tools||Search this Thread|
|thread||Thread Starter||Forum||Replies||Last Post|
|Visual Basic Module/Macro works in Windows Excel but not Mac Excel 2011||chrispy1042||Mac Applications and Mac App Store||2||May 4, 2014 07:41 AM|
|Templates for Microsoft Excel - Best Excel Spreadsheets for Mac!||Alexander-James||Mac Applications and Mac App Store||1||Apr 24, 2014 01:19 AM|
|Excel for PC reference books: okay for Excel 2011?||DanK104||Mac Applications and Mac App Store||1||Oct 21, 2013 12:57 PM|
|Formulas in Excel on Mac... Switch to values in Excel on PC||Noob2mac||Mac Applications and Mac App Store||2||Apr 24, 2013 05:24 AM|
|Creating Pivots in Excel 2011 that can be used in Excel 2003 (Windows)||Extreme Red||Mac Applications and Mac App Store||2||Jun 5, 2012 09:51 AM|
All times are GMT -5. The time now is 01:11 AM.