Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 4th 06, 08:44 PM posted to microsoft.public.excel.misc
Geo
 
Posts: n/a
Default 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

I’m fairly new to complex formulas, so any help would be appreciated.
Thank you.


  #2   Report Post  
Old June 4th 06, 09:52 PM posted to microsoft.public.excel.misc
mrice
 
Posts: n/a
Default 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   Report Post  
Old June 5th 06, 01:38 PM posted to microsoft.public.excel.misc
Geo
 
Posts: n/a
Default 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   Report Post  
Old June 5th 06, 09:58 PM posted to microsoft.public.excel.misc
mrice
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to sort but keep linked formulas? GovUser Excel Discussion (Misc queries) 7 April 3rd 06 10:36 PM
Help with copying dynamic column selected based on remote cell value and dynamic formula fill ers Charts and Charting in Excel 0 March 1st 06 02:05 AM
Sorting and copying automatically between worksheets nadia New Users to Excel 2 February 12th 06 11:18 PM
Macros and Toolbars Bec Excel Discussion (Misc queries) 7 January 23rd 06 03:54 PM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM


All times are GMT +1. The time now is 05:11 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017