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

 

[cerobit]

Hello again,

may I ask you for a modification? I would like to skip some numbering based on an additional condition (non-zero values of a column). See below. Thank you.

order_of_occurrence_of_areas.xlsx
	A	B	C	D	E
1	Document prefix	Data connector	Value		Desired output document prefix & order of occurrence of the same color/prefix (connected areas through data connector) When Value is zero, then only Prefix is displayed and the order is skipped. Order is not skipped, when there is/are zero/s, but even some other non-zero value/s within the same Prefix-Connector group (see cells C14, C15, C28).
2	A	1	100		A1
3	A	2	0		A
4	B	3	100		B1
5	C	4	0		C
6	B	5	100		B2
7	A	6	100		A2
8	B	7	0		B
9	A	8	0		A
10	A	8	0		A
11	B	9	0		B
12	B	10	0		B
13	C	11	50		C1
14	C	11	0		C1
15	C	11	0		C1
16	A	12	100		A3
17	A	13	0		A
18	C	14	100		C2
19	C	15	0		C
20	B	16	0		B
21	B	16	0		B
22	B	16	0		B
23	A	17	100		A4
24	C	18	0		C
25	C	18	0		C
26	C	19	100		C3
27	A	20	100		A5
28	A	21	0		A6
29	A	21	50		A6
30	A	21	100		A6
31	A	21	150		A6
32	A	22	100		A7
33	B	23	100		B3
example (no zeros)

 

[Peter_SSs]

For your original question, since you have Excel 365 I think you could use this simpler formula as it seems to produce the same results as given in post 2

25 01 03.xlsm
	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
cerobit

Cell Formulas
Range	Formula
D2:D33	D2	=A2&ROWS(UNIQUE(FILTER(A$2:B2,A$2:A2=A2)))

For your follow-up question, this seems to work for the sample data but is pretty complex and I have not been able to come up with a simpler version without running into errors, incorrect results or circular references.
Anyway, see if it does what you want.

25 01 03.xlsm
	A	B	C	D	E
1	Document prefix	Data connector	Value	Results	Desired
2	A	1	100	A1	A1
3	A	2	0	A	A
4	B	3	100	B1	B1
5	C	4	0	C	C
6	B	5	100	B2	B2
7	A	6	100	A2	A2
8	B	7	0	B	B
9	A	8	0	A	A
10	A	8	0	A	A
11	B	9	0	B	B
12	B	10	0	B	B
13	C	11	50	C1	C1
14	C	11	0	C1	C1
15	C	11	0	C1	C1
16	A	12	100	A3	A3
17	A	13	0	A	A
18	C	14	100	C2	C2
19	C	15	0	C	C
20	B	16	0	B	B
21	B	16	0	B	B
22	B	16	0	B	B
23	A	17	100	A4	A4
24	C	18	0	C	C
25	C	18	0	C	C
26	C	19	100	C3	C3
27	A	20	100	A5	A5
28	A	21	0	A6	A6
29	A	21	50	A6	A6
30	A	21	100	A6	A6
31	A	21	150	A6	A6
32	A	22	100	A7	A7
33	B	23	100	B3	B3
cerobit (2)

Cell Formulas
Range	Formula
D2:D33	D2	=IF(C2=0,IF(COUNTIFS(A$2:A$33,A2,B$2:B$33,B2,C$2:C$33,">0"),XLOOKUP(A2&"|"&B2,A$1:A1&"|"&B$1:B1,D$1:D1,XLOOKUP(A2&"|"&B2,A3:A$34&"|"&B3:B$34,D3:D$34)),A2),A2&ROWS(UNIQUE(FILTER(A$2:B2,(A$2:A2=A2)*(C$2:C2>0)))))

 

[cerobit]

Hello, this is awesome, it makes exactly what I was hoping for. Thank you!

1) I just needed to make a small correction while in a real application the columns Prefix and Connector are not adjacent, so I replaced the reference A$2:B2 with more general A$2:A2 & B$2:B2.

2) Is there any reason why the references are sometimes one row more longer (A3:A$34, B3:B$34, D3:D$34)?
And similarly why are the reference sometimes one row shorter (A$1:A1, B$1:B1, D$1:D1)?

