Excel - Matching data

[Guest]

excel 2003 - office professional.
I have data for water temperature from two reservoirs taken over 5 years. I
want to match the water temperatures from the two reservoirs from samples
taken on the same day as not all samples were taken at the same time. I
therefore have four columns: 1) date of sample res 1; 2) nitrate res 1; 3)
date of sample res 2; 4) nitrate res 2.
There are uneven sample numbers for each reservoir where there was data
missing from one of the reservoirs.
Many thanks

 

[neopolitan]

Peter, how do you want to match these two sets of data - not sure I
understand? Can you attach a small example of the the data the way it
looks now and the way you want it to look?

 

[Guest]

Hi, thanks for your reply, I am not sure how to attach files on this site,
but perhaps if I explain what I mean a bit more clearly:

date1 temp1 date2 temp2
22/06 5 22/06 8
23/06 6 24/06 9
24/06 8 25/06 9
25/06 8 27/06 10

Here are the types of columns I have. There is more data available for data
set 1 than for data set 2. I would like to match up and compare the temp data
only for data from the same date and dismissing data that is only available
for either 1 or 2. In other words I do not want to manually go through the
data deleting all the isolated data (e.g. from the 23/06 above).
Hope that is a bit clearer and I look forward to your reply.
Peter

 

[neopolitan]

Peter, there are probably more automated ways to do this but this is a
fairly simple manual solution to what i think you want to do:

1. Insert two new "Helper columns" in Col E & F (Call them Helper 1 &
Helper 2).
2. In Cell E2 (Helper col 1) input the formula:
=IF(ISERROR(VLOOKUP(C2,A:A,1,FALSE)),1,"")
3. Copy that formula down for the length of the data in Col C(Date 2).
4. In cell F2 (Helper col 2) input the formula:
=IF(ISERROR(VLOOKUP(A2,C:C,1,FALSE)),1,"")
5. Copy that formula down for the length of the data in Col A (Date
1).

The result of this will be a "1" in the cells in col E & F
corresponding to unique data in Col A & B.

Now use Autofilter and filter for Non Blanks in Col E. Delete the rows
that result from this filtering. Toggle off the autofilter.

Repeat this filter and deletion for Col F.

When you toggle off the autofilter from Col F, what remains should be
data in col A-D that have common dates.

I think this is what you wanted. Please post again if it is not or
this doesn't work as described.