Chalenging stuff , got to read the entire question to understand the problem

[darlavishu]

Hii mr excel expert,

good day

I work in a construction company (ROAD PROJECTS )have come accross some bit time problem and need ur help , it will help thing get supper efficient and easy


here the goes the problem


I enter data of work that takes place on the site and enter it in to a certain format as so


------A-------B-----------C------------D------------e-------f---------g----h-------i----

-----Date----Code--CHAINAGE FROM---CHAINAGE TO---LENGTH---WIDTH---DEPTH-VOLUME--REMARKS


1---2/1/13---s1------21000--------23000-----------2000----8.5-----.2-----3400--subgrade1


2----20/1/13--s2---- 21000--------23000-----------2000----8.5-----.2-----3400--subgrade2


for a typical layer in the road can have multiple layers ( hence the coding the subgrage has three layers s1 for first s2 for the second and s3 for third), now as the data has piled up itstuff to make out if some body is giving in overlapped data of chainages ( usualy the data/progress is collected over phone )


1) need a complex formulae ( may be beside the remarks column) that can warn me when i enter overlapping data

code

2) a warning when i enter a second layer's chainages with out entering the first layer or third layer data cant be entered with out second layer's work is done for the same chainage.


3) s1 date shud come before than that of s2 .....


this solution can really mean a lot and i am sure will have applications else where as well

best of luck

 

[darlavishu]

[pre]

Date	     Layer	  From	  To	 Side	Length	Breadth	Depth	volume .
21-02-13	s1	24+ 760	25+ 000	 LHS	240.00	6	0.2	288
18-03-13	s2	24+ 760	25+ 000	 LHS	240.00	8	0.15	288
18-03-13	s3	24+ 760	24+ 880	 LHS	120.00	8	0.15	144
19-03-13	s3	24+ 880	25+ 000	 LHS	120.00	8	0.15	144
20-03-13	s1	24+ 720	24+ 760	 LHS	40.00	8.5	0.2	68

[/pre]

 

[NARAYANK991]

Hi Vishu ,


I find it difficult to correlate the data you have given in your first post with what you have given in your second ; the Chainage From and Chainage To are shown as 24+ 760 , 24+ 880 , 24+ 720 , 25+ 000 ; can I assume that these two values are in two different columns , or are they in one column ? Are they representing 24760 , 24880 , 24720 and 25000 ?


Secondly , the column labelled Side is not present in the data given in the first post ; does it carry any significance ?


Thirdly , you have given 3 requirements ; I cannot understand any of them. If we take them in order , you mention that you want a warning if overlapping data is entered ; what is the definition of an overlap ? Is it a combination of Layer Code , Chainage From and Chainage To ? To be specific , is it an overlap if the Layer Code is the same , and there is an overlap between the two sets of Chainage From and Chainage To ?


On the second requirement , will the different layer chainages match with respect to the From and To values i.e. in your first post , you have mentioned values of 21000 and 23000 for the two layers ; instead can we have values such as :

[pre]

s1     21000     23000

s2     21000     22270
If the above is possible , then can a third layer data be entered as follows :

s3     21000     21750

[/pre]
As far as your third point is concerned , your second post shows data for the first layer being entered on 20-03-2013 and 21-03-2013 , while data for the second and third layers has been entered on 18-03-2013 and 19-02-2013 ; is this valid ?


I think the best thing for you to do would be to upload a workbook containing a lot of data , where you have already worked out manually whatever you wish the formulae / macro to do , so that the correctness of the formulae / macro can be checked with your manually worked out results.


Narayan