The whole formula seems to be working fine even for the first and last row when I replace the number 34 with 33 and the number 1 with 2 (except the D$1:D1).

I am curious because in a real application I am turning this formula to structured references style with named range data tables.

→ 3) See my attempt, I looks pretty overcomplicated (in column E), maybe it could be done more clean way...?

Anyway, it works, so I am glad.

order_of_occurrence_of_areas.xlsx
	A	B	C	D	E
1	Prefix	Connector	Value	Results (ref)	Results (tab)
2	A	1	100	A1	A1
3	A	2	0	A	A
4	B	3	100	B1	B1
5	C	4	0	C	C
6	B	5	100	B2	B2
7	A	6	100	A2	A2
8	B	7	0	B	B
9	A	8	0	A	A
10	A	8	0	A	A
11	B	9	0	B	B
12	B	10	0	B	B
13	C	11	50	C1	C1
14	C	11	0	B	B
15	C	11	0	B	B
16	A	12	100	A3	A3
17	A	13	0	A	A
18	C	14	100	C2	C2
19	C	15	0	C	C
20	B	16	0	B	B
21	B	16	0	B	B
22	B	16	0	B	B
23	A	17	100	A4	A4
24	C	18	0	C	C
25	C	18	0	C	C
26	C	19	100	C3	C3
27	A	20	100	A5	A5
28	A	21	0	A5	A5
29	A	21	50	A6	A6
30	A	21	100	A6	A6
31	A	21	150	A6	A6
32	A	22	100	A7	A7
33	B	23	100	B3	B3
example (no zeros) (3)

