Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a macro to copy data from a column to a row in another worksheet?
How to write a macro given a column with date and time
and chop off the time to just have the date in the column? How to write a macro to analyze the column of data and copy to a row in another worksheet in corresponding row? I like to run this Macro each month as I get more data so that if the data exist in Final Output Worksheet, it should copy the amount from given data work sheet to existing row on Final Output Worksheet. Attach is a text file of this question but the columns are properly space. The correct spacing is lost when I past the test here. With the given data below, perform the following: - Go down Item Descritpion - If Item Description = Buy bumper car Then goto Final Output Worksheet Search if the corresponding Item# exist then copy Debit amount to Buy Bumper Car column If item# not exist then in next available row copy item# to item# and copy date to date and copy Debit amount to Buy Bumper Car column - If item description = Car Wash Goto Final Output Worksheet and search for row that contain corresponding item# then copy debit amount to Car Wash column - If item description = buy gas Goto Final Output Worksheet and search for row that contain corresponding item# then copy debit amount to Car Wash column - If item description = car win race Goto Final Output Worksheet and search for row that contain corresponding item# then copy CREDIT amount to Car win race column - If item description = cost of selling car Goto Final Output Worksheet and search for row that contain corresponding item# then copy debit amount to cost of selling race column and in next row below copy the amount that follows "Selling price: " to selling price column - What ever is left just copy the entire row from given data worksheet to next available row in Misc WorkSheet - Repeat until Item Description is empty. Given Data WorkSheet Item# Date & Time Item Description Credit Debit 0001 Jan-01-03 01:30:30 EST Buy bumper car $100.00 0001 Jan-01-03 01:30:30 EST Car Wash $ 10.00 0001 Jan-01-03 01:30:30 EST Buy Gas $ 5.00 0010 Feb-01-03 03:30:00 Est Repairs $ 50.00 0002 Mar-01-03 01:30:30 EST Buy bumper car $150.00 0002 Mar-01-03 01:30:30 EST Car Wash $ 10.00 0002 Mar-01-03 01:30:30 EST Buy Gas $ 5.00 0001 Apr-02-03 23:00:00 EST Car Win Race $10.00 0002 May-03-03 14:00:01 EST Cost of Selling car $ 20.00 Selling Price: $120.00 (CASH) 0003 Jun-01-03 01:30:30 EST Buy bumper car $120.00 0003 Jun-01-03 01:30:30 EST Car Wash $ 10.00 0003 Jun-01-03 01:30:30 EST Buy Gas $ 5.00 0020 Jul-01-03 04:31:32 EST Buy Spoiler $ 10.00 Final OutPut WorkSheet Item# Date Buy Bumper Car Car Wash Buy Gas Car Win race Cost of sellig car Selling Price 0001 Jan-01-03 $100.00 $ 10.00 $ 5.00 $10.00 0002 Mar-01-03 $150.00 $ 10.00 $ 5.00 $20.00 $120.00 0003 Jun-01-03 $120.00 $ 10.00 $ 5.00 Misc WorkSheet Item# Date&Time ItemDescription Credit Debit 0010 Feb-01-03 03:30:00 Est Repairs $ 50.00 0020 Jul-01-03 04:31:32 EST Buy Spoiler $ 10.00 Attachment filename: bumper car spreadsheet task list.txt Download attachment: http://www.excelforum.com/attachment.php?postid=394538 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
1 Create a macro to Copy & paste certain data to another sheet | Excel Discussion (Misc queries) | |||
Create a macro to copy data from one workbook to another | Excel Discussion (Misc queries) | |||
Create macro to copy data on a scheduled basis | Excel Discussion (Misc queries) | |||
create a macro to copy a worksheet into another | Excel Discussion (Misc queries) | |||
Create Macro to Copy Data from one spreadsheet to another | Excel Discussion (Misc queries) |