I have expense data from my credit card statements that I have scanned and imported into rows and columns into Microsoft Excel 2003. The data shows the date I made the purchase, who/what I paid (e.g. Mortgage, Allstate Car Insurance, Wendys, etc…), where the payment was made (what State), and how much I paid / the dollar amount of the expense.. I have about 500 rows of data for the year 2011 (per credit card) with the following column headings: Date purchased Name of vendor / person Location (State of purchase) Dollar Amount of purchaseNow, I want to take that data (about 500 rows) and combine it into logical categories. So for example, if I purchased meals at Wendys, Outback, and The Pancake House, I want to have those purchases show under the “Restaurants” category. Instead of having a category (column) per name of each restaurant, I want to combine that data under one logical category (column) called e.g. “Restaurant”.I have about 30 of these categories (e.g. Restaurants, Health Insurance, Travel, House Utilities (gas, electric, water), etc…). So now I want those 500 rows of raw data to be consolidated to a smaller number of rows on a separate (2nd) tab on my spreadsheet. And I want the data by week (week 1, week 2, week 52) based on the date of purchase on the credit card statement.For example, If I made 5 purchases of meals at Wendys’ and 5 purchases of meals at The Pancake House in week 1 of 2011 that totaled $120, I want to show one (1) row that represents those 10 purchases (5 + 5) under the column Restaurants for week 1 with a dollar amount of $120. I want to do this for each week in the year. I want to have the sum of the dollar amount spent per week (row) / category (column)I want to have a sum of each row (each week) – in the last column called “TOTAL”. I want to have a sum of each column category (e.g. how much money did I spend on the mortgage in 2011 or how much I spent on restaurants). I want this in the last row called “TOTALS”.I need the output as an excel spreadsheet with the ability to save as a comma delimited file.Date Restaurants- Mortgage- Utilities- Cat X- TOTAL-----------------------------------------------------------Week 1 – $120 Total $Week 2 – $90 $250 Total $Week 3 – $50 Total $Week 4 - $175 $1,000 Total $Week x - $ Total $TOTALS $$sum above $$sum above $$sum above TOTAL $How can I do this in Microsoft Excel 2003?
trial and error
Hi, I’m a moderator for this topic and I wonder whether you’re still waiting for an answer. If you are, please let me know and I will do my best to find a Professional to assist you right away. If not, feel free to let me know and I will cancel this question for you. Thank you!
Yes, I am still waiting for an answer.
Sometimes, finding the right Professional can take a little longer than expected and we thank you greatly for your understanding. We’ll be in touch again shortly
Thank you for your patience, your business is very important to us, we are waiting on the professional with the right expertise to come online. Feel free to let us know if you would like us to continue searching for a professional or if you would like us to close your question. Thank you for your understanding!
Rachel, thank you for the status update. Yes, I still need the answer to this question.
Please understand that it’s rare when we cannot find the right Professional Nevertheless, because you are entitled to your money back with no questions asked, we will process your refund immediately, upon your request. Please accept our sincere apology. We hope you’ll think of us again when the need arises