How to Assign a Unique Number ID to a Group

Goal

Assign a unique number ID to a group.

Learn

Using the GROUPBY and GROUPUNIQUES functions:

To use these functions, you need to perform a set of actions over a dataset. For this example, you have two columns - ID and Brand, in your initial sheet:

  1. In the initial sheet (or its duplicate, if required) add new column named NewNumber and apply the GROUPBY(1) function over it. This action fills the new column with 1 in every row. 
  2. Create another sheet called DataPrep and apply the GROUPBY function to the NewNumber column.
  3. Create a new column called UniqueBrand on the DataPrep sheet and apply the GROUPUNIQUES function to the Brand column from the initial sheet.
  4. On the DataPrep sheet, name the next column RowNumber and apply the GROUPROWNUMBER function. Now you should have 2 sheets at your workbook: the inital sheet with the ID, Brand, and NewNumber columns and the DataPrep sheet with NewNumber, UniqueBrand, and RowNumber columns.
  5. Join these two sheets by the Brand and UniqueBrand columns and remove all other columns except ID, Brand, and RowNumber.

Using encoding functions:

To use this method, you need to perform a set of actions over the initial data set. For this example, you have two columns - ID and Brand, in your initial sheet:

  1. On the same sheet (or its duplicate, if required) add a new column called HashValue and apply the SHA_256(#Brand) formula to it. This function creates hash values for every record in the Brand column. For similar records, these hash values are identical.

While the second method is easier, but it might be not as convenient to operate with hash values.