Today's Dueling Excel, with Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen [Episode #1364]looks at selecting specific entries in a row and then concatenating those entries in one cell.
Transcript of the video:
Hey, welcome back it's another live dueling Excel podcast from MrExcel.
I'm Bill Jelen.
Mike Girvin from Excel Is Fun.
We're here right Highline Community College and doing some more fun Excel videos is... I mean OBLUKMT'S at YouTube want's to know.
He wants a formula that's gonna grab the last five values, last five values from each row and concatenate them together into a single cell.
All right so, let's see here I'm going to do equal INDEX.
Equal INDEX of this whole row, when the INDEX of this whole row, INDEX allows me to say which row I want.
Well, that's easy that's row number 1 because there's only 1 row and then which column.
Well okay, for this one I am going to go with the COUNTA of how many entries were there now our assumption here is there can't be any blank cells and that whole thing and what that's going to do that's gonna give me the last 1, that's gonna give me the last 1 and from there I wanna to go back 4, I wanna go back 4 that makes my head hurt minus 4.
I wonder if there's some way, that i can use column to do that we're gonna be minus 3, minus 2, minus 1 and then 0.
I suppose what i could do is minus 5, minus column of A1.
Boy that's gonna work I don't know.
Was missing a parentheses there so, that grabbed 1, 2, 3, 4, 5, the fifth one back copy across and ten 1154.
Hey, it worked that's cool let's copy it down and we'll do another one here.
So, this one that I should be getting 9, 1, 4, 1,7.
9,1, 4, 1, 7 I wish the concatenate, the concatenate function would let you concatenate a whole range together that would be a really, really good thing.
I'm gonna be able to Microsoft this week, maybe I'll ask them for that.
As a, as a Christmas present for everyone out there, ampersand because the otherwise we have to do this whole thing which is really tedious.
I didn't want the last 500 values.
There we go, that's what I've got. Mike.
Mike: All right, concatenate them all into one column, there's an add-in where you can use concatenate or something like that I wish Microsoft would.
You're going to have to tell them to give that to us as a Christmas present.
The more funk add-in, it doesn't work in 2010 now, and i use their functions all the time.
All right, let's see...
I'm gonna use INDEX 2 of this array.
I'm gonna highlight just this and assume there's only that many cells and I'm going to lock it going to the side, but not when it goes down so, that way the dancing ants will move down a row and move it down and the row number.
Well, I thought we were going to try and get the last one to go this way, but you're saying they want this one.
Well, so that's five in so i'm going to try the columns function and I am, here's all the column no that won't work. I'm gonna have to use count and I'll assuming their numbers i'm going to say count.
Now, that's all the columns.
So, if I F4, gotta lock it going to the side also.
Someone's going that way and how do I automatically subtract.
So, right now this one.
Bill: You could always go backwards and then in a concatenation fix it.
Mike: Well, in that case how about I lock the last one like that but not this one.
Right and so, what this means is, this is a, this should be a contracting range because the last one is locked but not the first one.
So, as it goes this way it'll Bill: That's what...
Mike: Let's see if it works.
Bill: Okay but the index the first thing in the index needs to know a row number.
Mike: If it's a one way I'm thinking, if it's one way, it will assume that the column is put in the row position.
Let's see what it works, I think that that's what helps us.
Bill: On 110 it goes backwards.
Mike: Okay so, but I returned them in the incorrect order.
So, I'll have to fix that during concatenation.
So, yeah 4, 8, 10, 9, 1, all right and so, then I have to go get that 10 first equals this ampersand double quotes comma space double quote ampersand and I am a bad typer Jelen and I'm always jealous when I watch you with that keyboard, but so I copied it and I'm gonna control+V each time, control+V control+V.
Bill: The answer, that's beautiful alright.
Mike: We gonna enter double click and send it down.
All right, it looks like it's working.
Bill: That's sweet, that is cool. Okay, look at me I've been doing the INDEX from home the whole time I always enter the one when there's only one row.
Mike: We better go look at help it.
Bill: Now, I believe you clearly it worked, I mean clearly I work I have empirical evidence.
Mike: But that's only...
Bill: Who uses Excel out come on...
Mike: No, I googled it.
Bill: Okay all right video YouTube was there two guys do then argue about the best way to do things in Excel.
You know this is pretty lame, right down to it there's like non-Excel videos at YouTube.
You know that are apparently my son watches them all the time he never is watching us there's the videos that I've excel are you actually the truth is in a few years everything will be at YouTube, actually everything's at YouTube now.
I think isn't it right yeah right so, you were the first else Excel guy there.
So, you're leading the pack meeting.
Pack yeah that's right and what 1250 videos and I didn't don't know all INDEX works until the day.
All right, that's it, isn't it I guess so until the next duel that's right.
I wanna thank everyone for stopping by.
We'll see you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen.
Mike Girvin from Excel Is Fun.
We're here right Highline Community College and doing some more fun Excel videos is... I mean OBLUKMT'S at YouTube want's to know.
He wants a formula that's gonna grab the last five values, last five values from each row and concatenate them together into a single cell.
All right so, let's see here I'm going to do equal INDEX.
Equal INDEX of this whole row, when the INDEX of this whole row, INDEX allows me to say which row I want.
Well, that's easy that's row number 1 because there's only 1 row and then which column.
Well okay, for this one I am going to go with the COUNTA of how many entries were there now our assumption here is there can't be any blank cells and that whole thing and what that's going to do that's gonna give me the last 1, that's gonna give me the last 1 and from there I wanna to go back 4, I wanna go back 4 that makes my head hurt minus 4.
I wonder if there's some way, that i can use column to do that we're gonna be minus 3, minus 2, minus 1 and then 0.
I suppose what i could do is minus 5, minus column of A1.
Boy that's gonna work I don't know.
Was missing a parentheses there so, that grabbed 1, 2, 3, 4, 5, the fifth one back copy across and ten 1154.
Hey, it worked that's cool let's copy it down and we'll do another one here.
So, this one that I should be getting 9, 1, 4, 1,7.
9,1, 4, 1, 7 I wish the concatenate, the concatenate function would let you concatenate a whole range together that would be a really, really good thing.
I'm gonna be able to Microsoft this week, maybe I'll ask them for that.
As a, as a Christmas present for everyone out there, ampersand because the otherwise we have to do this whole thing which is really tedious.
I didn't want the last 500 values.
There we go, that's what I've got. Mike.
Mike: All right, concatenate them all into one column, there's an add-in where you can use concatenate or something like that I wish Microsoft would.
You're going to have to tell them to give that to us as a Christmas present.
The more funk add-in, it doesn't work in 2010 now, and i use their functions all the time.
All right, let's see...
I'm gonna use INDEX 2 of this array.
I'm gonna highlight just this and assume there's only that many cells and I'm going to lock it going to the side, but not when it goes down so, that way the dancing ants will move down a row and move it down and the row number.
Well, I thought we were going to try and get the last one to go this way, but you're saying they want this one.
Well, so that's five in so i'm going to try the columns function and I am, here's all the column no that won't work. I'm gonna have to use count and I'll assuming their numbers i'm going to say count.
Now, that's all the columns.
So, if I F4, gotta lock it going to the side also.
Someone's going that way and how do I automatically subtract.
So, right now this one.
Bill: You could always go backwards and then in a concatenation fix it.
Mike: Well, in that case how about I lock the last one like that but not this one.
Right and so, what this means is, this is a, this should be a contracting range because the last one is locked but not the first one.
So, as it goes this way it'll Bill: That's what...
Mike: Let's see if it works.
Bill: Okay but the index the first thing in the index needs to know a row number.
Mike: If it's a one way I'm thinking, if it's one way, it will assume that the column is put in the row position.
Let's see what it works, I think that that's what helps us.
Bill: On 110 it goes backwards.
Mike: Okay so, but I returned them in the incorrect order.
So, I'll have to fix that during concatenation.
So, yeah 4, 8, 10, 9, 1, all right and so, then I have to go get that 10 first equals this ampersand double quotes comma space double quote ampersand and I am a bad typer Jelen and I'm always jealous when I watch you with that keyboard, but so I copied it and I'm gonna control+V each time, control+V control+V.
Bill: The answer, that's beautiful alright.
Mike: We gonna enter double click and send it down.
All right, it looks like it's working.
Bill: That's sweet, that is cool. Okay, look at me I've been doing the INDEX from home the whole time I always enter the one when there's only one row.
Mike: We better go look at help it.
Bill: Now, I believe you clearly it worked, I mean clearly I work I have empirical evidence.
Mike: But that's only...
Bill: Who uses Excel out come on...
Mike: No, I googled it.
Bill: Okay all right video YouTube was there two guys do then argue about the best way to do things in Excel.
You know this is pretty lame, right down to it there's like non-Excel videos at YouTube.
You know that are apparently my son watches them all the time he never is watching us there's the videos that I've excel are you actually the truth is in a few years everything will be at YouTube, actually everything's at YouTube now.
I think isn't it right yeah right so, you were the first else Excel guy there.
So, you're leading the pack meeting.
Pack yeah that's right and what 1250 videos and I didn't don't know all INDEX works until the day.
All right, that's it, isn't it I guess so until the next duel that's right.
I wanna thank everyone for stopping by.
We'll see you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
