Match record number across sheets based on data from multiple columns

I am working on a database that will hold data collected from surveys of beaver dams. I have data that is singular for each dam (i.e. dam length, pond depth), double for each dam (i.e. vegetation from river right and river left), and data that is multiple for each dam (i.e. waypoints taken across the length of the dam).

I currently have the single, double and multiple data types in different sheets ('dam assessment', 'habitat assessment', 'waypoint data') that have corresponding event data but I would like to find a way to reference each dam to each other using a record number.

In my view, each dam in the 'dam assessment' sheet would have a record number that would then be used across the 'habitat assessment' and 'waypoint data' sheets. I would like this to happen automatically by using some formula in the record number column in 'habitat assessment' and 'waypoint data' that references data in the 'dam assessment' sheet. In 'dam assessment' there are two columns that give a waypoint start and waypoint end for each dam and in 'waypoint data' the waypoints are listed.

Ultimately, this data will be used in ArcGIS to map the beaver dams with the dam assessment and habitat data in the attribute table so there needs to be at least one value that is consistent across sheets for each beaver dam in the database.

I'll attach an example spreadsheet that shows what kind of data and format I'm dealing with.

Thank you for your help. I apologize if anything in my request is unclear but I'm still working on my excel vocabulary. Please let me know if there's anything I could clear up that would help solve the problem.

Hi daffodil11, thanks for answering my call for help.

I am trying to fill in column A (Record number) on 'habitat assessment' and 'waypoint data' with the record number that matches from column A in 'dam assessment. I think that using the Date (column B) and Site (column C) along with the dam number (M in 'dam assessment' and 'habitat assessment) would work for filling the record number in the 'habitat assessment' sheet.

In the waypoint data sheet, I would need to use the data in AF and AG of 'dam assessment' to match with K of 'waypoint data.'

Hope this helps. Thanks again!

Waypoint Data, A9=

=SUMPRODUCT(('Dam assessment'!$AF$11:$AF$1474='Waypoint data'!K9)*('Dam assessment'!$A$11:$A$1474))+SUMPRODUCT(('Dam assessment'!$AG$11:$AG$1474='Waypoint data'!K9)*('Dam assessment'!$A$11:$A$1474))

and copy down as needed

Note: a lot of the points listed on Waypoint Data do not seem to correspond to Dam Assessment records.

Dam Beavers.xlsx

Here's my finished result.

I tried the formula but received an error message saying that the formula refers to empty cells. I asked it to trace and it gave me this:

Screen Shot 2013-08-08 at 1.44.18 PM.png

My formula bar looks like this

Screen Shot 2013-08-08 at 1.48.41 PM.png

If you have the time could you explain how your formula works?

Oops,

Nevermind that last post. I was putting things into a mockup I had made a couple days ago. I'd still be interested in hearing what your thought process was on the problem.

I'm not so sure, but I can break down the formula logic so you can reverse engineer it and apply it to other scenarios in the future:

SumProduct adds up the sums of multiple arrays, so that {1,2,3} * {4,5,6} = (1*4) + (2*5) + (3*6) = 28

When applied to nonnumerical arrays with an operator, it matches them for the criteria specified, so that {bob,tom,frank=bob}*{1,2,3} = for every bob in {bob,tom,frank}, it multiplies the values out against the other array and then adds them together. This is extra useful for arrays where the criteria occurs more than once:

sumproduct((bob,frank, tom, bob, bob, tom=bob)*(1,2,3,4,5,6)) = 1 + 4 + 5 = 10

Applied to your situation:
=SUMPRODUCT(('Dam assessment'!$B$11:$B$1474='Habitat Assessment'!B8)*('Dam assessment'!$C$11:$C$1474='Habitat Assessment'!C8)*('Dam assessment'!$M$11:$M$1474='Habitat Assessment'!K8)*('Dam assessment'!$A$11:$A$1474))

is doing:

=SUMPRODUCT((All Locations=Habitat Assessment Location in B8)*(All Dates=Habitat Assessment Date in C8)*(All Dam #s='Habitat Assessment Dam # in K8)*(All Records))

It finds about 10 matching locations, of those find about only 4 matching dates, of those finds only one matching dam #, and multiplies that the matching record, which there is only one, and adds them up, still getting just that one.



The waypoint SumProduct is working the same way, its just doing it twice, once for each waypoint with an output that will always be "a record # + 0" or "0 + a record #"

Genius. Thank you so much for your help.

Ok,

So, some more problems on my end. The formula works correctly in your file but when I copy and paste the formula into my original database it gives me there error that the formula refers to empty cells and returns the value 0.
Screen Shot 2013-08-12 at 9.44.06 AM.jpg

I understand that the reason for the empty cells error (the array comes down to row 1474 so that new data can be added without the formula screwing up) but I don't understand why the return value is 0 in my database but it works in Dam Beavers.xlsx. I've checked and it's definitely the same formula.

Also: The formula for the Waypoints sheet doesn't seem to be functioning quite correctly. The first 7 rows should have a record number of 00001 because they refer to the waypoints across the first dam in 'Dam assessment.' The next 5 would have the record number 00002. In an ideal world it would look something like this: Screen Shot 2013-08-12 at 9.59.46 AM.jpg

I really appreciate your help in working through these kinks. You are a wizard.

Ok, I've solved the first problem but the problem with the waypoint sheet still exists

What is the actual result you are getting?

Here's what it looks like on my end: Screen Shot 2013-08-12 at 10.23.05 AM.jpg

Multiple waypoints apply to each dam. So, for instance, Record 1 has the waypoints 131 through 137 inclusive for the Site name 'North Creek' and Date '20130718' which I probably didn't express clearly. So that's why the first 7 rows of the waypoints tab should all be 00001. This may complicate things significantly. I'm not sure.

Thanks for getting back to me so quickly.

Though waypoints might have the same number, if they come from different survey events, they will correspond to a different lattitude and longitude.

Your formula seems to be getting closer to the right thing. This is what the database looks like now: Screen Shot 2013-08-12 at 11.27.52 AM.jpg

Note: I added a couple columns for 'Datum' and 'Zone' so I changed K9 to M9

Okay, I found the source of one of the issues.

Records 1, & 2 all share the same location, same date, and overlapping Waypoints.

1 has waypoints 131-137

2 has waypoints 137-142

Which record does Waypoint 137 belong to?

Do the records have any other unique identifiers?

137 belongs to record 1, my mistake. Thanks for catching it.

The records don't have any other unique identifiers besides date, site name, and surveyor. It seems like that should be enough. There shouldn't be multiple waypoints that refer to the same dam unless there's a mistake on the part of the surveyor.

Ok, found the last issue. Records with only 1 waypoint associated with them need to have start and end listed as the same number.

Blank Cells register a value of zero, so that cell was being picked up within all the ranges.

Original formula seems to still work.


(I modified the K to M)
=SUMPRODUCT(('Dam assessment'!$B$11:$B$1474='Waypoint data'!B9)*('Dam assessment'!$C$11:$C$1474='Waypoint data'!C9)*('Dam assessment'!$AF$11:$AF$1474<='Waypoint data'!M9)*('Dam assessment'!$AG$11:$AG$1474>='Waypoint data'!M9)*('Dam assessment'!$A$11:$A$1474))

This is awesome. Just awesome. I wish I could bake you cookies or something for all the help you've given me.

Haha, it was a pleasant diversion from my work. Thanks for the feedback.

I ask almost as many questions as I answer, so feel to drop by again.