# Numbering multiple rows sequentially

#### Walkon

##### New Member
Good afternoon,

I am assigning data to readers. The primary rows of data are on the first sheet in the workbook, one row per data point. On the second sheet, there are two rows of data related to each row on the first sheet. I would like to number each of the rows on the second sheet the same sequential number as its respective row on the first sheet. Therefore, on sheet 1, the rows are numbered sequentially going up by one increment on each row (e.g., 1, 2, 3, 4, etc.) and on the second sheet, two rows need to have the same number, follwed by the next two rows again with the next sequential number (e.g., 1, 1, 2, 2, 3, 3, 4, 4, etc.). I have tried to use Flash Fill but haven't been able to get it to recognize the pattern.
Any suggestions greatly appreciated.

Best,

Rob

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
In Sheet1 change the first argument in the SEQUENCE function to match the number of rows you want to number.
In Sheet2 enter the 1s manually in the first two rows.
Book1
A
11
22
33
44
55
66
77
88
99
1010
Sheet1
Cell Formulas
RangeFormula
A1:A10A1=SEQUENCE(10,1,1,1)
Dynamic array formulas.

Book1
A
11
21
32
42
53
63
74
84
95
105
116
126
137
147
158
168
179
189
1910
2010
Sheet2
Cell Formulas
RangeFormula
A3:A20A3=IF(ROW()>MAX(2*Sheet1!A:A),"",IF(A2=A1,A2+1,A2))

In Sheet1 change the first argument in the SEQUENCE function to match the number of rows you want to number.
In Sheet2 enter the 1s manually in the first two rows.
Book1
A
11
22
33
44
55
66
77
88
99
1010
Sheet1
Cell Formulas
RangeFormula
A1:A10A1=SEQUENCE(10,1,1,1)
Dynamic array formulas.

Book1
A
11
21
32
42
53
63
74
84
95
105
116
126
137
147
158
168
179
189
1910
2010
Sheet2
Cell Formulas
RangeFormula
A3:A20A3=IF(ROW()>MAX(2*Sheet1!A:A),"",IF(A2=A1,A2+1,A2))
Good morning,

Thank-you very much for your assistance. The formula for sheet 1 works great. I am having a bit of a challenge with the sheet 2 formula however. I likely should have mentioned in my original question, that the data rows for both pages do not always start with the number 1. The data comes in batches, however, while batch 1 can be numbered on sheet 1: 1 to say, 372, the rows of data from the next batch, which is placed in a separate work book (or at least a separate worksheet), must start with 373.

I tried using the formula you kindly provided, but modified it to fit my workbook: =IF(ROW()>MAX(2*Applications!E:E,"",IF(B3=B2,B3+1,B3)) however I get a #VALUE! error.

Any suggestions greatly appreciated.

Best,

Rob

Please post some sample data and expected result using XL2BB.

You could use SEQUENCE for the sheet2 formula too:

Excel Formula:
``=INT(SEQUENCE(20,,373,1/2))``

The 20 is the number of rows you want, the 373 is the starting point, and the 2 (in 1/2) is the number of times you want each value to repeat.

You could use SEQUENCE for the sheet2 formula too:

Excel Formula:
``=INT(SEQUENCE(20,,373,1/2))``

The 20 is the number of rows you want, the 373 is the starting point, and the 2 (in 1/2) is the number of times you want each value to repeat.
Good afternoon,

Thank-you very much, this worked like a charm!

Best,

Rob

Please post some sample data and expected result using XL2BB.
Good afternoon,

Thank-you for your continued support, I truly appreciate the quick replies. The fellow below suggested an alternative formula which seems to have worked very well on sheet 2.

Best,

Rob

Glad we could help! Thanks for the update.

Replies
3
Views
295
Replies
2
Views
60
Replies
4
Views
147
Replies
3
Views
396
Replies
5
Views
164

1,203,027
Messages
6,053,120
Members
444,640
Latest member
Dramonzo

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