Here is a quick step-by-step guide on how to easily get rid of duplicate rows in Excel and at the same time convert the number of duplicates into a new column. This procedure may be useful when using weighted draws in RandomPicker.

MS Excel has a Pivot Table feature that will help you convert the data in a minute.

Convert duplicates into weights

Step 1: Load the data

Open the file with the data in MS Excel. In our example, you can see a list of 18 entries with 6 names in column A; almost all the names are repeated in the list:

Data deduplication in Excel

Step 2: Create a Pivot Table

To create a Pivot Table, select the data you want to analyze. Click on any cell in the data range and go to the Insert tab. Click on the ‘Pivot Table’ button. In the Create PivotTable dialog box, select the ‘New Worksheet’ option and click OK.

Step 3: Set Up the Pivot Table

A blank pivot table appears in the new sheet. Click anywhere in the Pivot Table and you will see the PivotTable Fields options:

In our example, there is only one column named “Entry” – click its checkbox in the PivotTable Fields settings to select it and it will appear in the ‘Rows’ field. The pivot table will generate the deduplicated list of entries.

In the next step, drag the checked Entry item in the PivotTable Fields setting and drop it in the ‘Values’ field – and set it to ‘Count of Entry’. Thanks to this setting, the second column will appear with the count values. And this is exactly what we need because this is the number of duplicates for each entry, i.e. a weight that you want to import into RandomPicker.

Select the entire pivot table (excluding the Grand Total row) and copy & paste the values into a new excel file.

Step 3: Add the second column

RandomPicker requires a fixed import format: the first column is the entry, the second column is your internal note, and the third column is dedicated to the weight. All you need to do is to add the second column ‘Internal note’ to your new file (even if you don’t plan to use it). Right click the column B header and choose Insert: a new column will appear between Entry and Weight/count. Enter the column heading ‘InternalNote’ and save the file. Now, you can try to import your file into RandomPicker.

By following these steps, you can easily convert a file in MS Excel with multiple duplicate records into a table with weights. This can be useful for data preparation and an easy import.