Append ID Number. Duplicates but Unique

[jbklee]

Can anyone assist please. Two orders displayed here, two IDs. I'd like to have a formula for Col E showing an appended Order ID per customer Code as example. So far, I have only worked out how to have an incremental number appended.

Order ID	Line Item	Customer Code		Append Order ID
2786	1.120	Yar		2786.1
2786	1.120	Yar		2786.1
2786	1.120	Yar		2786.1
2786	1.140	Noy		2786.2
2786	1.140	Noy		2786.2
2786	1.140	Noy		2786.2
2786	1.210	Abo		2786.3
2786	1.210	Abo		2786.3
2786	1.210	Abo		2786.3
2787	1.120	Eka		2787.1
2787	1.120	Eka		2787.1
2787	1.120	Eka		2787.1

 

[Eric W]

Like this?

Book1
	A	B	C	D	E
1	Order ID	Line Item	Customer Code		Append Order ID
2	2786	1.12	Yar		2786.1
3	2786	1.12	Yar		2786.1
4	2786	1.12	Yar		2786.1
5	2786	1.14	Noy		2786.2
6	2786	1.14	Noy		2786.2
7	2786	1.14	Noy		2786.2
8	2786	1.21	Abo		2786.3
9	2786	1.21	Abo		2786.3
10	2786	1.21	Abo		2786.3
11	2787	1.12	Eka		2787.1
12	2787	1.12	Eka		2787.1
13	2787	1.12	Eka		2787.1
Sheet2

Cell Formulas
Range	Formula
E2:E13	E2	=A2&"."&SUMPRODUCT(--(A$1:A1=A2),--(C$1:C1<>C$2:C2))+1

 

[felixstraube]

How about something like this:

The numbers are not exactly what you wrote in your OP, because the numbers are in alphabetical order. Abo is first, Noy second and Yar third.
Let me know if that works or do you need something else?

Also the formula in E2 requires excel version 2021 onwards. If you have an older version let me know and we'll try something else.

=LET(orderId, $A2,
customerCode, $C2,
orderIds, $A$2:$A$100,
customerCodes, $C$2:$C$100,
orderIdsCustomerCodes, SORT(UNIQUE(FILTER(customerCodes, orderIds=orderId))),
customerCodeIndex, MATCH(customerCode, orderIdsCustomerCodes,0),
orderId&"."&customerCodeIndex
)

I was thinking what would happen if you sort your data differently. Using the alphabetical order of the customer code the 1 will always be with Abo, the 2 with Noy, and so forth.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have a recent Excel version, this might suit you. I have provided two alternatives. Column F might be more useful if there can be more than 9 different customer codes per Order ID so that the appended bit would then go
.01
.02
.03
.
.
.09
.10
.11

If there could be 100 or more customer codes per Order ID then add an extra "0" in that last bit of the col F formula.

24 01 20.xlsm
	A	B	C	D	E	F
1	Order ID	Line Item	Customer Code		Append Order ID
2	2786	1.12	Yar		2786.1	2786.01
3	2786	1.12	Yar		2786.1	2786.01
4	2786	1.12	Yar		2786.1	2786.01
5	2786	1.14	Noy		2786.2	2786.02
6	2786	1.14	Noy		2786.2	2786.02
7	2786	1.14	Noy		2786.2	2786.02
8	2786	1.21	Abo		2786.3	2786.03
9	2786	1.21	Abo		2786.3	2786.03
10	2786	1.21	Abo		2786.3	2786.03
11	2787	1.12	Eka		2787.1	2787.01
12	2787	1.12	Eka		2787.1	2787.01
13	2787	1.12	Eka		2787.1	2787.01
jbklee

Cell Formulas
Range	Formula
E2:E13	E2	=A2&"."&ROWS(UNIQUE(FILTER(B$2:B2,A$2:A2=A2)))
F2:F13	F2	=A2&"."&TEXT(ROWS(UNIQUE(FILTER(C$2:C2,A$2:A2=A2))),"00")