Order of occurrence of same value areas (formula

[cerobit]

Hello, I am trying to generate special identifier (string) based on document prefix and some additional connecting data. I have tried various ways, but with no succes

Please see example below with a form of desired output. Any additional helping columns can be used, but a single formula/column would be nice.

Thank you for your help

order_of_occurrence_of_areas.xlsx
	A	B	C	D	E	F
1	Document prefix	Data connector		Desired output document prefix & order of occurrence of the same color/prefix (connected areas through data connector)		Progress so far…
2	A	1		A1		A1
3	A	2		A2		A2
4	B	3		B1		B1
5	C	4		C1		C1
6	B	5		B2		B2
7	A	6		A3		A3
8	B	7		B3		B3
9	A	8		A4		A4
10	A	8		A4		A5
11	B	9		B4		B4
12	B	10		B4		B5
13	C	11		C2		C2
14	C	11		C2		C3
15	C	11		C2		C4
16	A	12		A5		A6
17	A	13		A6		A7
18	C	14		C3		C5
19	C	15		C4		C6
20	B	16		B5		B6
21	B	16		B5		B7
22	B	16		B5		B8
23	A	17		A7		A8
24	C	18		C5		C7
25	C	18		C5		C8
26	C	19		C6		C9
27	A	20		A8		A9
28	A	21		A9		A10
29	A	21		A9		A11
30	A	21		A9		A12
31	A	21		A9		A13
32	A	22		A10		A14
33	B	23		B6		B9
example

Cell Formulas
Range	Formula
F2:F33	F2	=A2 & IF(COUNTIF($A$2:$A$33, A2) > 1, COUNTIF($A$2:A2, A2), "")

 

[DanteAmor]

Hi @cerobit, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

It seems to me that you have a issue in your expected results in rows 11 and 12, since the values in columns B are different and in column D you did not consider an increment..

I did the adjustment, following the pattern of the other examples and I show you the result below:

Dante Amor
	A	B	C	D
1	Document prefix	Data connector		Desired output
2	A	1		A1
3	A	2		A2
4	B	3		B1
5	C	4		C1
6	B	5		B2
7	A	6		A3
8	B	7		B3
9	A	8		A4
10	A	8		A4
11	B	9		B4
12	B	9		B4
13	C	11		C2
14	C	11		C2
15	C	11		C2
16	A	12		A5
17	A	13		A6
18	C	14		C3
19	C	15		C4
20	B	16		B5
21	B	16		B5
22	B	16		B5
23	A	17		A7
24	C	18		C5
25	C	18		C5
26	C	19		C6
27	A	20		A8
28	A	21		A9
29	A	21		A9
30	A	21		A9
31	A	21		A9
32	A	22		A10
33	B	23		B6
Hoja6

Cell Formulas
Range	Formula
D2:D33	D2	=A2&SUMPRODUCT(($A$2:A2=A2)*(MATCH($A$2:A2&$B$2:B2, $A$2:A2&$B$2:B3,0)=ROW($B$2:B2)-1))

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[cerobit]

Hi @DanteAmor !

Thank you for your reply.

You are right, the rows 11 and 12 are my bad, I have had an error in my problem's decription.

Your solution is exactly what I was trying to assemble. Than you again, this is awesome

Regards,
cerobit