Start with a tricky problem of how to detect the first and last events of a day. Bill uses a helper column with AND. Mike improves with a boolean multiplication, but then explains how to embed the helper column into a formula, both with and without Ctrl+Shift+Enter
Transcript of the video:
Bill: Hey, it's time for another Dueling Time podcast, I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun.
This is our episode 176 - Embedding a Helper Column in an Array Formula. Also, Start time and End Time!
Well Mike, this duel is a doozy, I have a solution, and I'm hoping that you have a much better solution.
Here's what we have, now, the data is going from earliest at the bottom, to latest at the top.
And you see, that for each day, there's some event that happens, right?
So at 14:16, we went from 0 to 3, and then 9, and 10, and it trails off.
And this one event happens every single day, only once every day, so everyone from 0, to 2, to 1, 64, 5, 3, 2, 0! Alright?
And then on the 7th, from 0 to 5, 3, 2, 6, down to 0.
And the goal is to find the start time and end time, for each of those events.
Alright, and here is the only way that I was able to come up with to do this.
I went down to the bottom of the data set, we're going to do =IF, we're going to check to see if three things happen: First off, is this date equal to the prior to date?
Is the prior value equal to 0?
And, is this value greater than 0?
If all of that is TRUE, then I say "first"!
This is the first event of the day.
Alright, now, what to do if it's FALSE?
Then I check and see if it's the last event of the day.
So we check to see if the date in this cell, is equal to the date in the cell above us?
If the cell above us is equal to 0?
And if this cell is greater than 0?
If all that's TRUE, then we have "last"!
If neither of those are TRUE, then it's either a 0, or something in the middle, I'm just going to put "No" there.
And then one more to close the IF function.
Alright, so THAT is going to build words for me, that indicates whether it's the first or the last, but then I’m still not done there, because what I'm going to have to do, is concatenate the date - today's date.
So I'll press F2, go right, and I should have pressed CTRL+Enter there, but I didn't.
Alright.
So we need that helper column, once we have that helper column, then it becomes something like this: =VLOOKUP of the word "First" concatenated with the date, I'll press F4 three times, to lock that down, into this, range here, three cells, F4, ,3,FALSE) And that will show me the start time each day.
To get the end time, instead of concatenating the word "First", I'm going to concatenate the word "Last".
And there you have it!
It's horrible to me, that I had to create this huge set of helper cells out here, I want to see if you have anything better.
Mike: Thanks MrExcel, and it's great to be doing duels again!
Now, I like your helper column here because check this out: That formula creates an exact look up item, first, with this serial number date, and last, and then over here, boom, you just create a VLOOKUP formula.
Now I'll take a slightly different take on this.
I'm going to notice that, in this column here, if I CTRL+Shift+` (grave accent), those are serial numbers, right?
So because they're numbers, CTRL+Z, I'm going to build a helper column, that checks whether there's a number greater than 0 over here, and multiplies it by the serial number date.
So I'm going to say, give me the serial number date, TIMES( , and this will be a boolean logical test, Are you greater than 0 ) ! Now, there's only two values that this little inner part evaluates to: TRUE or FALSE.
When it's a false, like here, FALSE* the number will give us 0, when we copy it down here and we'll get a TRUE over here, it'll be TRUE times this number, it's like 1 * this number, and it will give us the serial number, double click and send it down.
So in essence I've created a column, where I filtered out the serial numbers i don't want with 0!
Now, I don't have a unique identifier like MrExcel, but I'm going to take this little block here, use the actual serial number from this state, and do Look-up Last, and Look-up First.
Now, I'm looking up the start time, but in essence the start time is last in this list, and then the end time will be the first one.
So I'm going to come over here to start time, and I'm going to use the LOOKUP function, because the trick for LOOKUP last is, we're doing approximate match look up, and LOOKUP automatically does approximate match.
I'm going to look up a big number, and the big number is 2, because when I get to this lookup vector, I'm going to have errors and ones, so 2 will be bigger than any number one, and the way approximate match lookup works is, if you give it a big number, it will always find the last number.
The array is going to be 1/( , I'm going to highlight this whole column, CTRL+Shift+Down Arrow, F4, and I'm going to say: Are any of you equal, to this date (right here) relative cell reference?
) ! Now what this little bit right here does, F9, is that Divide by Zeroes will be the filter, and will only get 1's, where we see that serial number.
The 2 will be looked up, and because we're doing approximate, we'll always get the position of the last one.
That's how lookup_vector does, it's kind of like the MATCH function.
CTRL+Z, comma, and for result_vector, we simply give it the times, CTRL+Shift+Down Arrow, F4, and that's LOOKUP last, ) , CTRL+Enter, and double click and send it down.
I go to the last cell and hit F2, boom, it looks like it's working.
Now to get the first, we notice that if we're doing exact match, in either VLOOKUP or MATCH function, I'm going to use VLOOKUP, if we have the last argument as FALSE, any time it sees duplicates, it'll automatically get the first one.
So the LOOKUP value, boom, comma, and the table, it's this entire table, first column is the helper column, last column has the time.
CTRL+Shift+Down arrow, F4, ,3 to get the item from the third column, comma, and 0 for exact match!
That forces, when there are duplicates, to only get the first one.
CTRL+Enter, double click, and send it down.
And then we can check this, it looks like 17:48 is the start time, and 17:51 is the end time.
Now if we didn't want to have this helper column, I want to notice something: if I look at this, it's the cell from the date column, and then there's a cell from the value column, Enter, F2, Enter, F2 - anytime we have a helper column, that's looking at two columns, we can kind of simulate this, but instead of the C5 I put the whole column here, instead of the E5 I put the whole column here, right into our formula.
Now, I'm going to copy this first formula here, CTRL+V and F2, and the beautiful thing about this so rate calculation is, LOOKUP can handle array operations without any special keystroke.
So it's really that little bit right there, which is our helper column, that we're going to need to simulate, so I'm going to highlight the date column, CTRL+Shift+Down, F4, and multiply it in (, times the value column, CTRL+Shift+Down, F4, Are any of you greater than 0?
) . Now this array operation here will work, we have multiplying first, we're forcing the > operator before the multiplication, then it will multiply, and then it will get that comparative operator sign, = , CTRL+Enter, double click, and send it down.
That gives us LOOKUP Last, or the start time.
Now I'm going to cheat here, I'm going to copy this whole little bit right here, because I'm going to use this a second time, that in essence is simulating our helper column, CTRL+C, Esc, and now I'm going to use =INDEX( and look-up, the values, the times, CTRL+Shift+Down Arrow, F4, comma, and I'm going to use the MATCH function, to look-up serial number, date, comma, within this array, I'm going to CTRL+V, actually I don't need all of this, all I need, is the little part that simulates the helper column, ,0 to get only the first item when we have duplicates, )) and because that argument right there can't handle that array operation, without the special keystroke that we have to use, CTRL+Shift+Enter.
I look up to the formula bar, I see my curly brackets, double click, and send it down.
Now if you want it to get tricky, And not use CTRL+Shift+Enter, guess what, that whole array operation, we can put it inside of INDEX - like, LOOKUP INDEX is one of five functions, that can handle array operations without CTRL+Shift+Enter.
But I need the whole column, in essence, to be delivered to the MATCH, so watch this, because a column is filled with rows, inside of INDEX: comma the rows, to get all the rows delivered, I leave that argument empty.
) , and that will work with just Ctrl+Enter.
No CTRL+Shift+Enter, double click and send it down.
Alright, we'll send it back to MrExcel!
Bill: Like, 10,000 points to you!
That was the most coherent explanation, on how to take a helper column, and embed it right in the formula.
Two different ways, absolutely awesome.
Yes, it's great to be doing Dueling Excel podcasts again, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
This is our episode 176 - Embedding a Helper Column in an Array Formula. Also, Start time and End Time!
Well Mike, this duel is a doozy, I have a solution, and I'm hoping that you have a much better solution.
Here's what we have, now, the data is going from earliest at the bottom, to latest at the top.
And you see, that for each day, there's some event that happens, right?
So at 14:16, we went from 0 to 3, and then 9, and 10, and it trails off.
And this one event happens every single day, only once every day, so everyone from 0, to 2, to 1, 64, 5, 3, 2, 0! Alright?
And then on the 7th, from 0 to 5, 3, 2, 6, down to 0.
And the goal is to find the start time and end time, for each of those events.
Alright, and here is the only way that I was able to come up with to do this.
I went down to the bottom of the data set, we're going to do =IF, we're going to check to see if three things happen: First off, is this date equal to the prior to date?
Is the prior value equal to 0?
And, is this value greater than 0?
If all of that is TRUE, then I say "first"!
This is the first event of the day.
Alright, now, what to do if it's FALSE?
Then I check and see if it's the last event of the day.
So we check to see if the date in this cell, is equal to the date in the cell above us?
If the cell above us is equal to 0?
And if this cell is greater than 0?
If all that's TRUE, then we have "last"!
If neither of those are TRUE, then it's either a 0, or something in the middle, I'm just going to put "No" there.
And then one more to close the IF function.
Alright, so THAT is going to build words for me, that indicates whether it's the first or the last, but then I’m still not done there, because what I'm going to have to do, is concatenate the date - today's date.
So I'll press F2, go right, and I should have pressed CTRL+Enter there, but I didn't.
Alright.
So we need that helper column, once we have that helper column, then it becomes something like this: =VLOOKUP of the word "First" concatenated with the date, I'll press F4 three times, to lock that down, into this, range here, three cells, F4, ,3,FALSE) And that will show me the start time each day.
To get the end time, instead of concatenating the word "First", I'm going to concatenate the word "Last".
And there you have it!
It's horrible to me, that I had to create this huge set of helper cells out here, I want to see if you have anything better.
Mike: Thanks MrExcel, and it's great to be doing duels again!
Now, I like your helper column here because check this out: That formula creates an exact look up item, first, with this serial number date, and last, and then over here, boom, you just create a VLOOKUP formula.
Now I'll take a slightly different take on this.
I'm going to notice that, in this column here, if I CTRL+Shift+` (grave accent), those are serial numbers, right?
So because they're numbers, CTRL+Z, I'm going to build a helper column, that checks whether there's a number greater than 0 over here, and multiplies it by the serial number date.
So I'm going to say, give me the serial number date, TIMES( , and this will be a boolean logical test, Are you greater than 0 ) ! Now, there's only two values that this little inner part evaluates to: TRUE or FALSE.
When it's a false, like here, FALSE* the number will give us 0, when we copy it down here and we'll get a TRUE over here, it'll be TRUE times this number, it's like 1 * this number, and it will give us the serial number, double click and send it down.
So in essence I've created a column, where I filtered out the serial numbers i don't want with 0!
Now, I don't have a unique identifier like MrExcel, but I'm going to take this little block here, use the actual serial number from this state, and do Look-up Last, and Look-up First.
Now, I'm looking up the start time, but in essence the start time is last in this list, and then the end time will be the first one.
So I'm going to come over here to start time, and I'm going to use the LOOKUP function, because the trick for LOOKUP last is, we're doing approximate match look up, and LOOKUP automatically does approximate match.
I'm going to look up a big number, and the big number is 2, because when I get to this lookup vector, I'm going to have errors and ones, so 2 will be bigger than any number one, and the way approximate match lookup works is, if you give it a big number, it will always find the last number.
The array is going to be 1/( , I'm going to highlight this whole column, CTRL+Shift+Down Arrow, F4, and I'm going to say: Are any of you equal, to this date (right here) relative cell reference?
) ! Now what this little bit right here does, F9, is that Divide by Zeroes will be the filter, and will only get 1's, where we see that serial number.
The 2 will be looked up, and because we're doing approximate, we'll always get the position of the last one.
That's how lookup_vector does, it's kind of like the MATCH function.
CTRL+Z, comma, and for result_vector, we simply give it the times, CTRL+Shift+Down Arrow, F4, and that's LOOKUP last, ) , CTRL+Enter, and double click and send it down.
I go to the last cell and hit F2, boom, it looks like it's working.
Now to get the first, we notice that if we're doing exact match, in either VLOOKUP or MATCH function, I'm going to use VLOOKUP, if we have the last argument as FALSE, any time it sees duplicates, it'll automatically get the first one.
So the LOOKUP value, boom, comma, and the table, it's this entire table, first column is the helper column, last column has the time.
CTRL+Shift+Down arrow, F4, ,3 to get the item from the third column, comma, and 0 for exact match!
That forces, when there are duplicates, to only get the first one.
CTRL+Enter, double click, and send it down.
And then we can check this, it looks like 17:48 is the start time, and 17:51 is the end time.
Now if we didn't want to have this helper column, I want to notice something: if I look at this, it's the cell from the date column, and then there's a cell from the value column, Enter, F2, Enter, F2 - anytime we have a helper column, that's looking at two columns, we can kind of simulate this, but instead of the C5 I put the whole column here, instead of the E5 I put the whole column here, right into our formula.
Now, I'm going to copy this first formula here, CTRL+V and F2, and the beautiful thing about this so rate calculation is, LOOKUP can handle array operations without any special keystroke.
So it's really that little bit right there, which is our helper column, that we're going to need to simulate, so I'm going to highlight the date column, CTRL+Shift+Down, F4, and multiply it in (, times the value column, CTRL+Shift+Down, F4, Are any of you greater than 0?
) . Now this array operation here will work, we have multiplying first, we're forcing the > operator before the multiplication, then it will multiply, and then it will get that comparative operator sign, = , CTRL+Enter, double click, and send it down.
That gives us LOOKUP Last, or the start time.
Now I'm going to cheat here, I'm going to copy this whole little bit right here, because I'm going to use this a second time, that in essence is simulating our helper column, CTRL+C, Esc, and now I'm going to use =INDEX( and look-up, the values, the times, CTRL+Shift+Down Arrow, F4, comma, and I'm going to use the MATCH function, to look-up serial number, date, comma, within this array, I'm going to CTRL+V, actually I don't need all of this, all I need, is the little part that simulates the helper column, ,0 to get only the first item when we have duplicates, )) and because that argument right there can't handle that array operation, without the special keystroke that we have to use, CTRL+Shift+Enter.
I look up to the formula bar, I see my curly brackets, double click, and send it down.
Now if you want it to get tricky, And not use CTRL+Shift+Enter, guess what, that whole array operation, we can put it inside of INDEX - like, LOOKUP INDEX is one of five functions, that can handle array operations without CTRL+Shift+Enter.
But I need the whole column, in essence, to be delivered to the MATCH, so watch this, because a column is filled with rows, inside of INDEX: comma the rows, to get all the rows delivered, I leave that argument empty.
) , and that will work with just Ctrl+Enter.
No CTRL+Shift+Enter, double click and send it down.
Alright, we'll send it back to MrExcel!
Bill: Like, 10,000 points to you!
That was the most coherent explanation, on how to take a helper column, and embed it right in the formula.
Two different ways, absolutely awesome.
Yes, it's great to be doing Dueling Excel podcasts again, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
