MrExcel's Learn Excel #738 - Unique SN per CN

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 6, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top