Yes - a podcast of use to 10,000 British Cattle Farmers but of no use to anyone else. Actually...it is a fun challenge. Play this one to see the problem then see if you can come up with a formula to extend the fine numbering system used for British Cattle! Episode 1029 will show you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jump.
Basically, we start out with massive amount of data.
So, how we are going to analyze as well file up a pivot table.
Lets see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Sarah from England says that if I could solve this problem, it will help out tens of thousands of British cattle farmers.
It applies to no one else other than British cattle farmers.
These are the ID numbers issued to British cattle.
The first bit here this eight is static.
That's assigned to the farm, but then this number is incredibly annoying.
The 804, 805, 806 that continues increasing indefinitely.
But the first digit scales from one to seven and then starts over again at one.
And so Sarah says, "This is driving me crazy because it's six increases of one hundred thousand and one followed by one decrease of four hundred nine thousand, nine hundred and ninety-nine.
And how are you going to write a formula for that?" So, I wrote two formulas.
Now the first one here.
Let's do this.
We're going to do the static part, as a static part.
So, UK123456 services, they end up entering this.
All 100 manually just because they can't figure out a good way to solve it.
So, there's the static part ampersand.
Now here's where some of the magic is going to happen.
I'm going to take the mod of the row of A1,7.
Now, what does that do?
That divides the row number by seven.
And gives me the remainder that's going to be a number from 0 to 6.
I need to add 1.
Do that +1 ampersand and then we're going to use the TEXT Function.
The TEXT Function, the next one that I want is 817.
So, I went the row of A817 comma five zero's 1, 2, 3, 4, 5.
I would have put the leading zeros in there.
So I get Uk.
All right!
Type the initial text wrong.
Good and then instead of a 2, we need to get 6.
So, I need to adjust that row number.
So, we need to get a 7.
Parameter to A five.
Close A6.
All right!
There we go.
We have this let's copy it down.
That's the real test.
Will it work when we copy down?
So, the seven goes to one two three four.
looks good!
And in fact, it is incrementing the number.
So, there is one formula that will solve that problem, Big Concatenation Formula.
But you enter it once Here you're going to put this on a sticky note on the wall.
Copy it.
Just kind of do a little test here with the row figure out.
Which row to use as your starter row.
So, that way it gives the first number.
Once you get that you're golden you can roll.
And everything will work.
Now another way to go here is, I actually put the static part in column A and then built a formula here that looks at the mod of the row, divided by seven.
See if it is equal to zero.
If it is, it subtracts the 5999.
Actually that's to be 4999 and otherwise, it adds one thousand one to the previous value, and so this is working out.
Just fine!
Then of course we need a Concatenation Formula here.
Ampersand close face quote ampersand and copy that down all the way.
Now of course once you get this, you're going to want to convert those two values.
So, remember the good way to do this.
There's many ways that convert formulas values.
But go to the right edge.
Right click, drag right, drag left.
Let go of the right mouse button.
Copy here as values only and you have your answer.
So there you go Sarah.
It's up to you to let the 10,000 other cattle farmers in the UK, know about MrExcel.com and this amazing Formula.
I would use this one back here to be honest.
In fact, you create a little macro.
Maybe that would do it.
Would ask for the starting number and write the Formula.
That would have to be a podcast for another day, okay!I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Thanks, you stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jump.
Basically, we start out with massive amount of data.
So, how we are going to analyze as well file up a pivot table.
Lets see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Sarah from England says that if I could solve this problem, it will help out tens of thousands of British cattle farmers.
It applies to no one else other than British cattle farmers.
These are the ID numbers issued to British cattle.
The first bit here this eight is static.
That's assigned to the farm, but then this number is incredibly annoying.
The 804, 805, 806 that continues increasing indefinitely.
But the first digit scales from one to seven and then starts over again at one.
And so Sarah says, "This is driving me crazy because it's six increases of one hundred thousand and one followed by one decrease of four hundred nine thousand, nine hundred and ninety-nine.
And how are you going to write a formula for that?" So, I wrote two formulas.
Now the first one here.
Let's do this.
We're going to do the static part, as a static part.
So, UK123456 services, they end up entering this.
All 100 manually just because they can't figure out a good way to solve it.
So, there's the static part ampersand.
Now here's where some of the magic is going to happen.
I'm going to take the mod of the row of A1,7.
Now, what does that do?
That divides the row number by seven.
And gives me the remainder that's going to be a number from 0 to 6.
I need to add 1.
Do that +1 ampersand and then we're going to use the TEXT Function.
The TEXT Function, the next one that I want is 817.
So, I went the row of A817 comma five zero's 1, 2, 3, 4, 5.
I would have put the leading zeros in there.
So I get Uk.
All right!
Type the initial text wrong.
Good and then instead of a 2, we need to get 6.
So, I need to adjust that row number.
So, we need to get a 7.
Parameter to A five.
Close A6.
All right!
There we go.
We have this let's copy it down.
That's the real test.
Will it work when we copy down?
So, the seven goes to one two three four.
looks good!
And in fact, it is incrementing the number.
So, there is one formula that will solve that problem, Big Concatenation Formula.
But you enter it once Here you're going to put this on a sticky note on the wall.
Copy it.
Just kind of do a little test here with the row figure out.
Which row to use as your starter row.
So, that way it gives the first number.
Once you get that you're golden you can roll.
And everything will work.
Now another way to go here is, I actually put the static part in column A and then built a formula here that looks at the mod of the row, divided by seven.
See if it is equal to zero.
If it is, it subtracts the 5999.
Actually that's to be 4999 and otherwise, it adds one thousand one to the previous value, and so this is working out.
Just fine!
Then of course we need a Concatenation Formula here.
Ampersand close face quote ampersand and copy that down all the way.
Now of course once you get this, you're going to want to convert those two values.
So, remember the good way to do this.
There's many ways that convert formulas values.
But go to the right edge.
Right click, drag right, drag left.
Let go of the right mouse button.
Copy here as values only and you have your answer.
So there you go Sarah.
It's up to you to let the 10,000 other cattle farmers in the UK, know about MrExcel.com and this amazing Formula.
I would use this one back here to be honest.
In fact, you create a little macro.
Maybe that would do it.
Would ask for the starting number and write the Formula.
That would have to be a podcast for another day, okay!I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Thanks, you stopping by.
We'll see you next time for another netcast from MrExcel.
