small excel project
 

Hello,

I have a small project and am wondering if there may be someone
interested in which I could pay a nominal amount.

I am looking for a spreadsheet that will take 4 columns of data:



TIME PRICE TIME PRICE

5:00 2500 5:00 3000

4:59 2500 4:59 3001

4:58 2501 4:57 3000

4:57 2500 4:56 3001



and match up the rows based upon the TIME columns to correspond with
each other. The missing rows would then be populated with the price of
the prior row and the appropriate time. In this case it would simple
parse (use of macro?) and insert 4:58 in column 3 and 3000 in column 4.



A simple excel sheet with some VBA (?) and a macro to run it once the
dirty data is in would suffice I suppose but I am clueless when it
comes to programming anything.


Please let me know if you’re interested and what the cost may be.



Thanks,


Leonard


---
Message posted from http://www.ExcelForum.com/

small excel project
 

How about a simple Non VBA approach. If it sems complicated then by all means
send me the data and I'll fix it for you, but there's no charge based on the
info given so far.


With your data currently sat in 4 columns, make sure you have a blank column to
the left of the first two, and a blank column to the left of the second two. In
the first blank column, in the first cell put Table 1 and copy down to the
bottom. In the second blank column put Table 2 and copy down to the bottom.
Now take the last 3 columns ie Table 2, Time and price and drop them directly
underneath the first 3, so your data now looks like this (make sure you put a
heading on the Table column):-

Table Time Price
Table 1 05:00 2500
Table 1 04:59 2500
Table 1 04:58 2501
Table 1 04:57 2500
Table 2 05:00 3000
Table 2 04:59 3001
Table 2 04:57 3000
Table 2 04:56 3001


Select the entire table of data including the headings and do Data / Pivot Table
& Chart report

Hit Next / Next / Finish (leave the default of putting it on a new worksheet).

Now from the dialog box that appears, drag the Time field to where it says 'Drop
row fields here' and drag the Table field to where it says ' Drop column fields
here'. Now drag the Price field into the middle where it says 'Data'

You should now have an ordered list with all the gaps in the right places.
Select the entire Pivot table and do Edit / Copy, then Edit / Paste Special /
Values. Select the entire column headed Table 1 and do Edit / Go To / Special /
Blanks and then just hit the RIGHT arrow on your keyboard once and hit
CTRL+ENTER at the same time. Repeat this for the column Table 2 but hit the
LEFT arrow once and then CTRL+ENTER together.

Finally, Select the entire table again and do Edit / Copy, then Edit / Paste
Special / Values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"leonard " wrote in message
...
Hello,

I have a small project and am wondering if there may be someone
interested in which I could pay a nominal amount.

I am looking for a spreadsheet that will take 4 columns of data:



TIME PRICE TIME PRICE

5:00 2500 5:00 3000

4:59 2500 4:59 3001

4:58 2501 4:57 3000

4:57 2500 4:56 3001



and match up the rows based upon the TIME columns to correspond with
each other. The missing rows would then be populated with the price of
the prior row and the appropriate time. In this case it would simple
parse (use of macro?) and insert 4:58 in column 3 and 3000 in column 4.



A simple excel sheet with some VBA (?) and a macro to run it once the
dirty data is in would suffice I suppose but I am clueless when it
comes to programming anything.


Please let me know if you’re interested and what the cost may be.



Thanks,


Leonard


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

small excel project
 

Sod's law, missed a tiny bit out about typing = that would stop it working.
I'll just reissue the note with the amendments in place though, as follows (Any
problems just holler, or mail me the sheet - Need to take the NOSPAM bit out of
my email though):-

vWith your data currently sat in 4 columns, make sure you have a blank column to
the left of the first two, and a blank column to the left of the second two. In
the first blank column, in the first cell put Table 1 and copy down to the
bottom. In the second blank column put Table 2 and copy down to the bottom.
Now take the last 3 columns ie Table 2, Time and price and drop them directly
underneath the first 3, so your data now looks like this (make sure you put a
heading on the Table column):-

Table Time Price
Table 1 05:00 2500
Table 1 04:59 2500
Table 1 04:58 2501
Table 1 04:57 2500
Table 2 05:00 3000
Table 2 04:59 3001
Table 2 04:57 3000
Table 2 04:56 3001


Select the entire table of data including the headings and do Data / Pivot Table
& Chart report

Hit Next / Next / Finish (leave the default of putting it on a new worksheet).

Now from the dialog box that appears, drag the Time field to where it says 'Drop
row fields here' and drag the Table field to where it says ' Drop column fields
here'. Now drag the Price field into the middle where it says 'Data'

You should now have an ordered list with all the gaps in the right places.
Select the entire Pivot table and do Edit / Copy, then Edit / Paste Special /
Values. Select the entire column headed Table 1 and do Edit / Go To / Special /
Blanks and then just type = (don't hit enter yet) and hit the RIGHT arrow on
your
keyboard once, and hit CTRL+ENTER at the same time. Repeat this for the
column Table 2 but type = and then hit the LEFT arrow once and then
CTRL+ENTER together.

Finally, Select the entire table again and do Edit / Copy, then Edit / Paste
Special / Values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

small excel project
 

Thanks for the replies. Very helpful!


---
Message posted from http://www.ExcelForum.com/

small excel project
 

My pleasure - Just shout if you need any help with it.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"leonard " wrote in message
...
Thanks for the replies. Very helpful!


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

small excel project
 

Thanks for the replies. Very helpful!


---
Message posted from http://www.ExcelForum.com/