Contents
In most cases, repetitions in our data are undesirable and we try to get rid of them in different ways. But sometimes it happens that duplicates are needed and useful, and moreover – we need to create them!
Let’s say we have a table like this with the names of people who booked a movie ticket and the number of tickets for each:
Each ticket must be assigned a unique 6-digit number, which is generated here by a simple function BETWEEN THE CASE (EDGE BETWEEN), which generates a random integer in the given range 100000-999999. Yes, I know that theoretically there can be coincidences, but the probability is very small and so far, for example, we are satisfied (for those who are not satisfied – see this article). The problem is different: for those who ordered more than one ticket, you need to insert new duplicate rows into the table (according to the number of tickets ordered), i.e. get this as an output:
To do this with your hands is dreary, with formulas it is difficult. So that leaves the two most convenient options – macros and Power Query.
Method 1: Create duplicate rows with a macro
Open the macro editor with the button Visual Basic tab developer (Developer) or keyboard shortcut Alt+F11. Insert a new module through the menu Insert – Module and copy the text of our macro there:
Sub Duplicate_Rows() Dim cell As Range Set cell = Range("B2") 'first cell in ticket column Do While Not IsEmpty(cell) If cell > 1 Then cell.Offset(1, 0).Resize( cell.Value - 1, 1).EntireRow.Insert 'insert N empty rows cell.Resize(cell.Value, 1).EntireRow.FillDown 'fill down from first cells End If Set cell = cell.Offset(cell.Value, 0) Loop End Sub The principle here is not the most complicated:
- we pass from top to bottom along the column starting from B2 to the first empty cell
- if the number in the cell is >1, then insert empty lines under the cell one less than the number of tickets
- fill empty cells (the FillDown method is an analogue of “pulling the black cross” in the lower right corner of the cell)
- move on to the next cell, and so on.
Method 2: Create Duplicate Rows in Power Query
Those who have at least a little experience with Power Query do not need to advertise its power 🙂 For those who are not familiar (in short), Power Query is a free add-in for Excel from Microsoft that can do almost everything that is possible with data imagine: loading from any sources, cleaning, transformation, data analysis, etc. For Excel 2010-2013, it can be downloaded from the Microsoft website (a separate tab will appear Power Query after installation), and in Excel 2016 it is already built in by default (group Get external data tab Data).
Power Query can easily and beautifully solve our duplicate generation problem.
First, let’s select our table and load it into Power Query with the button From Table/Range (From Table/Range) tab Data (Date) or Power Query:
After the confirmation window, we will see the query editor and our table. Add a Custom Column to a Tab Add column (Add Column — Custom Column):
In the window that appears, enter the column name and a formula that creates a list of numbers from 1 to the number of tickets in each row:
After clicking on OK a new column will appear with lists, the elements of which can be expanded into rows using the button in the table header:
As a result, we get almost what we wanted:
It remains to delete the List column that is no longer needed (right-click on the header – Delete column) and upload the data back to the sheet on the tab Home (Home) using the button Close and load – Close and load in… (Close&Load — Close&Load to…) and specify a suitable place for the resulting table:
And the very simple part remains – add a column with the formula to the table BETWEEN THE CASE (EDGE BETWEEN)to generate random ticket numbers:
It is especially nice that with any changes in the original (left) table (adding new people or changing the number of tickets), it will be enough just to update our resulting table with ticket numbers with the right mouse button.
PS
If you want random numbers not to be generated each time anew when recalculating the sheet, but to be formed once, then saving their values, then you will have to use the StaticRandBetween macro function from the PLEX add-on or something similar.
It is also possible, for clarity, to glue the ticket number and the serial number from the column through a hyphen List directly in Power Query using the command Merge columns tab Transformation (Transform).
- Merging two lists without duplicates
- Retrieving unique elements from a range
- What are macros, how to use them, where to insert macro code in Visual Basic