Listings based on a partial cell search?

tezgm99

New member
Joined
Jun 5, 2020
Messages
2
Reaction score
0
Points
0
Location
BC, Canada
Excel Version(s)
365
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.
 

Attachments

  • AutoCAD Setup_reduced.xlsx
    104.9 KB · Views: 10
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.
 

Attachments

  • AutoCAD Setup_reduced_PQ.xlsx
    124.9 KB · Views: 8
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))
 

Attachments

  • tezgm99.xlsx
    115.7 KB · Views: 9
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","")
 
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.
 
The new functions are definitely nice, especially the backwards lookup in XLOOKUp & XMATCH, although I suspect that using PQ would be better.
 
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.
 
Glad we could help & thanks for the feedback.
 
Back
Top