i have lot of data containing coordinates of Easting and Northing with elevation.they all a have fixed chainage and description (say a point in road center line and few points on both side of it as left side as RHS and right side as RHS and center as CL.now i and distance of each point from a fixed center line and in smaller distance to larger distance order.the LHS offset should be in negative sign.i am attaching one excel sheet for your help
One way...which may or may not work for you...
I selected A B C D an sorted by A, then B
Then in column F i put
=C1-$C$1
and copy it down until the end of that segment (the 0+070's, row 13)
Then i edited each formula to be not absolute anymore, so =C1=C1, =C1=C2, etc
Selected all of those formulas and copied to the bottom
Select column F and copy/paste special/values only
Then select all columns and sort by A then F
You don't get the empty rows however.
Here's a macro to do it for you.
Change the BOLD to however nmany rows you have.
I amde some mistakes while recording this so there's some redundant commands in there
Please Login or Register to view this content.
EDIT: Nevermind this won't work, I thought the categories were the same number of entries eacy (13 row) but they vary.
Last edited by Speshul; 08-21-2014 at 04:03 PM.
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
This code will work
Please Login or Register to view this content.
Thank those who have helped you by clicking the Star * below their name and please mark your post [SOLVED] if it has been answered satisfactorily.
Why not just sort the table manually by Chainage and Easting?
Entia non sunt multiplicanda sine necessitate
That is essentially what I did in the macro except I added the space between each unique that was in column A. I think that was the issue at hand that he didn't want to do that manually each time however that's my guess.
the VBA CODE IS NOT RUNNING.IT IS SHOWING NOT RESPONDING AFTER STARTING WELL THEN AFTER FEW MINUTES LATTER IT IS SHOWING RUNTIME ERROR.I OBSERVED MY FRIEND HAD A SHEET WHERE HE JUST PASTED A SHEET SIMILARLY MY ONE AND CLICK ON A BOTTON IT JUST MAKE IT IN PROPER ORDER AND ALSO DISTANCE OF EACH POINT FROM THEIR RESPECTIVE CENTER POINT,THERE THE LEFTHAND SIDE HAVE NEGATIVE VALUE AND HAVE A GAP AFTER COMPLETION OF EACH CHAINAGE ,WHEN I ASKED HE JUST TOLD ME THAT THE DISTANCE FORNULA FOR CHAINAGE 0+070 IS LIKE =SQRT((M$9-M8)^2+(N$9-N8)^2).HE DID NOT SAY ANYTHING MORE SO PLS HELP ME.MAY BE HE IS USING MORE CODE NOT ONE TO MAKE IT SIMILARLY AS I SENT YOU.PLS HELP ME
please don't use UpperCase, it looks like screaming.
What kind of friend you have, if he is not sharing an working solution with you?Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
ok sir sorry
pls help me me posting it again,
the vba code is not running.it is showing not responding after starting well then after few minutes latter it is showing runtime error.i observed my friend had a sheet where he just pasted a sheet similarly my one and click on a botton it just make it in proper order and also distance of each point from their respective center point,there the lefthand side have negative value and have a gap after completion of each chainage ,when i asked he just told me that the distance fornula for chainage 0+070 is like =sqrt((m$9-m8)^2+(n$9-n8)^2).he did not say anything more so pls help me.may be he is using more code not one to make it similarly as i sent you.pls help me
Since you have replies form several members it is always usefull to add to whom your responding.
Why not ask your friend if he/she has an working solution?Please Login or Register to view this content.
the vba code is not running.it is showing not responding after starting well then after few minutes latter it is showing runtime error.i observed my friend had a sheet where he just pasted a sheet similarly my one and click on a botton it just make it in proper order and also distance of each point from their respective center point,there the lefthand side have negative value and have a gap after completion of each chainage ,when i asked he just told me that the distance fornula for chainage 0+070 is like =sqrt((m$9-m8)^2+(n$9-n8)^2).he did not say anything more so pls help me.may be he is using more code not one to make it similarly as i sent you.pls help me
Please Login or Register to view this content.
many many thanks to you sir,,,,,,,,,,,,,,,,,,,,,,,really u r great
If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks