In Progress

simple macro MAX BID FOR THIS IS $50

MAX BID FOR THIS IS $50

Simple Macro for lining up dates and prices

I have data like the attached sheets. There are two sets of dates (and associated costs). The columns are called

DateT CostT DateS CostP.

I will refer to the first two columns as dataset1 (ds1) and the second two columns as dataset2 (ds2) and also use

[url removed, login to view] to refer to the ds1 date column and [url removed, login to view] to refer to the ds1 cost column and [url removed, login to view] and ds2.Cost.

The goal simply stated is to line up these two sets of dates (and their associated costs)

such that the [url removed, login to view] is exactly 1 day ahead of [url removed, login to view]

For example, if [url removed, login to view] = Jan 5, 2009 then in the same row (but 2 columns over) [url removed, login to view]

should be Jan 6, 2009.

There are two problems with this.

1. There are some big (or small) gaps in the [url removed, login to view] data. For example if you check the attached sheet

and look at row 54, ds1.date= 12/21/09

but row 55 has [url removed, login to view] = 1/17/10.

However there may not be (and probably won't be) this kind of corresponding gap in ds2.date. So this needs to be fixed.

2. There are gaps in the [url removed, login to view] data as well -primarily on weekends and holidays. So this will also

affect the lining up of the columns as the simple goal stated above.

For issue #1, deletion of [url removed, login to view] and [url removed, login to view] is the right way to go. The only thing is that I don't want to delete

more than is necessary. For example, if the data looks like in the attached dataset called "[url removed, login to view]" then

the cells in yellow would be deleted. For example, in [url removed, login to view] there is an entry for 11/4/09

but this should not be deleted because in [url removed, login to view] there is a 11/3/09 and since 11/3/09

precedes ll/4/09, 11/4/09 should not be deleted.

For issue #2, the data which is deleted (in this case from ds1) prior to being deleted should be "absorbed"

into the remaining data. I mean that for example Saturday and Sunday data would need to be deleted from ds1

since it will not be in ds2. But the values for Friday, Saturday, and Sunday should be averaged and Fridays data

([url removed, login to view] for Friday) should be replaced with this average.

Remember that this Friday data from ds1 will be in the same row as Monday's ds2 data.

The process describe in the preceding paragraph for issue #2 should be generalized to any data where ds2 is missing

days so that the appropriate data in ds1 is deleted but first averaged.

Boy this is complicated to explain but the idea is pretty simple I think.

The macro should sort the data by date at the beginning. Also note that the lengths of ds1 and ds2 will not be equal at the start.

MAX BID FOR THIS IS $50

Skills: Excel, Visual Basic

See more: weekends only, visual basic cost, use case simple example, data entry macro, fixed prices, fixed bid, simple macro, macro, macro excel, look max, line sheet, gap, excel macro sort, excel sort date, sort macro, simple check date, excel macro row, xls visual basic, max case, sort excel date, excel delete row, missing columns data, visual basic problems excel, date monday, paragraph note

About the Employer:
( 53 reviews ) Beppu shi, Japan

Project ID: #1127499

7 freelancers are bidding on average $43 for this job

bharanisrihari

Hello, Excel VBA expert here. Please check PM for details. Thanks!

$50 NZD in 2 days
(72 Reviews)
6.5
koolsidz

Please see PMB

$42 NZD in 0 days
(124 Reviews)
5.9
microk

Let's start..

$35 NZD in 1 day
(30 Reviews)
4.5
carmenli

Hello. I guarantee high-quality and quick work. Please check PM for further details. Thank you

$50 NZD in 1 day
(20 Reviews)
4.3
BDML

Hi, I want to work in this project. Thanks

$35 NZD in 4 days
(0 Reviews)
0.0
udaybhaskark

Hi Please check PMB

$30 NZD in 3 days
(0 Reviews)
0.0
mdhassan1988

I will do your work sincerely. Give me a chance to prove myself.

$60 NZD in 2 days
(0 Reviews)
0.0