Cell Formulas
Range	Formula
D2:D32	D2	=IF( C2=0,IF(COUNTIFS( A$2:A$33, A2, B$2:B$33, B2, C$2:C$33,">0"),XLOOKUP( A2&"|"& B2, A$2:A2&"|"& B$2:B2, D$1:D1,XLOOKUP( A2&"|"& B2, A3:A$33&"|"& B3:B$33, D3:D$33)), A2), A2&ROWS(UNIQUE(FILTER( A$2:A2& B$2:B2,( A$2:A2= A2)*( C$2:C2>0)))))
E2:E33	E2	=IF( [@Value]=0,IF(COUNTIFS( [Prefix], [@Prefix], [Connector], [@Connector], [Value],">0"),XLOOKUP( [@Prefix]&"|"& [@Connector], OFFSET(t_data[[#Headers],[Prefix]],1,0):[@Prefix]&"|"& OFFSET(t_data[[#Headers],[Connector]],1,0):[@Connector], t_data[[#Headers],[Results (tab)]]:OFFSET([@[Results (tab)]],-1,0),XLOOKUP( [@Prefix]&"|"& [@Connector], OFFSET([@Prefix],1,0):INDEX([Results (tab)],ROWS(t_data))&"|"& OFFSET([@Connector],1,0):INDEX([Connector],ROWS(t_data)), OFFSET([@[Results (tab)]],1,0):INDEX([Results (tab)],ROWS(t_data)))), [@Prefix]), [@Prefix]&ROWS(UNIQUE(FILTER( OFFSET(t_data[[#Headers],[Prefix]],1,0):[@Prefix]& OFFSET(t_data[[#Headers],[Connector]],1,0):[@Connector],( OFFSET(t_data[[#Headers],[Prefix]],1,0):[@Prefix]= [@Prefix])*( OFFSET(t_data[[#Headers],[Value]],1,0):[@Value]>0)))))
D33	D33	=IF( C33=0,IF(COUNTIFS( A$2:A$33, A33, B$2:B$33, B33, C$2:C$33,">0"),XLOOKUP( A33&"|"& B33, A$2:A33&"|"& B$2:B33, D$1:D32,XLOOKUP( A33&"|"& B33, A$33:A34&"|" &#REF!, E$33:E34)), A33), A33&ROWS(UNIQUE(FILTER( A$2:A33& B$2:B33,( A$2:A33= A33)*( C$2:C33>0)))))

 

[cerobit]

Sorry, my bad, I haven't noticed, that there is a faulty value on line 28 of given result when the boundaries are shifted (34→33 and 1→2) . So your solution is correct.

I've updated even the "structured style", if somebody else would need this piece of work.

order_of_occurrence_of_areas.xlsx
	A	B	C	D	E	F
1	Prefix	Connector	Value	Result (hard)	Results (ref)	Results (tab)
2	A	1	-100	A1	A1	A1
3	A	2	0	A	A	A
4	B	3	100	B1	B1	B1
5	C	4	0	C	C	C
6	B	5	-100	B2	B2	B2
7	A	6	100	A2	A2	A2
8	B	7	0	B	B	B
9	A	8	0	A	A	A
10	A	8	0	A	A	A
11	B	9	0	B	B	B
12	B	10	0	B	B	B
13	C	11	-100	C1	C1	C1
14	C	11	0	C1	C1	C1
15	C	11	0	C1	C1	C1
16	A	12	100	A3	A3	A3
17	A	13	0	A	A	A
18	C	14	100	C2	C2	C2
19	C	15	0	C	C	C
20	B	16	0	B	B	B
21	B	16	0	B	B	B
22	B	16	0	B	B	B
23	A	17	-100	A4	A4	A4
24	C	18	0	C	C	C
25	C	18	0	C	C	C
26	C	19	100	C3	C3	C3
27	A	20	100	A5	A5	A5
28	A	21	0	A6	A6	A6
29	A	21	50	A6	A6	A6
30	A	21	-100	A6	A6	A6
31	A	21	150	A6	A6	A6
32	A	22	100	A7	A7	A7
33	B	23	100	B3	B3	B3
example (no zeros) (corr)

Cell Formulas
Range	Formula
E2:E33	E2	=IF( C2=0,IF(COUNTIFS( A$2:A$33, A2, B$2:B$33, B2, C$2:C$33,"<>0"),XLOOKUP( A2&"|"& B2, A$1:A1&"|"& B$1:B1, E$1:E1,XLOOKUP( A2&"|"& B2, A3:A$34&"|"& B3:B$34, E3:E$34)), A2), A2&ROWS(UNIQUE(FILTER( A$2:A2& B$2:B2,( A$2:A2= A2)*( C$2:C2<>0)))))
F2:F33	F2	=IF( [@Value]=0,IF(COUNTIFS( [Prefix], [@Prefix], [Connector], [@Connector], [Value],"<>0"),XLOOKUP( [@Prefix]&"|"& [@Connector], t_data[[#Headers],[Prefix]]:OFFSET([@Prefix],-1,0)&"|"& t_data[[#Headers],[Connector]]:OFFSET([@Connector],-1,0), t_data[[#Headers],[Results (tab)]]:OFFSET([@[Results (tab)]],-1,0),XLOOKUP( [@Prefix]&"|"& [@Connector], OFFSET([@Prefix],1,0):OFFSET(t_data[[#Headers],[Prefix]],ROWS(t_data)+1,0)&"|"& OFFSET([@Connector],1,0):OFFSET(t_data[[#Headers],[Connector]],ROWS(t_data)+1,0), OFFSET([@[Results (tab)]],1,0):OFFSET(t_data[[#Headers],[Results (tab)]],ROWS(t_data)+1,0))), [@Prefix]), [@Prefix]&ROWS(UNIQUE(FILTER( OFFSET(t_data[[#Headers],[Prefix]],1,0):[@Prefix]& OFFSET(t_data[[#Headers],[Connector]],1,0):[@Connector],( OFFSET(t_data[[#Headers],[Prefix]],1,0):[@Prefix]= [@Prefix])*( OFFSET(t_data[[#Headers],[Value]],1,0):[@Value]<>0)))))

 

[Peter_SSs]

I've updated even the "structured style", if somebody else would need this piece of work.

What about this col H formula as a simpler (or at least shorter) "structured style" option. (I added a new column between prefix and Connector just to check that it worked fine when they were not adjacent as you mentioned)
This formula also avoids the volatile function OFFSET

cerobit.xlsm
	A	B	C	D			G	H
1	Prefix	Column1	Connector	Value			Results (tab)	Results
2	A	data 1	1	-100			A1	A1
3	A	data 2	2	0			A	A
4	B	data 3	3	100			B1	B1
5	C	data 4	4	0			C	C
6	B	data 5	5	-100			B2	B2
7	A	data 6	6	100			A2	A2
8	B	data 7	7	0			B	B
9	A	data 8	8	0			A	A
10	A	data 9	8	0			A	A
11	B	data 10	9	0			B	B
12	B	data 11	10	0			B	B
13	C	data 12	11	-100			C1	C1
14	C	data 13	11	0			C1	C1
15	C	data 14	11	0			C1	C1
16	A	data 15	12	100			A3	A3
17	A	data 16	13	0			A	A
18	C	data 17	14	100			C2	C2
19	C	data 18	15	0			C	C
20	B	data 19	16	0			B	B
21	B	data 20	16	0			B	B
22	B	data 21	16	0			B	B
23	A	data 22	17	-100			A4	A4
24	C	data 23	18	0			C	C
25	C	data 24	18	0			C	C
26	C	data 25	19	100			C3	C3
27	A	data 26	20	100			A5	A5
28	A	data 27	21	0			A6	A6
29	A	data 28	21	50			A6	A6
30	A	data 29	21	-100			A6	A6
31	A	data 30	21	150			A6	A6
32	A	data 31	22	100			A7	A7
33	B	data 32	23	100			B3	B3
cerobit (3)

Cell Formulas
Range	Formula
G2:G33	G2	=IF([@Value]=0,IF(COUNTIFS([Prefix],[@Prefix],[Connector],[@Connector],[Value],"<>0"),XLOOKUP([@Prefix]&"|"&[@Connector],t_data[[#Headers],[Prefix]]:OFFSET([@Prefix],-1,0)&"|"&t_data[[#Headers],[Connector]]:OFFSET([@Connector],-1,0),t_data[[#Headers],[Results (tab)]]:OFFSET([@[Results (tab)]],-1,0),XLOOKUP([@Prefix]&"|"&[@Connector],OFFSET([@Prefix],1,0):OFFSET(t_data[[#Headers],[Prefix]],ROWS(t_data)+1,0)&"|"&OFFSET([@Connector],1,0):OFFSET(t_data[[#Headers],[Connector]],ROWS(t_data)+1,0),OFFSET([@[Results (tab)]],1,0):OFFSET(t_data[[#Headers],[Results (tab)]],ROWS(t_data)+1,0))),[@Prefix]),[@Prefix]&ROWS(UNIQUE(FILTER(OFFSET(t_data[[#Headers],[Prefix]],1,0):[@Prefix]&OFFSET(t_data[[#Headers],[Connector]],1,0):[@Connector],(OFFSET(t_data[[#Headers],[Prefix]],1,0):[@Prefix]=[@Prefix])*(OFFSET(t_data[[#Headers],[Value]],1,0):[@Value]<>0)))))
H2:H33	H2	=LET(p,[@Prefix],pc,[Prefix],cc,[Connector],vc,[Value],r,MIN(FILTER(SEQUENCE(ROWS(t_data)),(vc<>0)*(pc=p)*(cc=[@Connector]),NA())),IF([@Value]=0,IFNA(INDEX([Results],r),p),p&ROWS(UNIQUE(FILTER(pc&cc,(pc=p)*(vc<>0)*(ROW(pc)<=ROW()))))))

 

[cerobit]

Very nice modification and optimization of a volatile function, thank you so much.

There is one more related issue, where a similar optimization would be useful.

I am highlighting these consequenting/grouped rows with a top&bottom border using conditional formating. To manage this I have added some helping columns.

In a real application these consequenting/grouped rows are less common, than a single non-connected data rows, so this kind of visual highlighting is clearer than its negative variant (like border on every row and no border for connected data).

In one of a helping columns I am also using the OFFSET function. Could it be replaced with some non-volatile function?

order_of_occurrence_of_areas.xlsx
	A	B	C	D	E	F	G
1	Prefix	Connector	Value	Results (tab) 2	Helper 1	Helper 2	Helper 3
2	A	1	-100	A1	0	NEPRAVDA	NEPRAVDA
3	A	2	0	A	0	NEPRAVDA	NEPRAVDA
4	B	3	100	B1	0	NEPRAVDA	NEPRAVDA
5	C	4	0	C	0	NEPRAVDA	NEPRAVDA
6	B	5	-100	B2	0	NEPRAVDA	NEPRAVDA
7	A	6	100	A2	0	NEPRAVDA	NEPRAVDA
8	B	7	0	B	0	NEPRAVDA	PRAVDA
9	A	8	0	A	1	PRAVDA	NEPRAVDA
10	A	8	0	A	2	PRAVDA	PRAVDA
11	A	9	100	A3	0	NEPRAVDA	NEPRAVDA
12	B	10	100	B3	0	NEPRAVDA	NEPRAVDA
13	C	11	100	C1	0	NEPRAVDA	NEPRAVDA
14	B	12	0	B	0	NEPRAVDA	NEPRAVDA
15	B	13	0	B	0	NEPRAVDA	PRAVDA
16	C	14	-100	C2	1	PRAVDA	NEPRAVDA
17	C	14	0	C2	2	PRAVDA	NEPRAVDA
18	C	14	0	C2	3	PRAVDA	PRAVDA
19	A	15	100	A4	0	NEPRAVDA	NEPRAVDA
20	A	16	0	A	0	NEPRAVDA	NEPRAVDA
21	C	17	100	C3	0	NEPRAVDA	NEPRAVDA
22	C	18	0	C	0	NEPRAVDA	PRAVDA
23	B	19	0	B	1	PRAVDA	NEPRAVDA
24	B	19	0	B	2	PRAVDA	NEPRAVDA
25	B	19	0	B	3	PRAVDA	PRAVDA
26	A	20	-100	A5	0	NEPRAVDA	PRAVDA
27	C	21	0	C	1	PRAVDA	NEPRAVDA
28	C	21	0	C	2	PRAVDA	PRAVDA
29	C	22	100	C4	0	NEPRAVDA	NEPRAVDA
30	A	23	100	A6	0	NEPRAVDA	PRAVDA
31	A	24	0	A7	1	PRAVDA	NEPRAVDA
32	A	24	50	A7	2	PRAVDA	NEPRAVDA
33	A	24	-100	A7	3	PRAVDA	NEPRAVDA
34	A	24	150	A7	4	PRAVDA	PRAVDA
35	A	25	100	A8	0	NEPRAVDA	NEPRAVDA
36	B	26	100	B4	0	NEPRAVDA	NEPRAVDA
example (separation)

Cell Formulas
Range	Formula
D2:D36	D2	=LET( p,[@Prefix], pc,[Prefix], cc,[Connector], vc,[Value], r,MIN(FILTER(SEQUENCE(ROWS(t_data_with_border)),(vc<>0)*(pc=p)*(cc=[@Connector]),NA())), IF([@Value]=0, IFNA(INDEX([Results (tab) 2],r),p), p&ROWS(UNIQUE(FILTER(pc&cc,(pc=p)*(vc<>0)*(ROW(pc)<=ROW())))) ) )
E2:E36	E2	=LET( order_within_connected_data, IF(COUNTIF([Connector], [@Connector]) > 1, COUNTIF(t_data_with_border[[#Headers],[Connector]]:[@Connector], [@Connector]), 0), order_within_connected_data)
F2:F36	F2	=LET( order_within_connected_data, IF(COUNTIF([Connector], [@Connector]) > 1, COUNTIF(t_data_with_border[[#Headers],[Connector]]:[@Connector], [@Connector]), 0), is_it_connected_data, order_within_connected_data > 0, is_it_connected_data)
G2:G36	G2	=LET( border_follows, OR([@[Helper 2]] <> OFFSET([@[Helper 2]], 1, 0), AND([@[Helper 1]] > OFFSET([@[Helper 1]], -1, 0), OFFSET([@[Helper 1]], -1, 1) <> 0, [@[Helper 1]] > OFFSET([@[Helper 1]], 1, 0))), border_follows)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:G36	Expression	=$G2	text	NO

 

[Peter_SSs]

I'm not sure that I am fully understanding what you are trying to achieve but try this as a replacement for Helper 3

=LET(next,IFERROR(INDEX([Helper 1],ROW()-ROW(t_data_with_border[#Headers])+1),0),OR(AND([@[Helper 1]]=0,next=1),[@[Helper 1]]>next))

Note that this does not use helper 2 so unless you are using that for something else, perhaps you don't need it any more?