Forum Discussion
matching rows in complex data set
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.
Good morning!
I very much appreciate your assistance, and your willingness to train me in some of the finer points of Excel.
Attached is the file of my 2017 data (I have similar files for 23 years).
Column A are the sample numbers for data on fish collected by a particular type of net (a 180 m seine). Column E are the sample numbers for habitat data collected as the field crew sampled fishes using many different types of nets (this matrix actually required me to do a lot of processing to reduce/reformat the data into the the simple list contained in this file). Since the fish we're working with were effectively caught only with the 180 m seine net, I need to extract the habitat data associated only with those sample numbers (Column A).
I've been doing this by brute force.... Shifting values in Column A downward to match the same values in Column E, then sorting the whole matrix on Column A and discarding rows that don't match A and E. It's a slow and painful process, and I hadn't had much luck in trying to find a way to automate the matching process.
Your guidance is much appreciated!
Jon