Assign a unique number ID to a group.
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:
- In the initial sheet (or its duplicate, if required) add new column named
NewNumberand apply the
GROUPBY(1)function over it. This action fills the new column with
1in every row.
- Create another sheet called
DataPrepand apply the
GROUPBYfunction to the NewNumber column.
- Create a new column called UniqueBrand on the DataPrep sheet and apply the
GROUPUNIQUESfunction to the Brand column from the initial sheet.
- On the DataPrep sheet, name the next column RowNumber and apply the
GROUPROWNUMBERfunction. 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.
- 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:
- 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.