Yet another Excel question

Factual Questions
1

I have an Excel worksheet with four columns. Column A contains all dates from 1/1/2008 to 31/12/2008 and Column B reference values corresponding to the date from column A.

Col. C contains dates from actual measurements and col. D the values on these dates. The dates in col. C are not consecutive.

Here is an example



2/1/2008	806,732 	9/1/2008	1,036
3/1/2008	814,726 	14/1/2008	1,019
4/1/2008	821,875 	17/1/2008	0,999
5/1/2008	822,882 	24/1/2008	0,997
6/1/2008	822,882 	28/1/2008	0,997
7/1/2008	822,882 	31/1/2008	0,997
8/1/2008	818,835 	4/2/2008	0,997
9/1/2008	815,214 	8/2/2008	0,987
10/1/2008	812,855 	12/2/2008	1,009

(Values in column D are passed through a formula, that’s why they are way off from reference values in B)
Besides cutting and pasting by hand, is there any quicker way to discard the dates that there’s no actual measurement and match measured values to reference values?

2

The discarding part is not easy, but let me skip over that and show you how to match up values to references.

In Column E, add a formula in E1:

=VLOOKUP(C1,A:B,2,FALSE)

The value produced by this formula will be the reference value in Column B corresponding to the date in Column A that matches the date in Column C. If the date is not found in Column A, the result will be #N/A.

Then copy or fill-down the formula you just put in E1 in all the rows in E.

To discard unused values, you probably want to sort columns C & D to see matches easier, then manually cull out unused data. Or after doing the above steps you can manually cut out data that results in #N/A. This could be done automatically if you write VBA code. If you are desperate to do that I could help but I’m taking the rest of the day off :smiley:

Merry Christmas :slight_smile:

3

:smiley:

This worked like a charm! Thanks mate! :cool: