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

Try this:

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

Biff

"Ashley" wrote in message
...
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

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" wrote:

Try this:

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

Biff

"Ashley" wrote in message
...
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

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

"Ashley" wrote in message
...
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" wrote:

Try this:

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

Biff

"Ashley" wrote in message
...
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

It works!
Hallelujah!
Thank you so so much!

"T. Valko" wrote:

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

"Ashley" wrote in message
...
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" wrote:

Try this:

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

Biff

"Ashley" wrote in message
...
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

You're welcome. Thanks for the feedback!

Biff

"Ashley" wrote in message
...
It works!
Hallelujah!
Thank you so so much!

"T. Valko" wrote:

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

"Ashley" wrote in message
...
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" wrote:

Try this:

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

Biff

"Ashley" wrote in message
...
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