Dataset question - listing multiple lines of data with the same ID by group

[kboedeker]

I have a dataset with >1000 lines of data. Column A is a 10-digit ID that I need to deidentify to a non-identifying code. An example is below.
What formula should I use to create column B and list all of the groups of numbers as one site?

Thanks in advance!

Column A (currently lists sample data as 10 digit ID number - this is not the actual data)	Column B - this is the new column that I want to create and list these as (01, 02, 03, 04... etc.)	Columns c-f (unique data like location, values, scores, etc.)
9746584102	01
9746584102	01
9746584102	01
9746584102	01
7625182637	02
7625182637	02
7625182637	02
7625182637	02
7625182637	02
1122237313	03
1122237313	03
1122237313	03

 

[alansidman]

Here is a power query solution that replicates your results without the preceding 0

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Data", each _, type table [ID=number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Index", "Data", {"ID"}, {"ID.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"ID.1"})
in
    #"Removed Columns"

Book14
	C	D
2	9746584102	1
3	9746584102	1
4	9746584102	1
5	9746584102	1
6	7625182637	2
7	7625182637	2
8	7625182637	2
9	7625182637	2
10	7625182637	2
11	1122237313	3
12	1122237313	3
13	1122237313	3
Sheet1