Forum Discussion
matching rows in complex data set
I have some large data sets that i need to query to isolate specific data rows. Each row is identified by a date-based designation, with many columns of data following the date. There may be 300...
FishDoc A quick formula based solution would be to add a column that extracts the four rightmost characters from the IRM code and then finds a match in the list of selected codes.
finally, filter all the row with TRUE.
Example attached.
Now, a more solid solution would, in my opinion be to use Power Query, where you connect to both data sets and merge ("attach") data from the full set to the codes in the companion set. It would create a new data set without the need to create formulae and manually filter out the relevant rows.
Thanks Riny!
I got lost working thru the Power Query approach....
The Match and filter approach seems simpler, but have a question about what function to use for identifying the match?
I got lost working thru the Power Query approach....
The Match and filter approach seems simpler, but have a question about what function to use for identifying the match?
FishDoc The formula in my example is in column A.
=ISNUMBER(MATCH(RIGHT(B2,4),selected,0))
where "selected" is a named range. So, what's your question?
- OK, I copied column B from your example into a new spreadsheet, including the "selected" values in a labeled block at the bottom of the column. The formula was placed in the appropriate spot in column A. Whether I used the word "selected" as in your formula, or replaced it with the fixed Block range ($B$21:$B$23), I got a response of FALSE for all pairings......
Any clue what I'm doing wrong?
Thanks!FishDoc Please upload the file. Then I can have a look.