Trying to write an insane formula to solve another poorly formatted spreadsheet.
Transcript of the video:
Hey! Welcome back, it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel, be joined by Mike Girvin from Excel Is Fun, episode 155.
Who has NOT signed off, per contact?
All right! Like we have a doozy, today.
Let's just concentrate over here on columns A through G, and I'll explain the problem, the big problem we have is, here's a single document.
They have six rows for this document.
The title only appears once, the data only appears once and that's a problem for another day.
But here's the issue the reason, why we have six rows is, this document has to be signed off by these six people.
These six people and whatever system is using this, allows these six people to choose any of the six signature spots and sign.
So, Jay Smith came along, and he signed first and then Adam West came along, and he didn't choose row two.
He just chose some random row, right.
He picked a blank spot and I've seen this, I've been on a school board, people just choose where to sign, other this is some computerized wacko version of choose where to sign and so, we need to figure out of these six people, who have already signed?
And actually, what we need to find out is who still needs to sign?
All right! So, there's the problem, here's how I solve this.
I started out with a temporary formula here that says where does this contract start?
So, look for this contract in all of column A and exact match and for all of the rows of doc 123.
It says it starts in row 2, but for all the rows of doc 2, 3, 4b.
It says it starts in row 8.
How many rows in this contract?
All right! So, that's a simple COUNTIF, count how many times in column A, we have this document.
It is sort of documenting numbers, that helps us.
All right! And then here, I'm just going to hover here, to show you the concept of what I wanted to do.
I want to say hey, let's go do a match of this name in Column D, through F2 to F7, to see if they've signed yet, all right!
But that's the tricky part because the F2 to F7, you would tend to want to do an offset there.
But offset is a volatile function.
So, check this out, I think this formula is the cool formula in the podcast, at least for me, so I'm doing the match of D2.
But instead of saying then I want to look from F2 to F7.
I put in INDEX here.
Now, index usually returns a value but it turns out if the index is followed by a colon or preceded by a colon, it actually returns the cell address.
All right! So, we're saying hey! Look for D2 within the I2.
So, that's the second value in column F and then go to I2 plus J2, which is 2 plus 6, 12 may minus 1.
So this in essence right here is referring to F2 to F7 comma 0.
All right! So, that test formula, sure enough is saying hey Jones B. has not signed yet.
Dylan M. is not signed yet, but West A. has signed.
All right! So, once I had that formula working then over here just a big ISNA, then I bring the value over, otherwise quote quote.
So, this is list of all the people who have not signed and then came down here to the bottom and said hey if this is the same contract is what's below, pull the previous list of who hasn't signed, otherwise blank blank.
With this current who hasn't signed and so you know, is this rolls up here, the very first line for each contract is going to be a list of all the people, who have not signed.
Although, we're going to have some extra spaces appearing for every person, who did sign.
And then finally, this here is a vlookup of this document number.
The 13th column to get the top value for those, who need to sign and wrap that in trim, to get rid of the extra spaces.
So, I was able to shorten that whole thing down to just a couple of columns, columns H and I, or temporary columns, hid those and we have the complete list of people who need to sign.
Very convoluted and it works, but Mike, boy! I'm hoping you have an easier way to go.
Mike: Thanks, MrExcel.
I do not know how to do this, can't get them all into a single cell.
I know there's more fun add-in but I can't get it to work on my computer and haven't since 2010.
All right! I don't know how to do that, but I am going to try and get the names every time we see a first document into cell.
So, if there's three names they'll be in three different cells and as MrExcel, pointed out.
We need a dynamic range.
First in this as a copy formula down this far we need to look there and there.
But when we get down to copying the formula down here, we need to look at a different range, dynamically move and what if we can create these two dynamic ranges.
We're going to ask the question, are any of these over in this list?
We're going to ask, really the opposite or any of these not over in this because that's what we're after and then we'll extract the names.
All right! MrExcel use the beautiful, index, colon, index.
I love that and use that a lot, but here let me show you OFFSET.
Offset is volatile, so if it's not a big data set, no problem.
The reference while I'm always going to start here [ F4 ] to lock at all directions comma and then I need to know, move down a number of rows and in fact I'm going to move this up here, so we can move down one, to get to Jones and then we're down here.
We'll move down, I think 8.
So, I'm going to use the match in the rows.
Match and it says hey, go look up this.
Now, I'm actually going to copy this formula and define names and that's going to need to be locked as we go this way but relative as we copy down.
So, I'm going to hit the [ F4 ] key 3 times, that will actually cause a problem with our defined names.
But we'll see how to deal with that comma.
Please look that up here, group within that whole range, MrExcel highlighted the whole column.
I'm just going to highlight the whole first column of the data set [ F4 ] and then comma 0 and that's important for duplicates because now, match when it looks up a duplicate, it's always going to get the first one.
When we copy it down here to this cell, it will get the position of that which is I think eight, right 78.
So, that's how many will move down from that starting position, right.
So, the second range it will count eight down and boom!
That will be the starting position.
So, ROWS is working off of that right there, comma columns.
We don't need to move on over any, so I'm gonna, from that starting position.
So, I'm going to comma and then the height, it's a simple COUNTIF.
I'm going to say the whole range [ F4 ] comma and then that first one right there, [ F4 ] 123 times, close parentheses.
Finally, the width we don't need it because it's one it will assume whatever the width is from that reference, close parenthesis.
Now, I'm going to [ control shift enter ] and copy this down, just to check it out, notice right here if I hit [ F9 ], boom! It's looking row, that whole range there [ escape ], anywhere in this blue range.
When a highlighted offset will deliver a dynamic range, 5 to Parker, 5 to Parker, and then same down here.
Now, we need to get our second range here and then here, I'm going to call that SI and we only need to do one thing different.
So, copy and edit mode, paste it.
We're going to move this and I'm going to move it using my move cursor.
So, that will be our starting position [ control shift enter ].
Well, I'm just entering them here.
We're going to put them up and define names what we can check this out.
Now, it's looking, [ F9 ] from West, 500 Smith Parker, zero there it is.
Now, I'm going to put this up and define names and here's the problem.
When you have anything, that's relative you actually have to click in the cell.
We're going to use it here and copy it over.
Now, so I'm going to click in that cell before I open up the dialog box to paste this name and now copy, [ escape ], click there, [ ctrl F3 ].
New, I'm going to call this RE, for requested.
Highlight, [ ctrl V ], click [ ok ] and I'm going to test it right here, right.
Test, it looks like, it's got it right on test click [ close ].
Now, I'm going to go get the second one, copy and edit mode, [ escape ] click there [ ctrl F3 ].
New top, call this one SI, let's paste this down here, [ ctrl V ], click [ ok ] and I'm going to test it looking good.
Now, I need a COUNT.
I need to know, how many are not from this, here are not over here.
So, I'm going to create a third name and call it CO.
Come down here, and we're going to use our define names, equals COUNT of RE.
Now RE, is this requested range, that's all of them, minus count of, we're counting Text here, so it is that of SI.
So, I'm going to do SI, right.
And so, that will calculate all the way down.
Now, I type to 1, I think I'm going to put an 'I'.
I'm very careful that, click [ ok ], click [ close ], just for kicks, I'm going to say equals CO, [ control enter ], double click and send it down.
We could test this if all of a sudden, I had Kent over here, as a sign person boom!
It's given me to all the way down [ ctrl Z ].
Now, I also want a column before I show this, over here.
I want to actually list the document, and then we're going to have just the names here and then on the next row where there's new document.
The document name in there, so I'm going to say equals if any time relative cell reference you are not equal to the one below then I want to see the document.
And I want to join it in double quotes, [ space ], equal sign and double quote, otherwise double-quote, double-quote, no texting, close parentheses, [ control enter ], double click and send it down.
I want to use that same logic of when these two are not equal because at first we're going to copy this flowing down.
It's a big array formula that's going to be running all over the place, but I'm going to turn it off.
So, the formula doesn't run down here using that same logical test.
Now, we can use the index look up.
We're looking up from the requested column, so I'm gonna for the array, RE and here's the problem comma row number, we have a bunch of matches position 1, 2 and 6 for this first range, that we need to extract.
So, row number as I copied across the columns, I need 1, 2 and then 6, relative position.
So, I'm either going to use small, to extract the row numbers from the array.
I'm going to create, or I'm going to use aggregate and then use the 15th function, 14 to 19 to array, the rest do not, so I'm going to say 15.
The reason I'm using aggregate is because it can use make an array calculation without [ control shift enter ] comma 6 because I'm going to have an error from division comma and now what do I need for the array right here and need a bunch of row numbers.
So, watch this row and I'm going to use RE, right now row RE that's dynamic.
It's from requested row, will give me 1 to 7 but when I copy the formula down then it will get row 8 to 14.
So, watch this further, normally when we create an array of relative positions.
We use row of the whole range minus row of the first one but this is dynamic right here, for the first one I need row 2 and down here I need row 8, so I'm going to say minus min of this row.
So, it'll always be looking through the dynamic range.
Row will spit out an array of numbers and men will get the smallest.
That will give me, actually right now if I hit this in highlight this, andd hit [ F9 ] gives me 0 to 5, right.
0 to 5 [ ctrl Z ], down here will give me 0 to 13, but I need to add one back in.
All right! So, that I have to put in parentheses for this array because that's going to be in the numerator.
So, then we divide and here's our famous little comparing two lists, to see what's not in a list and it's ISNA, that'll catch the NAs from the match.
That's finding relative positions and the trick here is that we need to look up these values here, all of them.
But that's the wrong range, sorry.
I highlight, that's RE.
We need to look up all of those as the look of value this is a function argument array operation, within SI, right.
So, SI comma 0 because they're not sorted and watch this because we're looking up the REs first these, over here.
We're saying what's the relative position of these over here when I hit [ F9 ].
NA.
NA.
Those are the two were after and then 451 because we did this ranges look up, it found Smith, which is 4 and then NA, NA, West which is 3.
4 5, so 5 which is 5 Smith which is 1 and there we go and then the NA will pick the NAs, which is what we're after, [ control Z ], close parenthesis.
If we highlight, just the NA, part and [ F9 ], it gives me true, true and then a true at the very end, boom!
Those are the markers in essence for the names we want.
When we do the division the whole array that gives me 12 and 6 those are the relative positions in this first range of the names, we want to extract meaning, they haven't signed.
[ Ctrl Z ] Now, comma we need a cave as we go this way, we need to extract from the small function first, second, third.
So, I'm gonna use columns and I'm sitting in K2, so dollar sign, K2, colon K2, close parenthesis.
I'm going to have to use that K ,again.
So, I'm going to copy it just for a moment.
Now, I can close off the aggregate, watch the screen tips closed off that.
Now, I'm seeing the row number, the aggregate will deliver the right row number, as we copy it to the side, close parenthesis on that.
I don't have to [ control shift enter ], just [ ctrl enter ], copy it over and down.
Check that out, and now it's working we got.
Look down here at its first come, document 234b, Kent, Jones, Dillon, those are exactly the ones that are have not signed.
Now, we need to add some criteria as we copy to the side, we need to turn this formula off when we get past the count of three and we need to turn all these off because these two items are the same.
So, I'm going to edit mode and do an IF and there's two conditions.
So, I'm using and in the logical test the first one is that.
[ F4, F4, F4 ], lock the column but not the row, are you not equal to the one below?
[ F4 ], a bunch, lock the column but not the row comma, to get to the second logical test and then I'm going to say columns [ control V ], that means, one two three four any time you are less than or equal to our count, which is CO, close parenthesis.
So, those two tests whoops!
I didn't type a comma, those two tests right there will be run and if it's true little run the index, otherwise comma value of false.
And that's important that we do the double quote here and IF, logical test and not IF here, because this big huge array with all these dynamic ranges is going to be running a lot.
Especially, what the fact that I use offset.
I don't have to [ control shift enter ], just [ cTRL enter ], copy it over and double click and send it down.
If I come down here and test it, so now Kent has signed over here and boom!
Just like that, throw it back to MrExcel.
MrExcel: Mike!
Mike, that was a softball question, that was a easy underhanded task.
I feel you're gonna say that all of this stuff, that I did here, all of these four columns could be completely replaced by a COUNTIFs, go see how many times this contract has this person, which will give me a list of all the people who have not signed, just joined together and we're done.
Although, the joke is probably on both of us, someone in the YouTube comments is going to come up with some formula, that's 12 characters long, that solves this whole thing.
But I want to thank everyone for stopping by, we'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, be joined by Mike Girvin from Excel Is Fun, episode 155.
Who has NOT signed off, per contact?
All right! Like we have a doozy, today.
Let's just concentrate over here on columns A through G, and I'll explain the problem, the big problem we have is, here's a single document.
They have six rows for this document.
The title only appears once, the data only appears once and that's a problem for another day.
But here's the issue the reason, why we have six rows is, this document has to be signed off by these six people.
These six people and whatever system is using this, allows these six people to choose any of the six signature spots and sign.
So, Jay Smith came along, and he signed first and then Adam West came along, and he didn't choose row two.
He just chose some random row, right.
He picked a blank spot and I've seen this, I've been on a school board, people just choose where to sign, other this is some computerized wacko version of choose where to sign and so, we need to figure out of these six people, who have already signed?
And actually, what we need to find out is who still needs to sign?
All right! So, there's the problem, here's how I solve this.
I started out with a temporary formula here that says where does this contract start?
So, look for this contract in all of column A and exact match and for all of the rows of doc 123.
It says it starts in row 2, but for all the rows of doc 2, 3, 4b.
It says it starts in row 8.
How many rows in this contract?
All right! So, that's a simple COUNTIF, count how many times in column A, we have this document.
It is sort of documenting numbers, that helps us.
All right! And then here, I'm just going to hover here, to show you the concept of what I wanted to do.
I want to say hey, let's go do a match of this name in Column D, through F2 to F7, to see if they've signed yet, all right!
But that's the tricky part because the F2 to F7, you would tend to want to do an offset there.
But offset is a volatile function.
So, check this out, I think this formula is the cool formula in the podcast, at least for me, so I'm doing the match of D2.
But instead of saying then I want to look from F2 to F7.
I put in INDEX here.
Now, index usually returns a value but it turns out if the index is followed by a colon or preceded by a colon, it actually returns the cell address.
All right! So, we're saying hey! Look for D2 within the I2.
So, that's the second value in column F and then go to I2 plus J2, which is 2 plus 6, 12 may minus 1.
So this in essence right here is referring to F2 to F7 comma 0.
All right! So, that test formula, sure enough is saying hey Jones B. has not signed yet.
Dylan M. is not signed yet, but West A. has signed.
All right! So, once I had that formula working then over here just a big ISNA, then I bring the value over, otherwise quote quote.
So, this is list of all the people who have not signed and then came down here to the bottom and said hey if this is the same contract is what's below, pull the previous list of who hasn't signed, otherwise blank blank.
With this current who hasn't signed and so you know, is this rolls up here, the very first line for each contract is going to be a list of all the people, who have not signed.
Although, we're going to have some extra spaces appearing for every person, who did sign.
And then finally, this here is a vlookup of this document number.
The 13th column to get the top value for those, who need to sign and wrap that in trim, to get rid of the extra spaces.
So, I was able to shorten that whole thing down to just a couple of columns, columns H and I, or temporary columns, hid those and we have the complete list of people who need to sign.
Very convoluted and it works, but Mike, boy! I'm hoping you have an easier way to go.
Mike: Thanks, MrExcel.
I do not know how to do this, can't get them all into a single cell.
I know there's more fun add-in but I can't get it to work on my computer and haven't since 2010.
All right! I don't know how to do that, but I am going to try and get the names every time we see a first document into cell.
So, if there's three names they'll be in three different cells and as MrExcel, pointed out.
We need a dynamic range.
First in this as a copy formula down this far we need to look there and there.
But when we get down to copying the formula down here, we need to look at a different range, dynamically move and what if we can create these two dynamic ranges.
We're going to ask the question, are any of these over in this list?
We're going to ask, really the opposite or any of these not over in this because that's what we're after and then we'll extract the names.
All right! MrExcel use the beautiful, index, colon, index.
I love that and use that a lot, but here let me show you OFFSET.
Offset is volatile, so if it's not a big data set, no problem.
The reference while I'm always going to start here [ F4 ] to lock at all directions comma and then I need to know, move down a number of rows and in fact I'm going to move this up here, so we can move down one, to get to Jones and then we're down here.
We'll move down, I think 8.
So, I'm going to use the match in the rows.
Match and it says hey, go look up this.
Now, I'm actually going to copy this formula and define names and that's going to need to be locked as we go this way but relative as we copy down.
So, I'm going to hit the [ F4 ] key 3 times, that will actually cause a problem with our defined names.
But we'll see how to deal with that comma.
Please look that up here, group within that whole range, MrExcel highlighted the whole column.
I'm just going to highlight the whole first column of the data set [ F4 ] and then comma 0 and that's important for duplicates because now, match when it looks up a duplicate, it's always going to get the first one.
When we copy it down here to this cell, it will get the position of that which is I think eight, right 78.
So, that's how many will move down from that starting position, right.
So, the second range it will count eight down and boom!
That will be the starting position.
So, ROWS is working off of that right there, comma columns.
We don't need to move on over any, so I'm gonna, from that starting position.
So, I'm going to comma and then the height, it's a simple COUNTIF.
I'm going to say the whole range [ F4 ] comma and then that first one right there, [ F4 ] 123 times, close parentheses.
Finally, the width we don't need it because it's one it will assume whatever the width is from that reference, close parenthesis.
Now, I'm going to [ control shift enter ] and copy this down, just to check it out, notice right here if I hit [ F9 ], boom! It's looking row, that whole range there [ escape ], anywhere in this blue range.
When a highlighted offset will deliver a dynamic range, 5 to Parker, 5 to Parker, and then same down here.
Now, we need to get our second range here and then here, I'm going to call that SI and we only need to do one thing different.
So, copy and edit mode, paste it.
We're going to move this and I'm going to move it using my move cursor.
So, that will be our starting position [ control shift enter ].
Well, I'm just entering them here.
We're going to put them up and define names what we can check this out.
Now, it's looking, [ F9 ] from West, 500 Smith Parker, zero there it is.
Now, I'm going to put this up and define names and here's the problem.
When you have anything, that's relative you actually have to click in the cell.
We're going to use it here and copy it over.
Now, so I'm going to click in that cell before I open up the dialog box to paste this name and now copy, [ escape ], click there, [ ctrl F3 ].
New, I'm going to call this RE, for requested.
Highlight, [ ctrl V ], click [ ok ] and I'm going to test it right here, right.
Test, it looks like, it's got it right on test click [ close ].
Now, I'm going to go get the second one, copy and edit mode, [ escape ] click there [ ctrl F3 ].
New top, call this one SI, let's paste this down here, [ ctrl V ], click [ ok ] and I'm going to test it looking good.
Now, I need a COUNT.
I need to know, how many are not from this, here are not over here.
So, I'm going to create a third name and call it CO.
Come down here, and we're going to use our define names, equals COUNT of RE.
Now RE, is this requested range, that's all of them, minus count of, we're counting Text here, so it is that of SI.
So, I'm going to do SI, right.
And so, that will calculate all the way down.
Now, I type to 1, I think I'm going to put an 'I'.
I'm very careful that, click [ ok ], click [ close ], just for kicks, I'm going to say equals CO, [ control enter ], double click and send it down.
We could test this if all of a sudden, I had Kent over here, as a sign person boom!
It's given me to all the way down [ ctrl Z ].
Now, I also want a column before I show this, over here.
I want to actually list the document, and then we're going to have just the names here and then on the next row where there's new document.
The document name in there, so I'm going to say equals if any time relative cell reference you are not equal to the one below then I want to see the document.
And I want to join it in double quotes, [ space ], equal sign and double quote, otherwise double-quote, double-quote, no texting, close parentheses, [ control enter ], double click and send it down.
I want to use that same logic of when these two are not equal because at first we're going to copy this flowing down.
It's a big array formula that's going to be running all over the place, but I'm going to turn it off.
So, the formula doesn't run down here using that same logical test.
Now, we can use the index look up.
We're looking up from the requested column, so I'm gonna for the array, RE and here's the problem comma row number, we have a bunch of matches position 1, 2 and 6 for this first range, that we need to extract.
So, row number as I copied across the columns, I need 1, 2 and then 6, relative position.
So, I'm either going to use small, to extract the row numbers from the array.
I'm going to create, or I'm going to use aggregate and then use the 15th function, 14 to 19 to array, the rest do not, so I'm going to say 15.
The reason I'm using aggregate is because it can use make an array calculation without [ control shift enter ] comma 6 because I'm going to have an error from division comma and now what do I need for the array right here and need a bunch of row numbers.
So, watch this row and I'm going to use RE, right now row RE that's dynamic.
It's from requested row, will give me 1 to 7 but when I copy the formula down then it will get row 8 to 14.
So, watch this further, normally when we create an array of relative positions.
We use row of the whole range minus row of the first one but this is dynamic right here, for the first one I need row 2 and down here I need row 8, so I'm going to say minus min of this row.
So, it'll always be looking through the dynamic range.
Row will spit out an array of numbers and men will get the smallest.
That will give me, actually right now if I hit this in highlight this, andd hit [ F9 ] gives me 0 to 5, right.
0 to 5 [ ctrl Z ], down here will give me 0 to 13, but I need to add one back in.
All right! So, that I have to put in parentheses for this array because that's going to be in the numerator.
So, then we divide and here's our famous little comparing two lists, to see what's not in a list and it's ISNA, that'll catch the NAs from the match.
That's finding relative positions and the trick here is that we need to look up these values here, all of them.
But that's the wrong range, sorry.
I highlight, that's RE.
We need to look up all of those as the look of value this is a function argument array operation, within SI, right.
So, SI comma 0 because they're not sorted and watch this because we're looking up the REs first these, over here.
We're saying what's the relative position of these over here when I hit [ F9 ].
NA.
NA.
Those are the two were after and then 451 because we did this ranges look up, it found Smith, which is 4 and then NA, NA, West which is 3.
4 5, so 5 which is 5 Smith which is 1 and there we go and then the NA will pick the NAs, which is what we're after, [ control Z ], close parenthesis.
If we highlight, just the NA, part and [ F9 ], it gives me true, true and then a true at the very end, boom!
Those are the markers in essence for the names we want.
When we do the division the whole array that gives me 12 and 6 those are the relative positions in this first range of the names, we want to extract meaning, they haven't signed.
[ Ctrl Z ] Now, comma we need a cave as we go this way, we need to extract from the small function first, second, third.
So, I'm gonna use columns and I'm sitting in K2, so dollar sign, K2, colon K2, close parenthesis.
I'm going to have to use that K ,again.
So, I'm going to copy it just for a moment.
Now, I can close off the aggregate, watch the screen tips closed off that.
Now, I'm seeing the row number, the aggregate will deliver the right row number, as we copy it to the side, close parenthesis on that.
I don't have to [ control shift enter ], just [ ctrl enter ], copy it over and down.
Check that out, and now it's working we got.
Look down here at its first come, document 234b, Kent, Jones, Dillon, those are exactly the ones that are have not signed.
Now, we need to add some criteria as we copy to the side, we need to turn this formula off when we get past the count of three and we need to turn all these off because these two items are the same.
So, I'm going to edit mode and do an IF and there's two conditions.
So, I'm using and in the logical test the first one is that.
[ F4, F4, F4 ], lock the column but not the row, are you not equal to the one below?
[ F4 ], a bunch, lock the column but not the row comma, to get to the second logical test and then I'm going to say columns [ control V ], that means, one two three four any time you are less than or equal to our count, which is CO, close parenthesis.
So, those two tests whoops!
I didn't type a comma, those two tests right there will be run and if it's true little run the index, otherwise comma value of false.
And that's important that we do the double quote here and IF, logical test and not IF here, because this big huge array with all these dynamic ranges is going to be running a lot.
Especially, what the fact that I use offset.
I don't have to [ control shift enter ], just [ cTRL enter ], copy it over and double click and send it down.
If I come down here and test it, so now Kent has signed over here and boom!
Just like that, throw it back to MrExcel.
MrExcel: Mike!
Mike, that was a softball question, that was a easy underhanded task.
I feel you're gonna say that all of this stuff, that I did here, all of these four columns could be completely replaced by a COUNTIFs, go see how many times this contract has this person, which will give me a list of all the people who have not signed, just joined together and we're done.
Although, the joke is probably on both of us, someone in the YouTube comments is going to come up with some formula, that's 12 characters long, that solves this whole thing.
But I want to thank everyone for stopping by, we'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
