Autofill Numbers In Merged Cells

Bugalho

New Member
Joined
Jun 1, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.

Webp.net-compress-image.jpg


Best regards
Élson
 

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,193
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
60,112
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 26, 2018
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 26, 2018
Messages
560
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
11.gif
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 26, 2018
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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.
11.gif

11.gif

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
Joined
Jun 1, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top