Progress Chart for Road works

[ashokkumarkolla]

Hi,

I am working in highway project and needs to update progress the progress of road works on day to day basis.

I had attached the file for your reference.

In sheet "data Input" is being entered in the format specified.

I need to update sheet "update the output chart format" each and every day, so i request to help me in automating this process.

I had tried and spend time in solving this time consuming process, and searched the web also.

Please help me.

Thanks in advance,
ashok

 

[vletm]

@ashokkumarkolla
Questions:
Is x-scale only from '000+990' to '002+000'? Interesting format.
If more, could this turn 90degrees to clockwise?
Are sides 'just' LHS & RHS?
Are codes those from 'output chart format' and just that order?

 

[ashokkumarkolla]

Hi,

It is the format used in road project.

1. My project road length is 86.057 kms. The plus symble format used in X-scale is like 0 km and 900 meters. That means total end value will be 86060.

2. The sheet can be turned to 90 degrees clockwise.

3. Sides are only LHS & RHS.

4. Codes shown in "data input" is for reference. the total codes are shown in "update the output chart format".

5. The codes shown are for reference and can be changed to project requirement (as i had given the codes based on the activity myself).

Thanks
ashok

 

[vletm]

@ashokkumarkolla
Check this version ...
Ideas ... Questions?

 

[ashokkumarkolla]

Hi,

Thanks a lot for your great support & time spent for me.

minor corrections needed in macro.

1. Chainage value 000+990 etc. shown in "output" sheet is a number i.e. (990 etc..).
+ sign is for viewing only (i am using number formatting 000+000)

2. In Sl No-1 of "data input" from 1500 to 2000 LHS , but the merging of cells in "output" is to be done from 1510 to 2000 lhs. (Like that all)

3. If the values with same code & Side are overlapping while merging, The first one has to be retained and the next one to be continued for remaining length.

For your reference i am attaching another file.

Thanks,
Ashok

 

[vletm]

@ashokkumarkolla
1) 'data input' and 'output' from-to -values should be same in both sheets.
I just had to make 'some things' that those values works with the 1st version.
If You need any changes, please, do those to sheets and after that I'll check what have to change.
2) 1500 to 2000 LHS, output starts from 1500 and ends to 2000,
if You want 1510 to 2000 LHS, then You should write 1510 to 2000.
Or of course, it's possible to do always like 1500-2000 means 1510-2000.
It's Your choice!
3) Two overlapping merge isn't so good idea! Can You make it manually?
No 001+000 - 001-400 and 001+350 - 001+500
=> 001+00 - 001+350 and 001+400 - 001+500
five 'blocks' are missing from the first part.
But I can make this different way (not to sheet-merge); with objects and there can be 'like unlimited layers'. I made only sample output layout for You.
>> You give Your answers/changes and
after that I can make changes for You...

 

[vletm]

@ashokkumarkolla
No answers ...
anyway this makes possible to have overlaps ...

 

[ashokkumarkolla]

hi,

Sorry for late response.

First i had checked the two files 1. merging cells 2. Using objects.

My opinion is use of merging the cells is better for my project ( by using the objects i have to manually check the overlaps and correct them, there may be errors due to human mistakes).

I had entered the full data of my project in the file "merging cells".

It had taken only 2 min.... to update the entire data and The size of file is only 2.4 mb.

Thanks a lot sir for all the work and time spent by you for me.

Request you to fine tune the code for following.

1. In Column "AB:AB" + sign is to be removed. (i can use number formatting to show the + sign) Sorry i should have attached the file without + sign in the format.

2. 1500 to 2000 LHS, output has to start from 1501 and ends to 2000

3. In the file attached Column "G" in "data input" sheet error is showing in some cells.

4. overlapping of merged cells:

I want to clear the overlapping manually. but verifying the overlaps manually is time consuming.

The instance which is overlapping is NOT to be merged and to be shown in
sheet "Overlap".

so that i can correct the overlaps manually.

Thanks a lot for your precious time spent for me.

Ashok

 

[vletm]

@ashokkumarkolla
A) Overlapping can make both way without manual checking!
You just didn't ask to make mark of those... Not a problem.
B) I would be nice to see the real data ... but I don't use '.rar'-format at all. 'zip'-format would be possible.
1) '+'-sign made extra work ... but without it quicker.
2) '1500 to 2000' - #5 Reply said output '1510 to 2000' and now '1501 to 2000'
You've used '10'm steps before ... now '1'm steps?
3) 'G-error' marks noticed error rows, which cannot 'move' to output.
4) Same way, it's possible to 'mark' overlaps too.
Hmm.. some time those overlaps would be right too? If same works have to do twice or more times within same area...
>> I can make many of those ... but the new 'non .rar' - file would help too.

 

[ashokkumarkolla]

Hi,

Attached the zip format.

1m steps due to input data is like that.

If overlaps can be marked same way, it will be nice.

thanks
ashok

 

[vletm]

@ashokkumarkolla
Here You are ...
It took here ~32/50sec to run (50sec if 'Overlap Check').
You can see 'error' better,
There are ... few kind of ... and 'Overlaps' too.
(RowNum OVERLAP Si.No.)
You can 'filter' error to make easier checkings...
If You click 'From or To'-values, You'll move to output ...
If You click in 'output' any of those 'boxes' then You'll get information of it.
'Output's' colors row 10, sets columns boxes-colors.
There are (and have to be) all those rows, for some checkings ... or no work!
>> Ideas ... Questions?

 

[ashokkumarkolla]

Hi,

I am amazed with your work.

using the file and trying to adopt the application.

Will reply in few minutes.

 

