J
JB Akron
I need to create a formula or function that evaluates criteria from 5 (B-F)
columns and matches it to another 5 (G-K) columns. If all the columns are
equal (combination of text and numbers) then a site identifier in Column A
needs to be copied and pasted to another column (L).
Here is where it gets fun- Each site identifier in Column A (e.g. S1, S2,
S3 etc) which is described by Columns B-F can relate to many individual
records in Column G-K. In other words there are many more records in columns
G-K about 5 times more. So the formula will have to look through a range of
information in B-F to find a match.
Essentially the B-F data represents a site location- "Watershed Basin Code",
"Stream Code", "River Name", "River Mile" and "Year". These same column
headings are in G-K but relate to separate species information. Thus you can
have 20 species at one site- so the formula would potentially to recognize
that there are 20 S1's and maybe only 5 S2's etc. There are instances where
certain sites do not have bio data and vice versa.
I imagine it will have to be a long complicated formula in which I am
stumbling to find a foundation.
Thank you for your time and consideration
JB
columns and matches it to another 5 (G-K) columns. If all the columns are
equal (combination of text and numbers) then a site identifier in Column A
needs to be copied and pasted to another column (L).
Here is where it gets fun- Each site identifier in Column A (e.g. S1, S2,
S3 etc) which is described by Columns B-F can relate to many individual
records in Column G-K. In other words there are many more records in columns
G-K about 5 times more. So the formula will have to look through a range of
information in B-F to find a match.
Essentially the B-F data represents a site location- "Watershed Basin Code",
"Stream Code", "River Name", "River Mile" and "Year". These same column
headings are in G-K but relate to separate species information. Thus you can
have 20 species at one site- so the formula would potentially to recognize
that there are 20 S1's and maybe only 5 S2's etc. There are instances where
certain sites do not have bio data and vice versa.
I imagine it will have to be a long complicated formula in which I am
stumbling to find a foundation.
Thank you for your time and consideration
JB