Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting and copying data to another worksheet in a workbook.
I have created a workbook with multiple worksheets, one for each month of the
year. Each worksheet has four columes: DESCRIPTION, DATE, AMOUNT, TYPE Example: JAN (worksheet name) DESCRIPTION DATE AMOUNT TYPE Maple St. 6/1/06 $1200.00 Check Davis St. 6/2/06 $1300.00 Credit Wilson St. 6/1/06 $ 900.00 Cash Brown Ave. 6/4/06 $2250.00 Credit Mare St. 6/1/06 $1150.00 Money Order Sikes Dr. 6/10/06 $1000.00 Credit At the end of each month, the data needs to be copied, arranged by date, sorted by TYPE and moved to their respective summary worksheets (Check Transactions, Credit Transactions, Cash Transactions and Money Order Transactions). Example of results: Check Transactions (worksheet) DESCRIPTION DATE AMOUNT Maple St. 6/1/06 $1200.00 Credit Transactions (worksheet) DESCRIPTION DATE AMOUNT Davis St. 6/2/06 $1300.00 Brown Ave. 6/4/06 $2250.00 Sikes Dr. 6/10/06 $1000.00 Cash Transactions (worksheet) DESCRIPTION DATE AMOUNT Wilson St. 6/1/06 $ 900.00 Money Order Transactions (worksheet) DESCRIPTION DATE AMOUNT Mare St. 6/1/06 $1150.00 Im fairly new to complex formulas, so any help would be appreciated. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting and copying data to another worksheet in a workbook.
The simplest way to do this is with autofilters, copying the rows for each type to the summary sheet. As you are only doing this once a month this shouldn't take too much time. You could go for an automated solution with macros but this is maybe over the top. -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=548328 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting and copying data to another worksheet in a workbook.
I appreciate the input. However, this was a small sampling of data. The
actual data is thousands of lines long with over 20 different summary sheets for over 100 different worksheets. I have been cutting and pasting thus far and it takes me at least a weeks worth of work to complete each month. An automated solution will save tens of thousands of dollars over the course of a year. Thank you again for the suggestion. With respect, Geo "mrice" wrote: The simplest way to do this is with autofilters, copying the rows for each type to the summary sheet. As you are only doing this once a month this shouldn't take too much time. You could go for an automated solution with macros but this is maybe over the top. -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=548328 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting and copying data to another worksheet in a workbook.
In this case a macro is your answer. If you have a list of your 100+ workbooks in a spreadsheet or they sit in a small number of folders you can loop through them opening one by one looking for the data relevant to each summary sheet. Try recording one and see how you get on. Using the dir() function will enable you to extract filenames from folders - see the Help files for details. You might also consider getting your data into a database which would make this sort of summarisation so much easier. -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=548328 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort but keep linked formulas? | Excel Discussion (Misc queries) | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Sorting and copying automatically between worksheets | New Users to Excel | |||
Macros and Toolbars | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |