Unique Identifier based on concatenated cell

[DBoutwell]

Hi All,

I searched the archives and could not find an exact match for the problem I am facing. Please point me in the right direction if this was already answered.

I am trying to create a unique identifier for services based on customer code, city, location type (APT or TO), and a number.

2-Letter code + Airport code + Location type will be concatenated into a single cell and then appended with a number. The number needs to increment to tell me how many services a customer has at that city/location.

E.g.
AB + MAD + APT + nnn = ABMADAPT001
AB + MAD + APT + nnn = ABMADAPT002
AB + MAD + TO + nnn = ABMADTO001
XY + MAD + APT + nnn = XYMADAPT001
XY + LHR + TO + nnn = XYLHRTO001
XY + LHR + TO + nnn = XYLHRTO0002

2 letter code	Apt Code	Location type	Concat	Identifier
AB	MAD	APT	ABMADAPT	ABMADAPT001
AB	MAD	APT	ABMADAPT	ABMADAPT002
AB	MAD	TO	ABMADTO	ABMADTO001
XY	MAD	APT	XYMADAPT	XYMADAPT001
XY	LHR	TO	XYLHRTO	XYLHRTO001
XY	LHR	TO	XYLHRTO	XYLHRTO002

The sequential number needs to be based on the number of services the customer has at that location. If customer "AB" has two services at the MAD airport, the IDs should be ABMADAPT001 and ABMADAPT002. If customer "XY" wants to start their first service at MAD airport, their ID would be XYMADAPT001.

ROW does not work since it will increment by 1 and will not take new customers into account.

Maybe Excel is not the tool for this.

Thanks in advance.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=CONCAT(A2:C2)&TEXT(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2),"000")

 

[alansidman]

Can be achieved with Power Query/Get and Transform Data found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Inserted Merged Column" = Table.AddColumn(Source, "ID", each Text.Combine({[2 letter code], [Apt Code], [Location type]}, ""), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"ID"}, {{"Data", each _, type table [2 letter code=text, Apt Code=text, Location type=text, ID=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Custom.Index"}),
    #"Inserted Merged Column1" = Table.AddColumn(#"Expanded Custom", "Unique ID", each Text.Combine({[ID], Text.From([Custom.Index], "en-US")}, ""), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column1",{"ID", "Data", "Custom.Index"})
in
    #"Removed Columns"

This will auto update every time you update the source table by selecting Refresh All

 

[DBoutwell]

Thanks for the fast answers. The answer from Fluff worked well. However, I will take a look at the Power Query as well.

Thanks again !!!

 

[Fluff]

Glad we could help & thanks for the feedback.