• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for creating a Table

A

ashokkumarkolla

Member
Dear Sir,

I am working in a road project which starts at Km 128850 to Km 272571, which contains obstructions / hindrances in some strips on Left Hand Side "LHS" or Right Hand Side "RHS".

The type of Hindrances are
1. LAQBalance 9.18%
2. Non payment
3. Structure obstructions
4. Trees obstructions
5. COS Works

The Clearance dates may or may not be different for each type of hindrance.

I need to present the data (Provided in Input Sheet) in the form of Table in sheet Output.

Points to note:
1. Hindrance in any side is to be considered hindrance in Road (i.e on both sides).
2. The Data to be shown in table shall be in ascending order (From Column)
3. If no hindrance in a length the same has to be treated as "available length" (i.e. available for construction of road).
4. Hindered stretches to be bifurcated based on the type of hindrance and clearance dates to be shown in respective columns accordingly.

The required output is shown in sheet "OUTPUT".

I am attaching the sample file for your review.

Request you to help me, as presently i had been working for a month to prepare the output sheet without mistakes. (The data shown in sample file is truncated and data i am working is huge)
 

Attachments

  • Hindrance.xlsx
    69 KB · Views: 15
@vletm

Hi Sir,

Good evening and Thanks for your response.

I am preparing the same manually. the procedure i am adopting is as follows.

1) as My road starts from 128850, i checked the status at the starting chainage. thus the road from 128850 to 129000 is clear from hindrances and so available.

2) From 129000 to 129850 LHS, type of Hindrance is "LAQBalance 9.18%"
(Pl refer Sl.no. 1 & 4)

3) from 129850 to 144006 LHS, type of Hindrances are "LAQBalance 9.18%" & "Non payment" (Pl refer Sl.no. 1 & 4).

4) from 144006 to 145006 LHS, type of Hindrance is "LAQBalance 9.18%"
(Pl refer Sl.no. 1 & 4)

5) from 145006 to 145500 LHS, No Hindrance thus available
(Pl refer Sl.no. 1 & 5)

...
The check has to continue up to 272571 (Road End chaiange)


The procedure is like...

Assume we are walking on the road from staring point.

Starting point to Point A - No Hindrance and Work can be done.
Point A to Point B - Hindrance A,
Point B to Point C - Hindrance A & B
Point C to Point D - No Hindrance and Work can be done.
Point D to Point E - Hindrance A & C
Point E to Point F - Hindrance B & D

etc.... up to End Point.

The same has to be prepared in output as Abstract.

Thanks
Ashok
 
thanks for reply,
same as above, the logic is the hindrance should be same from point f to G or Point G to point H. (as A&B or A&C or B&C or B&D or individual A or B or C or D ...)
 
ashokkumarkolla
You want to copy & paste those same 'Input' values to new file and
do same there.
... if You modify any value of those, You should do it twice!
Now, You can see 'TOP LEVEL' in 'Strip Chart'
and as well how those 'levels' are overlapping or 'nothing'.
It's possible to collect those as well to one table (as TOP LEVELS)
- of course, it needs coding.
 
vletm said:
ashokkumarkolla
You want to copy & paste those same 'Input' values to new file and
do same there.
... if You modify any value of those, You should do it twice!
Now, You can see 'TOP LEVEL' in 'Strip Chart'
and as well how those 'levels' are overlapping or 'nothing'.
It's possible to collect those as well to one table (as TOP LEVELS)
- of course, it needs coding.
Click to expand...
Sir,
In Strip Chart, Top Levels shall be one for any Stretch i.e. from A to B only one code will be there(i.e. top layer will be shown).
But here, The hindrances for any particular stretch may contain one or more. (i.e. From A to B, one (or) Multiple Hindrance may be there)
Note: The Hindrance in any side is to be considered as hindrance on both sides
 
Last edited:
Marc L said:
And explain row #11 (S.No 9) of your Output worksheet …
… as wrong results are impossible to reproduce !
Click to expand...
I already have a logic working for 11 rows among 13 of your result
worksheet : as both rows don't have same logical result of others rows
in exactly the same cases, I think your attachment is wrong.
So check your attachment for S.No #8 & 9 !
As I won't waste time writing any codeline if I'm not sure
to reach the expected result, the reason why an attachment with errors
is very not a good idea except to waste at least a couple of days !
​
 
Marc L said:
I already have a logic working for 11 rows among 13 of your result
worksheet : as both rows don't have same logical result of others rows
in exactly the same cases, I think your attachment is wrong.
So check your attachment for S.No #8 & 9 !
As I won't waste time writing any codeline if I'm not sure
to reach the expected result, the reason why an attachment with errors
is very not a good idea except to waste at least a couple of days !
​
Click to expand...
Sir,

checked the output and found correct, especially Sl.No 8 & 9.
I am attaching the simplified file with logic for your review.
Regards
Ashok
 

Attachments

  • table.xlsx
    14.7 KB · Views: 8
ashokkumarkolla said:
checked the output and found correct, especially Sl.No 8 & 9.
Click to expand...
Check the dates ! As others rows under same cases have not any …


Both attachments raise questions :

• from source Type of Hindrance column data aka A,B & C
why do you need in result A to E ? Why blanks columns in result ?

• Or maybe the result headers row is fixed ?
Not variable from the source data column
like it is always the same columns as Hindrance …

• Or the result worsheet is blank before executing the procedure
so the code must start creating the headers row …

As it's a mess when unexpected data are within the result
just attach a workbook with worksheets respecting the real data structure
with for the result a "before" worksheet (#2)
(where the procedure will fill data, sheet with its real name)
and an expected correct result worksheet (#3)
(no error, no missing data, no comment, no additional info, …).

Then if the result matches my logic I can write the procedure …
​
 
@vletm
Sorry for delay in response due to my ill-health.
i had tried some thing based on the support/ideas provided by you earlier (stripchart).
but end up with error in if statement.
Attaching the file for your review please.

Regards
Ashok
 

Attachments

  • trail.rar
    499.7 KB · Views: 8
Last edited by a moderator:
You must log in or register to reply here.
Back
Top