Matthew sends in a cool technique today to find a unique list of serial numbers for every model from a database. Matthew's trick uses about five tricks that you probably rarely use. Episode 738 walks you through Matthew's technique. You will see pivot table calculated fields, paste values, replace, and deleting all zero cells.
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:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a tip sent in by Matthew, and when I say a tip this is actually a fairly complicated situation.
But he uses some cool tricks here, so let's walk through it.
He says he starts out with the data set on the left-hand side that has two columns CN's and SN's and basically for every CN, he needs a list of all the SN's that appear in the database, but he wants the CN's in their own columns.
So here's what he does.
First he adds a new field to the database with the number 1.
So I'll double click the fill handle to copy that down and now we create a pivot table, so "Data" "Pivot Table" will just click finish and we'll put the CN's across the top, the SN's down the side and basically the number 1 in the data area.
So you see that anywhere that we have a 1 that means that that SN appears for that CN.
Then he goes through and adds a calculated field so "Pivot Table" "Formulas" "Calculated Field" and this calculated field is the =SN*One Click Add. Click OK and what that does.
This is a pretty clever step because now instead of just having one's there, we actually have the number where it appears. We're almost home. He takes the data now.
We'll take those columns not including the grand total and paste them to a new spot in the spreadsheet. "Paste special" "values" You need to change all the zeros to blanks, so we use Ctrl H and say, "Find what" 0 replaced with nothing and will do Alt A to "Replace All".
And now to select all the blank cells that's "Edit" "Go to" or F5.
Press "Special" choose "Blanks" and then finally we'll do "Edit" "Delete" Shift cells up Click OK and very quickly. We now have the list of sorted SN's for each CN.
Pretty clever uses a lot of amazing tricks there.
My favorite though is multiplying the SN field by the number 1 in order to change those 1s to the actual values.
Ofcourse this would only work if every SN appears in your database only once.
We don't want to have the number 1 appearing twice.
Then we get two times the SN and Chaos would result.
Hey, I want to thank Matthew for sending in that cool tip. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today we have a tip sent in by Matthew, and when I say a tip this is actually a fairly complicated situation.
But he uses some cool tricks here, so let's walk through it.
He says he starts out with the data set on the left-hand side that has two columns CN's and SN's and basically for every CN, he needs a list of all the SN's that appear in the database, but he wants the CN's in their own columns.
So here's what he does.
First he adds a new field to the database with the number 1.
So I'll double click the fill handle to copy that down and now we create a pivot table, so "Data" "Pivot Table" will just click finish and we'll put the CN's across the top, the SN's down the side and basically the number 1 in the data area.
So you see that anywhere that we have a 1 that means that that SN appears for that CN.
Then he goes through and adds a calculated field so "Pivot Table" "Formulas" "Calculated Field" and this calculated field is the =SN*One Click Add. Click OK and what that does.
This is a pretty clever step because now instead of just having one's there, we actually have the number where it appears. We're almost home. He takes the data now.
We'll take those columns not including the grand total and paste them to a new spot in the spreadsheet. "Paste special" "values" You need to change all the zeros to blanks, so we use Ctrl H and say, "Find what" 0 replaced with nothing and will do Alt A to "Replace All".
And now to select all the blank cells that's "Edit" "Go to" or F5.
Press "Special" choose "Blanks" and then finally we'll do "Edit" "Delete" Shift cells up Click OK and very quickly. We now have the list of sorted SN's for each CN.
Pretty clever uses a lot of amazing tricks there.
My favorite though is multiplying the SN field by the number 1 in order to change those 1s to the actual values.
Ofcourse this would only work if every SN appears in your database only once.
We don't want to have the number 1 appearing twice.
Then we get two times the SN and Chaos would result.
Hey, I want to thank Matthew for sending in that cool tip. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
