Results 1 to 8 of 8

Thread: Listings based on a partial cell search?

  1. #1
    Neophyte tezgm99's Avatar
    Join Date
    Jun 2020
    Location
    BC, Canada
    Posts
    2
    Articles
    0
    Excel Version
    365

    Question Listings based on a partial cell search?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone. I'm trying to find out if I can simplify the process from our GPS survey machine. I've attached a reduced version of the spreadsheet - it does come with a bunch of other tabs, but it puts it over the size limit so I've cut those out.

    Basically, in the 'ExtractAll' tab, I'm wanting to have formulas to find the data from the 'Raw Data' tab and put it in the Extract 1 part, unfortunately it's not been as simple as I was hoping. I figure if I can get the Label column right, the others can use the same function to get their data. The labels all start with PN, but the rest of it is whatever you labelled the point when surveying (so PNTOC3 for example would be the third point I've taken for the top of curb of a roadway) and that's been my issue - can excel find data on a partial search (something like "find data in column B of the 'Raw Data' tab starting with PN")?

    I cheated with the 'Sheet1' tab by copying the data I wanted from 'Raw Data' and just forcing the 'ExtractAll' tab to use that. But this was a small survey of only about 100 points, I don't want to have to do that with a large survey as it would be very time consuming. There isn't really a row pattern with the PN's either; sometimes there's a gap of 7 rows, other times it's 9.

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    119
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    This is a good application for Power Query aka Get & Transform. In the attached file I created a query to return your "Extract Step 2" from the data on the Raw Data tab. If you're not familiar my basic approach was to pull the data into the PQ editor, then filter for rows in column A containing GS. In another copy of the raw data I filtered for rows in column A containing BL. I then merged these tables on the columns containing the label to combine the coordinate and standard deviation data. I then stripped off the leading characters and converted to numerical values.

    What is the original source of the raw data? Is it a csv file? Power Query can load the data directly from that file. When working with csv files I like to place them in a folder and then select the one I'm interested in analyzing, using a query to find the files needed for the dropdown list.
    Attached Files Attached Files

  3. #3
    Conjurer Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    103
    Articles
    0
    Excel Version
    O365
    Hi & welcome to the board.
    If you have the new functions, then
    in B8
    =FILTER('Raw Data'!B1:E845,LEFT('Raw Data'!B1:B845,2)="PN")
    F8
    =INDEX('Raw Data'!E$1:E$845,XMATCH("Solution=RTK Fixed",INDEX('Raw Data'!$B$1:INDEX('Raw Data'!$B$1:$B$845,MATCH($B8,'Raw Data'!$B$1:$B$845,0)),0),0,-1))
    G8
    =INDEX('Raw Data'!F$1:F$845,XMATCH("Solution=RTK Fixed",INDEX('Raw Data'!$B$1:INDEX('Raw Data'!$B$1:$B$845,MATCH($B8,'Raw Data'!$B$1:$B$845,0)),0),0,-1))
    Attached Files Attached Files

  4. #4
    Conjurer Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    103
    Articles
    0
    Excel Version
    O365
    You could also use these, to get the stage 2 layout
    B8
    =SUBSTITUTE(FILTER('Raw Data'!B1:B1000,LEFT('Raw Data'!$B$1:$B$1000,2)="PN"),"PN","")
    C8
    =SUBSTITUTE(FILTER('Raw Data'!D1:d1000,LEFT('Raw Data'!$B$1:$B$1000,2)="PN"),"E ","")+0
    D8
    =SUBSTITUTE(FILTER('Raw Data'!C1:C1000,LEFT('Raw Data'!$B$1:$B$1000,2)="PN"),"N ","")+0
    E8
    =SUBSTITUTE(FILTER('Raw Data'!E1:E1000,LEFT('Raw Data'!$B$1:$B$1000,2)="PN"),"EL","")+0
    F8
    =SUBSTITUTE(MID(INDEX('Raw Data'!E$1:E$845,XMATCH("Solution=RTK Fixed",INDEX('Raw Data'!$B$1:INDEX('Raw Data'!$B$1:$B$845,MATCH($B8,'Raw Data'!$B$1:$B$845,0)),0),0,-1)),9,10),"m","")
    G8
    =SUBSTITUTE(MID(INDEX('Raw Data'!F$1:F$845,XMATCH("Solution=RTK Fixed",INDEX('Raw Data'!$B$1:INDEX('Raw Data'!$B$1:$B$845,MATCH($B8,'Raw Data'!$B$1:$B$845,0)),0),0,-1)),9,10),"m","")

  5. #5
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    119
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Very nice, Fluff, I was pleasantly surprised to find that those functions are available (though sadly not on my work PC). Your use of the search backwards option in XMATCH came in handy here.

  6. #6
    Conjurer Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    103
    Articles
    0
    Excel Version
    O365
    The new functions are definitely nice, especially the backwards lookup in XLOOKUp & XMATCH, although I suspect that using PQ would be better.

  7. #7
    Neophyte tezgm99's Avatar
    Join Date
    Jun 2020
    Location
    BC, Canada
    Posts
    2
    Articles
    0
    Excel Version
    365
    Thanks to both of you. I had a spill error but that was due to not clearing the cells first, once I did that, it worked.

  8. #8
    Conjurer Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    103
    Articles
    0
    Excel Version
    O365
    Glad we could help & thanks for the feedback.

Posting Permissions

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