+ Reply to Thread
Results 1 to 39 of 39

[Solved] Coordinate Data

  1. #1
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    [Solved] Coordinate Data

    Hi, All

    These are likely simple questions, but I've been banging my head for hours with tutorials with no joy.

    I have a csv file containing a Z coordinate only. I need to add x and y, whereas cell A1 is 1,1,"Z value", cell B1 is 1,2,"Z value", cell A2 is 2,1,"Z value". I have 480 rows and 640 columns of data, so the last cell should be 480,640,"Z value".

    I've attached a screenshot showing in A1 the desired result. I appreciate any help.

    EDIT

    To clarify, the X and Y coordinates will simply be the cell number with a comma separator. Hope that helps. Oh, and this would be my first post, so if I didn't ask properly, please let me know. Thanks.
    Attached Images Attached Images
    Last edited by bsjordan; 08-06-2011 at 09:05 PM. Reason: Clarity

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    open csv with excel put z value in sheet 2 !a1
    then in sheet 1 a1 put
    =COLUMN()&","&ROW()&","&Sheet2!$A$1 drag across 640 then down to row 480
    once done copy all of sheet 1 and paste back straight away as paste /special/ values to remove formula
    then delete value in sheet2 a1 and save as csv
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Thanks, Martin. Will try this now.

  4. #4
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Martin, was the "!" in your first statement "...sheet 2 !a1" purposeful or incidental?

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    thats just how formulas refer to sheet2 just put the z value in cell a1 of sheet2
    like this i've just filled it out a small bit
    Attached Files Attached Files
    Last edited by martindwilson; 08-06-2011 at 02:41 PM.

  6. #6
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Okay, great! That worked! I'll have to study the formula to understand the function, but it seems simplistic enough.

    The last thing I need is to convert this into a single column of data and assign a "point number". I figure this is the "Count" function, but I don't know how to append Column B to the bottom of A, C to B, etc.

  7. #7
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Uh-oh, wait a sec. I just noticed that the Z value from Sheet 2 is the "only" value. I have unique Z values for each cell.

  8. #8
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Here's a snapshot of the full file. sorry for the misunderstanding.
    Attached Images Attached Images

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    i assume it starts at a1 and all 640X40 cells are filled with z data
    use =COLUMN()&","&ROW()&","&Sheet2!A1
    then do it like this
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Perfect. So removing the $ sign was the key there, right? Thanks for this.

    Do you have any ideas about putting them into a single column format with an assigned number?

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    yep but what do you mean,you want them goimg straight down,all of row 1 then row 2 after and so on?

  12. #12
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Basically, I need a file that looks similar to the attached. With your help, we've created the xyz components. Now I need to add a point number and have all of the data arranged in 4 columns of PT#, X, Y, Z. Does this make sense? I don't feel like I'm being as clear as I could.
    Attached Files Attached Files

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    how did x/y change to non integers? since the are coordinates 1,1 2,1 etc
    also should the order be down then across or across then down

  14. #14
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    The example I attached was a different coordinate system (State Plane Coordinate System, to be exact). I maybe should have used a different example.

    This is what the first few points would ideally look like, starting with A1:

    PT# X Y Z
    1 1 1 58.51762
    2 1 2 57.85823
    3 1 3 57.78638

    That's the end result I want, but if I can get this:

    1,1,1,58.51762 in A1
    2,1,2,57.85823 in A2
    etc.

    That would be sufficient. I can edit out the commas and create the spacing I need afterwards.

    At the end of column A, I need to append column B starting with B1. At the end of B, start with C, and so on. So, we could have a single column (A) with the 4 values (1,1,1,58.51762, for example) in it.

  15. #15
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    If it makes any easier, I don't necessarily need the point number added. All I need for a certainty is to have x,y, and z. It can be comma delimited in a single column A, or three separate columns A,B, and C.

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Coordinate Data

    Maybe I'm entirely missing what you're trying to do, but it you have this on sheet1

    Please Login or Register  to view this content.
    ...and want this on sheet2,

    Please Login or Register  to view this content.
    Then on sheet2!A1 and copy across and down,

    =ROW() & "," & COLUMN() & "," & Sheet1!A1
    Entia non sunt multiplicanda sine necessitate

  17. #17
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Thanks for the reply, shg. We already have that accomplished. What I need is to convert this:

    --A--- --B--- --C--- --D---
    1 1,1,47 1,2,56 1,3,69 1,4,68
    2 2,1,15 2,2,31 2,3,90 2,4,55
    3 3,1,93 3,2,68 3,3,44 3,4,23
    4 4,1,36 4,2,75 4,3,44 4,4,85

    to this:
    1 1,1,47
    2 2,1,15
    3 3,1,93
    4 4,1,36
    5 1,2,56
    6 2,2,31
    7 3,2,68
    8 4,2,75
    9 1,3,69
    ...and so on

    Basically, append B to A, C to B, D to C, etc...

    Can you help with this?

  18. #18
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Coordinate Data

    Please Login or Register  to view this content.
    In F2 and down,

    =INDEX($A$1:$C$4, MOD(E2-1, ROWS($A$1:$C$4)) + 1, INT((E2-1) / ROWS($A$1:$C$4)) + 1)

  19. #19
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Looking at this, I think this is limited to the first 4 values in columns A,B, and C. Am I right? If so, I actually need to cover everything to cell XP480. Would it look like this?

    =INDEX($A$1:$XP$480, MOD(E2-1, ROWS($A$1:$XP$480)) + 1, INT((E2-1) / ROWS($A$1:$XP$480)) + 1)

  20. #20
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    I tried it. It doesn't seem to work.

  21. #21
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Coordinate Data

    Yes.

    And if you name the data tbl, you can simplify the formula to

    =INDEX(tbl, MOD(E2-1, ROWS(tbl)) + 1, INT((E2-1) / ROWS(tbl)) + 1)

  22. #22
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Coordinate Data

    It does. Try again.

  23. #23
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    I know it's basic, but how do I enter the formula? And how would I rename the data to tbl? Would I just save the file out to that name?

  24. #24
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Also, I don't really need to copy the data. That is, I don't need to keep columns B,C, and D after appending them to A. Since I have a 640x480 cell, I have to put the new column out in the "XT" column range. So, I think the formula may need to look like this:

    =INDEX($A$1:$XP$480, MOD(XT2-1, ROWS($A$1:$XP$480)) + 1, INT((XT2-1) / ROWS($A$1:$XP$480)) + 1)

    Does this look right?

  25. #25
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Ok, so when I hit F2, enter the command (overwriting the coordinate present in A1, I get a box in the XT2 cell. I drag it down and release, but I get nothing. Obviously, I'm missing something.

  26. #26
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    I've tried to rename the file and the worksheet to "tbl", and use the second formula you provided. Again, I see a box, but the data doesn't appear when I drag it down. Any thoughts?

  27. #27
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    1,1,45.17
    2,1,30.2
    3,1,80.91
    4,1,57.8
    5,1,69.28
    6,1,51.65
    7,1,89.32
    8,1,34.31
    9,1,31.36
    10,1,6.61
    11,1,23.01
    12,1,82.6
    13,1,9.66
    is easily doable in word
    copy the lot then past special unformatted text into word
    find replace ^t with ^p
    then copy paste back to excel
    then use text to columns using comma , as delimiter to separate

  28. #28
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    I've attached a screenshot of what I'm looking at. I'm not sure what to do with the box. I've tried grabbing the corner and clicking on the center of the box, but neither works as needed.
    Attached Images Attached Images

  29. #29
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    "find replace ^t with ^p"
    I'm not sure I understand this. I just copied and pasted into Word, but, because of the size of the file, it's still working to display it.

  30. #30
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Martin,

    It appears that the file is going to be too big for Word to handle in a reasonable way. Too much data. It's still crunching.

    I'm willing to try to work with the formula that shg has provided, but I can't get it to work properly.

  31. #31
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    This is what I'm getting in Word.
    Attached Images Attached Images

  32. #32
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    i just did it and athough it took just under 2 mins to replace in word, i now have 307200 rows in excel did you paste special unformatted text into word?

  33. #33
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Thanks, Martin. It was the "Paste Special" command that I should have used. Unfortunately, I'm now not sure how to get things formatted properly in Word. I understand what you are trying to do, and I know that once it's formatted, i can import into Excel with no problem. What is the command I need to do for Word to format properly?

  34. #34
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Coordinate Data

    I think Martin's suggestion is excellent.

    Example of the formula I posted attached.
    Attached Files Attached Files

  35. #35
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    once the find replace is done just copy all paste back into excel
    then select th column and use text to columns to splt

  36. #36
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    I'm beginning a little inadequate! I've attached what I'm looking at in Word. Not sure what I should be "finding and replacing".
    Attached Images Attached Images

  37. #37
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    again, thanks for you help, guys.

  38. #38
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Coordinate Data

    find ^t (which is tab),replace with ^p (which is paragraph mark) this is how it looks (yours will be slightly different as you have excel 2010)
    Attached Images Attached Images

  39. #39
    Registered User
    Join Date
    08-06-2011
    Location
    Decatur GA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Coordinate Data

    Success! I'm going to add a column of numbers, and I think we've got it licked. Thank you both for your patience.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1