MacRumors Forums Excel for Mac - how can I do this...?
 User Name Remember Me? Password

 Thread Tools Search this Thread Display Modes
 Feb 20, 2013, 07:45 AM #1 tekno Banned   Join Date: Oct 2011 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? Thanks 0
 Feb 20, 2013, 07:48 AM #2 Sky Blue Guest   Join Date: Jan 2005 figure out what your mortgage payment is listed as, then command + f and type it in the box. 0
Feb 20, 2013, 07:55 AM   #3
tekno
Thread Starter
Banned

Join Date: Oct 2011
Quote:
 Originally Posted by Sky Blue figure out what your mortgage payment is listed as, then command + f and type it in the box.
I figured out this much, but doing this means clicking through 36 entries and writing down the amounts.

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.
0
 Feb 20, 2013, 07:59 AM #4 MacDawg macrumors P6     Join Date: Mar 2004 Location: "Between the Hedges" Depending on how much information you downloaded into the spreadsheet, you should be able to filter on the Mortgage Company as Payee 0
 Feb 20, 2013, 08:06 AM #5 ryguy619 macrumors newbie   Join Date: Feb 2013 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. 0
Feb 20, 2013, 08:37 AM   #6
tekno
Thread Starter
Banned

Join Date: Oct 2011
Quote:
 Originally Posted by ryguy619 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.
Thanks for this but I think it's way above my Excel abilities. Typing "=LEFT(HFX HOME)" just gives the result "NAME?"
0
 Feb 20, 2013, 08:39 AM #7 MacDawg macrumors P6     Join Date: Mar 2004 Location: "Between the Hedges" What does the data in your spreadsheet look like? 0
Feb 20, 2013, 08:56 AM   #8
tekno
Thread Starter
Banned

Join Date: Oct 2011
Quote:
 Originally Posted by MacDawg What does the data in your spreadsheet look like?
Column A - Date
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.
0
 Feb 20, 2013, 09:07 AM #9 NeverhadaPC macrumors 6502   Join Date: Oct 2008 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 0
 Feb 20, 2013, 10:09 AM #10 prisstratton macrumors 6502   Join Date: Dec 2011 Location: Winnipeg Here is one way: Attached Thumbnails   __________________  17" 2011 MacBook Pro, 2.3 GHz i7 Quad, 16GB, Intel 520 240GB SSD  11" 2012 MacBook Air, 1.7 GHz i5 Duo, 4GB, 128GB  4th. Gen. iPad, 16GB, WiFi  4th. Gen. TC, 2GB 2
Feb 20, 2013, 12:48 PM   #11
tekno
Thread Starter
Banned

Join Date: Oct 2011
Quote:
 Originally Posted by prisstratton Here is one way:
It's times like this that MR needs an official "thanks" system. Really appreciate the time you've taken to answer my question - thank you.
0
 Feb 20, 2013, 02:00 PM #12 LongSticks macrumors 6502   Join Date: Jul 2012 Location: Kent, UK Excel - alternatives Hi 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. Attached Thumbnails       __________________ 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 0

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Similar Threads thread Thread Starter Forum Replies Last Post chrispy1042 Mac Applications and Mac App Store 2 May 4, 2014 07:41 AM Alexander-James Mac Applications and Mac App Store 1 Apr 24, 2014 01:19 AM DanK104 Mac Applications and Mac App Store 1 Oct 21, 2013 12:57 PM Noob2mac Mac Applications and Mac App Store 2 Apr 24, 2013 05:24 AM Extreme Red Mac Applications and Mac App Store 2 Jun 5, 2012 09:51 AM

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home News and Article Discussion     MacRumors.com News Discussion     Mac Blog Discussion     iOS Blog Discussion iPhone, iPod, iPad, Apple Watch     iOS         iOS 8         iOS 7         iOS 6         iOS 5 and earlier         Jailbreaks and iOS Hacks     iPhone         iPhone Tips, Help and Troubleshooting         iPhone Accessories         iPhone Launch Meetups         iPhone Wallpapers etc.         iPhone Apps     iPad         iPad Tips, Help and Troubleshooting         iPad Accessories         iPad Launch Meetups         iPad Wallpapers etc.         iPad Apps     iPod touch         iPod touch Hacks         iPod touch Accessories         iPod touch Apps     iPod     Apple Watch         Apple Watch Accessories         Apple Watch Apps     Alternatives to iOS and iOS Devices Apple Applications     iPhone and iPod touch Apps     iPad Apps     Apple Watch Apps     Mac Applications and Mac App Store Apple Hardware     Desktops         iMac         Mac Pro         Mac mini     Notebooks         MacBook         MacBook Pro         MacBook Air     PowerPC Macs     Apple TV and Home Theater     Mac Peripherals     Buying Tips and Advice Apple Systems and Services     Mac Basics and Help     OS X         OS X Yosemite (10.10)         OS X Mavericks (10.9)         OS X 10.8 Mountain Lion         Mac OS X 10.7 Lion         Mac OS X Server, Xserve, and Networking     iCloud and Apple Services     Windows, Linux & Others on the Mac     Programming         iPhone/iPad Programming         Mac Programming         App Store Business, Legal and Marketıng Special Interests     Mac and PC Games         Console Games     Digital Audio     Visual Media         Design and Graphics         Digital Photography         Digital Video         Web Design and Development     Apple Collectors     Distributed Computing Mac Community     Community Discussion         Apple, Industry and Internet Discussion         Current Events         Politics, Religion, Social Issues     Picture Gallery         UI Customization     Site and Forum Feedback         Mac Guides Private Forums Archive     Wasteland     Archives of Old Posts         MacRumors News Discussion (archive)         MacBytes.com News Discussion         Buying Tips, Advice and Discussion (archive)         Community         Daily Tunes Site Discussion and Feedback         Event Archives             Macworld San Francisco 2008         Games         General Mac Discussion         Hardware Rumors         iPhone Purchaser Meetups         Leopard Event Meetups         Mac Help/Tips         Mac OS X 10.3 (Panther) Discussion         Mac Scene         Macintosh Computers         MacRumors Old Skool         Marketplace Archive 1 (Posts count)         Marketplace Archive 2             iPhone Marketplace Archive         Music Discussion         New Mac Application Announcements         Product Recommendations/Reviews         Site News         Switch Stories         Web Design and Development (archive)         Past Contests             1,000,000 Post Contest             2,000,000 Post Contest             3,000,000 Post Contest             4,000,000 Post Contest             5,000,000 Post Contest             Ten Million Post Contest

All times are GMT -5. The time now is 07:57 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps
 Contact Us - MacRumors Forums - Archive - Privacy Statement / DMCA Agent - Top

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2015, MacRumors.com, LLC