Maybe VLOOKUP?

[Guest]

Hi
I have a huge dataset, 10,000+ rows many times over, of date + hour.
(i.e. 7/1/2004 16:00)
The point of my data is that there is not a value for each consecutive date
+ time, some are missing. I need to format my data (for a statistical
analysis program) so that one column shows every possible date + hour
comnbination for a 2-year period, and another column shows a "1" or "0" for
whether or not that date exists in my original dataset or not, respectively.
For example, my data looks like this:
7/1/2004 1:00
7/1/2004 2:00
7/1/2004 4:00
7/1/2004 5:00
And I want it look like this:
7/1/2004 1:00 1
7/1/2004 2:00 1
7/1/2004 3:00 0
7/1/2004 4:00 1
7/1/2004 5:00 1

It seems like VLOOKUP is the answer, but I can't get it to work. Am I on the
right track, but maybe have a formatting problem?

Thanks for considering!!
Ashley

 

[T. Valko]

Try this:

=--ISNUMBER(MATCH(B1,A$1:A$10000,0))

Biff

 

[Guest]

Thanks for the suggestion...
But I tried:
=--ISNUMBER(MATCH(C2,A$2:A$3712,0))
but it did not work. "A" is the original data (I'm using a smaller data set
for practice), "C" is all possible date + hour combinations over the data
span, and "B" is a value that I'd like to be returned in the case of a match,
but I'd happy with a TRUE or FALSE.

Also, when I drag to fill subsequent cells with the formula, it changes the
"A2:A3712" range to "A3:A3713", etc. (But I do want it to change "C2" to
"C3".)

So I tried:
=--ISNUMBER(MATCH(C2,A:A,0))
Which didn't work either.

What does the "--" after the equal sign mean?

Thanks again
Ashley

 

[T. Valko]

What does the "--" after the equal sign mean?

Without those "double minus" signs, the formula would return either TRUE or
FALSE. The "double minus" coerces the TRUE to 1 and FALSE to 0.

Also, when I drag to fill subsequent cells with the formula, it changes
the
"A2:A3712" range to "A3:A3713", etc
=--ISNUMBER(MATCH(C2,A$2:A$3712,0))

That's what the "$" do. They "lock" the range rows from changing when
copied.

If this formula doesn't work then you have data "problems". One column may
be TEXT and the other may be true Excel date/time values (which are really
just numbers formatted to look like a date/time). There may be leading and
or tailing spaces or some other unseen characters in one or both columns.

Biff

 

[Guest]

It works!
Hallelujah!
Thank you so so much!

Without those "double minus" signs, the formula would return either TRUE or
FALSE. The "double minus" coerces the TRUE to 1 and FALSE to 0.


That's what the "$" do. They "lock" the range rows from changing when
copied.

If this formula doesn't work then you have data "problems". One column may
be TEXT and the other may be true Excel date/time values (which are really
just numbers formatted to look like a date/time). There may be leading and
or tailing spaces or some other unseen characters in one or both columns.

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

It works!
Hallelujah!
Thank you so so much!