Look Who it Was Random video

Excel List of Unique Serial Numbers per Customer - Episode 738

1,275 views · Published 6 February 2009 · 3:08 · Indexed 22 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: List of Unique Serial Numbers per Customer

Welcome back to the MrExcel netcast where we share tips and tricks to help you become an Excel pro. In today's video, we have a tip sent in by Matthew that involves a fairly complicated situation. But don't worry, we'll walk through it step by step and show you some cool tricks along the way.

Matthew's data set on the left-hand side has two columns - CN's and SN's. His goal is to have a list of all the SN's that appear in the database for each CN, with the CN's in their own columns. To achieve this, he starts by adding a new field with the number 1 to the database. Then, he creates a pivot table with the CN's as the columns, SN's as the rows, and the number 1 in the data area. This shows where each SN appears for each CN.

Next, Matthew adds a calculated field to the pivot table by multiplying the SN field by the number 1. This clever step replaces the 1's with the actual values, making it easier to work with the data. Then, he copies and pastes the pivot table data to a new spot in the spreadsheet and uses the "Find and Replace" function to change all the zeros to blanks. Finally, he selects all the blank cells and deletes them, resulting in a sorted list of SN's for each CN.

This tip is a great example of using multiple Excel features together to solve a complex problem. My personal favorite is the use of the calculated field to replace the 1's with the actual values. However, it's important to note that this method only works if each SN appears in the database only once. Otherwise, we would end up with duplicate values and chaos would ensue.

I want to thank Matthew for sharing this clever tip with us and I hope it helps you in your Excel journey. Don't forget to subscribe to our channel for more helpful netcasts from MrExcel. Thanks for watching and we'll see you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction
(00:14) Tip from Matthew
(00:26) Starting Data Set
(00:40) Steps to Achieve Goal
(01:05) Calculated Field
(01:26) Clever Step
(01:36) Final Steps
(02:15) Conclusion
(02:25) Favorite Trick
(02:37) Important Note
(02:47) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Avoiding duplication of values in Excel databases
Efficient methods for sorting and filtering data in Excel
Excel tips and tricks for data analysis
How to use calculated fields in Excel pivot tables
Maximizing productivity with Excel features and functions
Multiplying fields in Excel to change values
Selecting and deleting blank cells in Excel using Edit and Delete
Sorting data in Excel using the Paste Special feature
Tips for organizing and analyzing data in Excel
Tricks for manipulating data in Excel pivot tables
Using Ctrl H to find and replace values in Excel
YouTube video tutorial on creating a pivot table in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152150/

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.

More from this channel