# Autofill Numbers In Merged Cells

#### Bugalho

##### New Member
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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### jasonb75

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
for topic less than 10.
MrExcel.xlsx
ABCDE
18text
28.1topic
38.1.1text
48.1.2text
58.1.3text
6
78.1.4text
88.2topic
98.2.1text
108.2.2text
118.2.3text
12
13
148.2.4text
158.2.5text
16
17
18
19
208.3topic
Sheet3 (2)
Cell Formulas
RangeFormula
A20,A14:A15,A2:A5,A7:A11A2=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)

Last edited:

#### shaowu459

##### Well-known Member

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

##### New Member
Thank you very much shaowu459! Very helpful
Regards!

#### Bugalho

##### New Member

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

##### Well-known Member
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

##### Well-known Member
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
ABCDE
18text
28.1text
38.1.1text
48.1.2text
58.1.3text
6
78.1.4text
88.2text
98.2.1text
108.2.2text
118.2.3text
128.2.4text
138.2.5text
148.2.6text
158.2.7text
168.2.8text
178.2.9text
18
19
208.2.10text
218.2.11text
22
23
24
25
268.3text
278.4text
288.4.1text
298.4.2text
308.5text
318.5.1text
328.5.2text
338.6text
348.6.1text
358.6.2text
368.7text
378.7.1text
388.7.2text
398.8text
408.8.1text
418.8.2text
428.9text
438.9.1text
448.9.2text
458.10text
468.10.1text
478.10.2text
488.11text
498.11.1text
508.11.2text
518.12text
528.12.1text
538.12.2text
549text
559.1text
569.1.1text
579.1.2text
589.1.3text
59
609.1.4text
619.2text
629.2.1text
639.2.2text
649.2.3text
659.2.4text
Sheet3
Cell Formulas
RangeFormula
A60:A65,A26:A58,A20:A21,A2:A5,A7:A17A2=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)&".*")))

Last edited:

#### Bugalho

##### New Member
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
ABCDE
18text
28.1text
38.1.1text
48.1.2text
58.1.3text
6
78.1.4text
88.2text
98.2.1text
108.2.2text
118.2.3text
128.2.4text
138.2.5text
148.2.6text
158.2.7text
168.2.8text
178.2.9text
18
19
208.2.10text
218.2.11text
22
23
24
25
268.3text
278.4text
288.4.1text
298.4.2text
308.5text
318.5.1text
328.5.2text
338.6text
348.6.1text
358.6.2text
368.7text
378.7.1text
388.7.2text
398.8text
408.8.1text
418.8.2text
428.9text
438.9.1text
448.9.2text
458.10text
468.10.1text
478.10.2text
488.11text
498.11.1text
508.11.2text
518.12text
528.12.1text
538.12.2text
549text
559.1text
569.1.1text
579.1.2text
589.1.3text
59
609.1.4text
619.2text
629.2.1text
639.2.2text
649.2.3text
659.2.4text
Sheet3
Cell Formulas
RangeFormula
A60:A65,A26:A58,A20:A21,A2:A5,A7:A17A2=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!

Replies
11
Views
200
Replies
0
Views
101
Replies
2
Views
17
Replies
1
Views
221
Replies
0
Views
189

1,136,734
Messages
5,677,455
Members
419,693
Latest member
divtjd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back