Matching column criteria in a one to many relationship

[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

 

[Otto Moehrbach]

JB
If I understand what you want, a formula is not going to do it. You
will need VBA. Let me rephrase what you said but in generic language.
You have entries in Column A. Starting in what row?
You have entries in Columns B:F in the same rows as the entries in Column A.
You have entries in Columns G:K. Starting in what row?
The entries in G:K are not necessarily related to the entries in B:F in the
same rows.
You want Excel to loop through all the rows of the entries in B:F.
For EACH row of B:F, you want Excel to loop through ALL the entries in G:K
for a match in ALL 5 columns of B:F and G:K
If a match is found, you want the entry in Column A copied to Column L. In
what row?
Is this right?
Can there be more than one match? If so, where do you want the Column A
entry copied?
HTH Otto

 

[Herbert Seidenberg]

Excel 2007
Concat(), Countif()
http://www.mediafire.com/file/tidjghmiogm/01_17_09a.xlsx

 

[Max]

One other thought ...
Assuming data in row2 down
Put this in L2, normal ENTER
=INDEX(A$2:A$10,MATCH(1,INDEX((B$2:B$10=G2)*(C$2:C$10=H2)*(D$2$10=I2)*(E$2:E$10=J2)*(F$2:F$10=K2),),0))
Copy down as far as required, to return desired results from col A. Adapt
the ranges (cols A to F ranges) to suit your actuals.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[JB Akron]

Otto, all good questions thank you for your response.

Entries in Column A start in row 2
Yes Entries in Columns B:F are in the same rows as entries in Column A
Entries in Columns G:K start in row 2
Yes entries in G:K are not necessarily related to entries in B:F of the same
row

Yes, I would like Excel to loop through all the entries in B:F to find a
match in the G:K Columns
If a match is found- I want the entry in column A to get pasted into Column
L in its corresponding row. Yes there can be more than one match.

If it would help I would like to send a small piece of a spreadsheet for you
to take a look? (e-mail address removed).

I put in the suggestion from Max below but was returned an N/A#.

 

[Max]

I put in the suggestion from Max below but was returned an N/A#.

Think it should have worked ok. Probably data inconsistency or extra white
spaces somewhere is throwing correct matching off.

Can you upload your sample file using a free filehost,
then post a link to it here?

You can use this "easy-to-use" free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply

(desensitize the data in your sample as required)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[JB Akron]

Here is the link it should be easier to see now. Thank you very much.

http://freefilehosting.net/download/445i5

 

[Max]

Your prob was data inconsistency. The data in col K were text nums, whilst
that in the corresponding col F were real nums. To convert col K all at one
go to real nums for consistent matching, just copy any empty cell, then
select col K, right-click > paste special > check "Add" > ok. That should do
it, and you'd get the results you seek. Pl press the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Max]

Another way to coerce the text nums to real nums
is simply to add a zero to col K's values in the expression,
ie amend it to: ...*(F$2:F$24=K2+0)
then copy the expression down.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---