Maybe VLOOKUP?
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 |
Maybe VLOOKUP?
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 |
Maybe VLOOKUP?
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 |
Maybe VLOOKUP?
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 |
Maybe VLOOKUP?
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 |
Maybe VLOOKUP?
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 |
All times are GMT +1. The time now is 04:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com