N
NewAtExcel
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
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