[ashokkumarkolla]

Hi,

Thanks once again for your help.

I need help for 2 modifications.

1. The maximum and minimum values set as 0 & 86060 in AB:AB of Sheet "output" is ok for this project.

For my another project the maximum & minimum values are varies. Can you please change those values to variable so that i can modify them as per the requirement?

2. The 1m steps i had shown is due to the values in From & To chainages in data ( for example 1001 to 1008 ). I assumed that by filtering the AB:AB only 10m increment can be shown. My assumption is found wrong.

Can you please change the increment (1m steps) to variable so that i can change the increment as per requirement?

Thanks for your Support
Ashok

 

[vletm]

@ashokkumarkolla
1) 'min value' is always 0. You can set 'max value' to [output!AB10] like 86060 with 10steps, there have to find 'max value' in column AB (below cell AB10).
All rows will set fixed height between 0 and 'max value'!
2) 'output'-sheet has 10m step/row. Like '000+040' top is 40m and bottom is 50m. If values has given like 45 then 'boxes' top start from middle of '000-040'-cell and so on (not 10m steps; it's real value).
3) You cannot filter 'well' output-sheet! There would be challenges.
4) If You click 'data input'-sheets H- or I-column cell then You will move to 'output'-sheet.
5) Codes are 'sensitive'. C&G is different than c&g and so on! If 'Side' is wrong then also 'Code' will be wrong. => 'error'
>> Ideas ... Questions?

 

[vletm]

@ashokkumarkolla
For some reason I noticed few things that have to change and so on ...
1) There were some 'code for my test purpose' left.
2) Overlapping: I tried make better rules =>
But ... how do it work with You values?
Like: 10..100 and 100.. 200 or 100..200 and 100..200
Q: Do those Overlap? You have some this kind of cases!
>NEW> I add 'overlapping bars to data input'. You can compare/see ... how do some cases overlap ... or not. Marks (F),(FT),(T) tells which or both From/To triggers possible overlapping.
>> Still this data takes 'only' 30sec (no Overlap Check) or 'just' 50sec (with Overlap Check).
>>>> Ideas ... Questions?

 

[vletm]

@ashokkumarkolla
... still something make me do ...
You can 'verify which overloops are valid = real'.
After that those 'valid overloops' don't give message anymore.
'output'-sheet has the most left/right cells 'visual information' how much works has done ... more red more work.
>> Ideas ... Questions?

 

[ashokkumarkolla]

hi,

Simply superb work you had done.

Its amazing to see the results.


in real time work i observed minor problem.

When data modified in Sl no column (Column A:A in "data input" sheet ) start with 1, The Overlap Check Box and Data to input Button is cleared and not visible after running the code.

If input is start with 2, Overlap check box alone is cleared after running the code.

If input is start with any other number below 1054, the running process becomes very slow.

Thanks,
Ashok

 

[vletm]

@ashokkumarkolla
Yes, yes, yes ... that's true, so far it works so because ...
after press [data to output], it takeaway 'boxes' ... and those 'boxes' names are just those [Si.No]'s ... and it has a challenge if no Si.No. -name box found!
Now, I made [Hide Boxes]-button.
So far, it should press ALWAYS BEFORE change any of those 'Si.No's!
I've few ideas to avoid this feature, but now You could test this!
Si.No. 's HAVE TO BE UNIQUE!

 

[ashokkumarkolla]

Hi,

The values from A2 to A715 in Sheet "data input" are from 1054 to 1767.

If change them as 1 to 714, and click the data to output TAB,

the Overlap check box in K1 & data to output TAB in M1:N1 is not visible after wards.

Pl find enclosed the file after run of macro.

Thanks
ashok

 

[vletm]

@ashokkumarkolla
That was tricky ... not next time!
I gotta do 'few' changes ... no more buttons!
But You can use three cells as a button
[K1] = Overlapping TRUE/FALSE ( just click it to change )
[M1] = DO IT ... move data to output ... it starts at once!
[O1] = BOXES = 'Reset'-boxes (GOTTA DO BEFORE CHANGE Si.No.'s)

 

[ashokkumarkolla]

Hi,

The From & To chainages in "Out Put" sheet are not matching with input data like From 500 to 1000 is being shown as 509 to 1028.

attaching the file for your reference.

Thanks
ashok

 

[vletm]

@ashokkumarkolla
Good that You're testing many ways. It takes time!
I noticed that kind of unwanted feature ONCE (too much).
Interesting that You're testing with only one row, good.
After I add 2nd line, no more false position... ?
I track 'from-to'-values, no problems after that one false!
Anyway, I made one change with code,
it rereads 'from-to' values again just before solving positions.
Have You noticed that You can 'jump' by-one-click from 'data input' to 'output' by click wanted rows 'H or I'-columns?

 

[ashokkumarkolla]

Hi,

1. for your reference i had shown only 2 values in the previously attached file.

2. I noticed the jump by one click from at input to output. It is very good for checking overlaps etc...

3. The objects placement being shown for different codes in output are not relevant to the data which was given in input.

for example, Sl No.1 in Input contains From 27420 to 28650 RHS BC.

If you check the object which was in Output, object starts from 28430 to 29700.

If the object is clicked the pop up window is showing correctly, but the placement of box is incorrect.

pl help,

Thanks
ashok

 

[vletm]

@ashokkumarkolla
2) You can see the best way those overlap with 'data input'-sheet boxes

3) Si.No. place in output ...
top of box starts 027+420

middle of box has RFI No.

and bottom of box 028+650

Would You tell what is wrong?

 

[vletm]

... 'data input' Overlapping-boxes