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 3000-5000 rows in each data set.
From a companion data set, I've identified ...say... 500 rows that I want to isolate in the original data set so I can examine those rows in more detail. As an example, for the rows listed below, I've determined that I want to pull out
the rows ending in 0102, 0301, and 0305.
I can do this manually, spending a heck of a lot of time on each search.
I'm hoping that someone can suggest a better way to do this.... i.e. use the list of 500 sample numbers to isolate the rows out of a table with 5000 rows.
Thanks!
| IRM2014010101 |
| IRM2014010102 |
| IRM2014010103 |
| IRM2014010104 |
| IRM2014010201 |
| IRM2014010202 |
| IRM2014010301 |
| IRM2014010302 |
| IRM2014010303 |
| IRM2014010304 |
| IRM2014010305 |
9 Replies
Hi FishDoc ,
I used power query on your sample file:
Step 1:
Made your exclusion list into an excel table, and did a power query on it:
Step 2:
Defined the Dataset into an excel table, and did a power query on it:
Step 3:
Did a Merge Query:
When you get to this dialog box I selected the Exclusion list as the Left (top)
and selected the Dataset as the right (bottom), then selected the Anti-Right Join,
meaning only show the rows in the Right table that does not match the Left table.
The result:
File is attached.
Cheers
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?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?
