Forum Discussion
matching rows in complex data set
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?
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
FishDoc You real data is slightly different than your original example where you suggested to look at the last four numbers in the Habitat Reference. But now you seem to look for the entire IRM-code.
In the attached file, I moved the shorter Reference list to a separate sheet and gave it a name "reference" used in the MATCH formula. Then I also put the headers in one row (you had "Habitat" and "Reference" is two separate cells). Finally, I put a filter on the longer list, so now you can select only TRUE in column A.