Autofill Numbers In Merged Cells

[Bugalho]

I know there are some topics about autofill in merged cells, but I couldn't find what I need. I don't even know if it's possible.


Is there a way to autofill numbers in this kind of table? (see image bellow). There are cells merged in rows and columns. Numbers with 2 or 3 digits, depending on the number of columns.

Best regards
Élson

 

[jasonb75]

You can only autofill merged ranges that are equal in size (same number of rows and columns).

Anything with more than one dot / period is a text string, not a number. Even without merged cells, the pattern would be difficult (if not impossible) to create.

 

[Fluff]

Cross posted Autofill Numbers In Merged Cells

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[shaowu459]

for topic less than 10.

MrExcel.xlsx
	A	B	C	D	E
1	8		text
2	8.1			topic
3	8.1.1				text
4	8.1.2				text
5	8.1.3				text
6
7	8.1.4				text
8	8.2			topic
9	8.2.1				text
10	8.2.2				text
11	8.2.3				text
12
13
14	8.2.4				text
15	8.2.5				text
16
17
18
19
20	8.3			topic
Sheet3 (2)

Cell Formulas
Range	Formula
A20,A14:A15,A2:A5,A7:A11	A2	=IF(MATCH("*",B2:Z2,)=3,A$1&"."&COUNTA(D$1:D1)+1,LEFT(LOOKUP(1,0/(A$1:A1<>""),A$1:A1),3)&"."&COUNTIF(A$1:A1,LEFT(LOOKUP(1,0/(A$1:A1<>""),A$1:A1),3)&".*")+1)

 

[shaowu459]

1) copy formula
2) select column A:A
3)Ctrl+G---special---blanks--OK
4) paste formula in editing bar
5) hold Ctrl and then press Enter.

 

[Bugalho]

Thank you very much shaowu459! Very helpful
Regards!

 

[Bugalho]

1) copy formula
2) select column A:A
3)Ctrl+G---special---blanks--OK
4) paste formula in editing bar
5) hold Ctrl and then press Enter.
View attachment 15213

It is possible for topic bigger than 10?

 

[shaowu459]

It is possible for topic bigger than 10?

May be it is possible, But the formula will be longer. If i can solve it, i will post the solution later.

 

[shaowu459]

there must be an easier formula, but i can not come up it with right now. only for your reference.
Type 8 or other number to begin and you can change the value in A1.

MrExcel.xlsx
	A	B	C	D	E
1	8		text
2	8.1			text
3	8.1.1				text
4	8.1.2				text
5	8.1.3				text
6
7	8.1.4				text
8	8.2			text
9	8.2.1				text
10	8.2.2				text
11	8.2.3				text
12	8.2.4				text
13	8.2.5				text
14	8.2.6				text
15	8.2.7				text
16	8.2.8				text
17	8.2.9				text
18
19
20	8.2.10				text
21	8.2.11				text
22
23
24
25
26	8.3			text
27	8.4			text
28	8.4.1				text
29	8.4.2				text
30	8.5			text
31	8.5.1				text
32	8.5.2				text
33	8.6			text
34	8.6.1				text
35	8.6.2				text
36	8.7			text
37	8.7.1				text
38	8.7.2				text
39	8.8			text
40	8.8.1				text
41	8.8.2				text
42	8.9			text
43	8.9.1				text
44	8.9.2				text
45	8.10			text
46	8.10.1				text
47	8.10.2				text
48	8.11			text
49	8.11.1				text
50	8.11.2				text
51	8.12			text
52	8.12.1				text
53	8.12.2				text
54	9		text
55	9.1			text
56	9.1.1				text
57	9.1.2				text
58	9.1.3				text
59
60	9.1.4				text
61	9.2			text
62	9.2.1				text
63	9.2.2				text
64	9.2.3				text
65	9.2.4				text
Sheet3

Cell Formulas
Range	Formula
A60:A65,A26:A58,A20:A21,A2:A5,A7:A17	A2	=IF(MATCH("*",B2:Z2,)=2,LOOKUP(1,0/(C$1:C1<>""),A$1:A1)+1,IF(MATCH("*",B2:Z2,)=3,LOOKUP(1,0/(C$1:C1<>""),A$1:A1)&"."&COUNTA(INDEX(D:D,LOOKUP(1,0/(C$1:C1<>""),ROW(C$1:C1))):D2),LOOKUP(1,0/(D$1:D1<>""),A$1:A1)&"."&1+COUNTIF(A$1:A1,LOOKUP(1,0/(D$1:D1<>""),A$1:A1)&".*")))

 

[Bugalho]

there must be an easier formula, but i can not come up it with right now. only for your reference.
Type 8 or other number to begin and you can change the value in A1.
View attachment 15251
View attachment 15252

MrExcel.xlsx
	A	B	C	D	E
1	8		text
2	8.1			text
3	8.1.1				text
4	8.1.2				text
5	8.1.3				text
6
7	8.1.4				text
8	8.2			text
9	8.2.1				text
10	8.2.2				text
11	8.2.3				text
12	8.2.4				text
13	8.2.5				text
14	8.2.6				text
15	8.2.7				text
16	8.2.8				text
17	8.2.9				text
18
19
20	8.2.10				text
21	8.2.11				text
22
23
24
25
26	8.3			text
27	8.4			text
28	8.4.1				text
29	8.4.2				text
30	8.5			text
31	8.5.1				text
32	8.5.2				text
33	8.6			text
34	8.6.1				text
35	8.6.2				text
36	8.7			text
37	8.7.1				text
38	8.7.2				text
39	8.8			text
40	8.8.1				text
41	8.8.2				text
42	8.9			text
43	8.9.1				text
44	8.9.2				text
45	8.10			text
46	8.10.1				text
47	8.10.2				text
48	8.11			text
49	8.11.1				text
50	8.11.2				text
51	8.12			text
52	8.12.1				text
53	8.12.2				text
54	9		text
55	9.1			text
56	9.1.1				text
57	9.1.2				text
58	9.1.3				text
59
60	9.1.4				text
61	9.2			text
62	9.2.1				text
63	9.2.2				text
64	9.2.3				text
65	9.2.4				text
Sheet3

Cell Formulas
Range	Formula
A60:A65,A26:A58,A20:A21,A2:A5,A7:A17	A2	=IF(MATCH("*",B2:Z2,)=2,LOOKUP(1,0/(C$1:C1<>""),A$1:A1)+1,IF(MATCH("*",B2:Z2,)=3,LOOKUP(1,0/(C$1:C1<>""),A$1:A1)&"."&COUNTA(INDEX(D:D,LOOKUP(1,0/(C$1:C1<>""),ROW(C$1:C1))):D2),LOOKUP(1,0/(D$1:D1<>""),A$1:A1)&"."&1+COUNTIF(A$1:A1,LOOKUP(1,0/(D$1:D1<>""),A$1:A1)&".*")))

Thnak you very much!! You are awesome!