Скачать или смотреть Excel - Excel Tutorial: Automatically Append Sequence Numbers to Duplicate Values - Episode 537

Microsoft Excel Tutorial: Automatically Append Sequence Numbers to Duplicate Values.

Welcome back to the MrExcel netcast where we answer your Excel questions and provide helpful tips and tricks. I'm Bill Jelen and today we have a question from Ben about appending sequences in Excel. Ben is working with a spreadsheet that contains multiple bands and each band may have several types of the same instrument. He wants to add a number at the end of the instrument name to indicate a sequence, such as Trumpet-1, Trumpet-2, Trumpet-3, and so on. In this video, I'll show you how to easily achieve this using a couple of new columns and a clever formula.

The first step is to create a column that counts the occurrence of each instrument. We will use the COUNTIF function and create a reference that is part absolute and part relative. This means that the first cell in the range will always be locked at A1, but the second cell will change as we copy the formula down. This will allow us to count how many times the value in A2 appears in the range. As we copy the formula down, it will automatically extend the series for duplicate instruments.

Next, we can use the CONCATENATE function to combine the instrument name with the count number. If you think you will only have single-digit numbers, you can use the formula =A2&" "&B2 to add a space between the instrument name and the count number. However, if you think you may have double-digit numbers, it's best to use the TEXT function to ensure that the numbers are formatted correctly. For example, you can use =A2&" "&TEXT(B2,"00") to add a leading zero for single-digit numbers. This will give you a final result of Trumpet 01, Trumpet 02, Trumpet 03, and so on.

Once you have the desired sequence, you can use the Paste Special Values feature to replace the formulas with the actual values. This will ensure that the sequence remains the same even if you make changes to the original data. Thank you to Ben for sending in this question and if you have any Excel questions, please feel free to drop us a note. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. We'll see you next time for another netcast from MrExcel.

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-...


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!

Table of Contents:
(00:00) Introduction
(00:20) Spreadsheet with multiple bands
(00:32) Appending numbers to instrument names
(00:42) Solution using COUNTIF function
(01:01) Building a reference with absolute and relative cells
(01:20) Automatically extending the series
(01:34) Concatenating values for double digits
(02:03) Using Ctrl+C and Paste Special Values
(02:15) 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:
Automatically extending series in Excel
Concatenating cells in Excel using ampersand operator
Copying formulas with fill handle in Excel
Creating a reference with absolute and relative cell values
Handling double-digit sequence numbers in Excel
Next netcast from MrExcel
Pasting special values in Excel to solve a problem
Sending questions for the MrExcel netcast
Spreadsheet solution for adding sequence numbers to instruments
Using COUNTIF function in Excel for instrument sequencing
Using the TEXT function in Excel for formatting
YouTube video on appending numbers to instrument names

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