+ Reply to Thread
Results 1 to 3 of 3

Need help to retrieve some data and sort. I am a noob!

  1. #1
    Registered User
    Join Date
    09-21-2015
    Location
    Napa CA
    MS-Off Ver
    2010
    Posts
    1

    Need help to retrieve some data and sort. I am a noob!

    OK, we went out this weekend and launched a high altitude rocket. We made 95,875 feet. Over 18 miles with a two stage launch!

    We had three GPS units transmitting coordinates throughout the flight.

    I need to be able to sort the three different incoming packets by separating them based on which GPS transmitter sent the packet. They are currently all mixed together.

    So, everything is in one column. There is a data and time line with the transmitter ID in the string and then the coords are in the line below.

    I tried to use an Index and Match formula but I am not that good with Excel.

    Example:
    2015-09-19 17:33:18 KE7OIF-2>APBL10 <UI C>:
    !4051.25N/11907.08W-/A=003901*

    Where "KE7OIF-2" is the tranmitter ID and line below is the coordinates.

    The other two transmitters are: KE7OIH-1 and KE7OIG-1

    I have 921 lines to parse to separate into three columns.

    Can anyone help?

    Here is a sample:

    2015-09-19 17:33:18 KE7OIF-2>APBL10 <UI C>:
    !4051.25N/11907.08W-/A=003901*
    2015-09-19 17:33:20 KE7OIH-1>APBL10 <UI C>:
    !4051.25N/11907.08W-/A=003875*
    2015-09-19 17:33:21 KE7OIG-1>APBL10 <UI C>:
    !4051.28N/11907.07W-/A=004111-
    2015-09-19 17:33:24 KE7OIF-2>APBL10 <UI C>:
    !4051.34N/11907.05W-/A=007140*
    2015-09-19 17:33:30 KE7OIF-2>APBL10 <UI C>:
    !4051.25N/11907.03W-/A=013774*
    2015-09-19 17:33:31 KE7OIH-1>APBL10 <UI C>:
    !4051.25N/11907.08W-/A=003875-
    2015-09-19 17:33:33 KE7OIG-1>APBL10 <UI C>:
    !4051.30N/11907.01W-/A=017761-
    2015-09-19 17:33:37 KE7OIH-1>APBL10 <UI C>:
    !4051.25N/11907.08W-/A=003875-


    Thanks in advance!!

    Alan

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Need help to retrieve some data and sort. I am a noob!

    Since no workbook is attached, I arranged the data myself. See attached.
    Attached Files Attached Files
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,257

    Re: Need help to retrieve some data and sort. I am a noob!

    See attached.

    Data retrieved to columns B,C & D

    in B1

    =IFERROR(LEFT(INDIRECT("A" & (ROW()-1)*2+1),FIND("K",INDIRECT("A" & (ROW()-1)*2+1),1)-1),"")

    in C1

    =IFERROR(MID(INDIRECT("A" & (ROW()-1)*2+1),FIND("K",INDIRECT("A" & (ROW()-1)*2+1),1),8),"")

    in D1

    =IFERROR(INDIRECT("A"&ROW()*2),"")
    Attached Files Attached Files
    Last edited by JohnTopley; 09-21-2015 at 03:17 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data Noob
    By gdary in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-07-2015, 11:15 AM
  2. [SOLVED] NOOB needs help with updating data from userform
    By peepaj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-12-2014, 07:49 PM
  3. Noob needs help w/ filling data
    By gdavis67 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2013, 10:06 AM
  4. VBA Noob - Sort Sheet on Worksheet Change
    By phatty70 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-11-2012, 02:55 AM
  5. Write a macro to retrieve data according to comment and sort for date
    By Sa DQ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2012, 02:27 AM
  6. Noob! sort growing database with vba macro
    By pbecks1963 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2011, 05:36 AM
  7. Retrieve sort columns. Excel 2003.
    By Vagelisr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-29-2010, 09:10 AM

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