Numbering multiple rows sequentially

[Walkon]

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

 

[JoeMo]

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
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
Sheet1

Cell Formulas
Range	Formula
A1:A10	A1	=SEQUENCE(10,1,1,1)
Dynamic array formulas.

Book1
	A
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5
11	6
12	6
13	7
14	7
15	8
16	8
17	9
18	9
19	10
20	10
Sheet2

Cell Formulas
Range	Formula
A3:A20	A3	=IF(ROW()>MAX(2*Sheet1!A:A),"",IF(A2=A1,A2+1,A2))

 

[Walkon]

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
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
Sheet1

Cell Formulas
Range	Formula
A1:A10	A1	=SEQUENCE(10,1,1,1)
Dynamic array formulas.

Book1
	A
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5
11	6
12	6
13	7
14	7
15	8
16	8
17	9
18	9
19	10
20	10
Sheet2

Cell Formulas
Range	Formula
A3:A20	A3	=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

 

[JoeMo]

Please post some sample data and expected result using XL2BB.

 

[Eric W]

You could use SEQUENCE for the sheet2 formula too:

=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.

 

[Walkon]

You could use SEQUENCE for the sheet2 formula too:

=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

 

[Walkon]

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

 

[Eric W]

Glad we could help! Thanks for the update.