Ben asks how he can automatically append a sequence number to duplicate values in his spreadsheet. Episode 537 shows how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question sent in by Ben.
If you have a question for the netcast, please feel free to drop us a note and we'll get to you on a future podcast.
Ben has a spreadsheet where he's working with a number of bands.
Each band might have several types of the same instrument.
So, he might have Trumpet on row 2 and row 8 and he says, I want to append a number to the end of the instrument.
That indicates basically a sequence.
So, you'd have Trumpet dash 1, Trumpet dash 2, Trumpet dash 3 and so on.
My solution to this, it's going to require a couple of new columns.
So, the first column is going to count, which occurrence of the instrument this is.
My first formula is going to use the COUNTIF function.
The COUNTIF function, and we're going to do something clever with the range here.
We're going to say, that we want to count from A1, always A1, down to column A of this row.
So, we're going to build a reference, that is part absolute and part relative.
So, it'll always be $A$. I want to lock it at the topmost cell, but we're going to let that extend down to A2 and A2 won't have any dollar signs at all.
I wanna count how many times that range contains the value that's in A2.
And of course in the first one, it'll be 1, but as I copy that formula down, double click the fill handle.
You'll see that as we get duplicates, here's the second flute, here's the third trumpet, it automatically extends the series.
Now, if you think, that you're only going to have 1 through 9, then it's a simple matter of using =A2& maybe, a space and then ampersand B2.
So, again that was A2 ampersand quote space quote ampersand B2, will concatenate that together.
If you think, that you're gonna go up to like 11 and you might have some double digits, then you might want to use the text of B2.
Text of B2 comma and then maybe 00 to make sure that you have two digits there and everything will sort out correctly.
So, there's Trumpet 01, Trumpet 02, Trumpet 03 and so on.
Now, ofcourse, until we get the solution, We can use Ctrl+C and Paste Special Values back on top of column A in order to solve the problem.
Thanks to Ben for sending in that question.
If you have a question, please feel free to drop us a note.
Otherwise, will see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question sent in by Ben.
If you have a question for the netcast, please feel free to drop us a note and we'll get to you on a future podcast.
Ben has a spreadsheet where he's working with a number of bands.
Each band might have several types of the same instrument.
So, he might have Trumpet on row 2 and row 8 and he says, I want to append a number to the end of the instrument.
That indicates basically a sequence.
So, you'd have Trumpet dash 1, Trumpet dash 2, Trumpet dash 3 and so on.
My solution to this, it's going to require a couple of new columns.
So, the first column is going to count, which occurrence of the instrument this is.
My first formula is going to use the COUNTIF function.
The COUNTIF function, and we're going to do something clever with the range here.
We're going to say, that we want to count from A1, always A1, down to column A of this row.
So, we're going to build a reference, that is part absolute and part relative.
So, it'll always be $A$. I want to lock it at the topmost cell, but we're going to let that extend down to A2 and A2 won't have any dollar signs at all.
I wanna count how many times that range contains the value that's in A2.
And of course in the first one, it'll be 1, but as I copy that formula down, double click the fill handle.
You'll see that as we get duplicates, here's the second flute, here's the third trumpet, it automatically extends the series.
Now, if you think, that you're only going to have 1 through 9, then it's a simple matter of using =A2& maybe, a space and then ampersand B2.
So, again that was A2 ampersand quote space quote ampersand B2, will concatenate that together.
If you think, that you're gonna go up to like 11 and you might have some double digits, then you might want to use the text of B2.
Text of B2 comma and then maybe 00 to make sure that you have two digits there and everything will sort out correctly.
So, there's Trumpet 01, Trumpet 02, Trumpet 03 and so on.
Now, ofcourse, until we get the solution, We can use Ctrl+C and Paste Special Values back on top of column A in order to solve the problem.
Thanks to Ben for sending in that question.
If you have a question, please feel free to drop us a note.
Otherwise, will see you next time for another netcast from MrExcel.
