Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel - Matching data
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 |
#2
|
|||
|
|||
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? -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=475013 |
#3
|
|||
|
|||
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" wrote: 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? -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=475013 |
#4
|
|||
|
|||
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. -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=475013 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Linking the data from one excel to another | Excel Discussion (Misc queries) | |||
Getting Excel Data from One Sheet to Another.... | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |