How do I select for multiple shared values between 3 data sets?

[StrixKOtus]

Hey everyone,

I am currently trying to solve a problem with data for my undergraduate Honors Thesis.

So, I have three years of data, each with a respective spreadsheet. The first year has a less robust dataset than the other two years. Since I want to keep all my years even, I only want to use data that is shared with the first year.

I am interested in relating the three datasets with unique locations from year 1. ie: Selecting all data that have shared values and negate everything else. Each year more locations are added, but I am only interested in locations that were visited in the first year and revisited later.

Ex: Imagine something like this, but Year One has 2844 values in the column (including site replicates). By year three there were 7000 values. I am only interested in the values that are shared between all three years

1999
Location|UTMx|UTMy|Date
Warcreek
JepsonIsland
JepsonIsland
MillerPark
JonesCreek

2000
Location|UTMx|UTMy|Date
Jupiter
Voltaranch
WarCreek
Warcreek
Market
Jones Island
WarCreek
MillerPark
Jupiter
Jones Creek

2001
Location|UTMx|UTMy|Date
VoltaRanch
VoltaRanch
JonesCreek
JonesCreek
SkylineRidge
MillerPark
Jupiter
Jupiter
Jupiter
JepsonIsland
JepsonIsland
Warcreek

So my question is this: How do I select or Query for values that are shared with Year One. Is this possible? If what I am asking doesn't make sense, please let me know where I should be more specific.

Thank you so much I need to graduate!!!

-Kurt

[RudiS]

Hi,

See the process in the attached workbook.
Sheet 1 represents your original data,
Sheet 2 instructs to move the year to a new column
Sheet 3 instructs to remove the blank rows (and additional column headings) to make a proper list of data
Sheet 4 instructs to run the macro to process and mark the data
Sheet 5 instructs to filter the data for the information (I assume) you needed