Insert number to group of cells based on empty cell

[gzell]

I have a large spread sheet that I would like to insert number to a group of cells (Column B) based on empty cell in column E. At next empty cell add 1 to number and insert in cells in column B and loop until end of worksheet. The spreadsheet is a research document and has 27K+ rows. This will allow me to know the exact number of people in the document. Any help would be greatly appreciated.

 

[DanteAmor]

You could give an example of how your data is and how you want the result.

 

[gzell]

	A	B	C	D	E
1	Row_ID	Person_ID	Name		Research Data
2	1	1	Able, Ezekiel
3	2	1	Able, Ezekiel	1_1	Army: Confederacy
4	3	1	Able, Ezekiel	1_2	Location: Texas
5	4	1	Able, Ezekiel	1_3	Regiment: 10th Regiment, Texas Infantry (Nelson's)
6	5	1	Able, Ezekiel	1_4	Function: Infantry
7	6	1	Able, Ezekiel	1_5	Company: C
8	7	1	Able, Ezekiel	1_6	Rank: Private
9	8	1	Able, Ezekiel	1_7	Age: Unknown
10	9	1	Able, Ezekiel	1_8	Residence: Unknown
11	10	1	Able, Ezekiel	1_9	Enrolled: Enrolled: Buchanan, Texas
12	11	1	Able, Ezekiel	1_10	Date: October 16, 1861
13	12	1	Able, Ezekiel	1_11	Enlisted: Houston, Texas
14	13	1	Able, Ezekiel	1_12	Date: October 25, 1861
15	14	1	Able, Ezekiel		Detail: Hospital Nurse
16	15	1	Able, Ezekiel		Date: November 8, 1862
17	16	1	Able, Ezekiel		Relieved of Duty: Nurse
18	17	1	Able, Ezekiel		Date: February 4, 1862
19	18	1	Able, Ezekiel		Captured: Battle of Arkansas Post
20	19	1	Able, Ezekiel		Date: January 11, 1863
21	20	1	Able, Ezekiel		Forwarded: St. Louis, Missouri via Boats
22	21	1	Able, Ezekiel		Forwarded: Camp Douglas, Illinois via Rail
23	22	1	Able, Ezekiel		Prisoner: Camp Douglas, Illinois
24	23	1	Able, Ezekiel		Died: March 7, 1863
25	24	1	Able, Ezekiel		Cause: Small Pox
26	26	2	Adams, John H.
27	27	2	Adams, John H.	1_1	Army: Confederacy
28	28	2	Adams, John H.	1_2	Location: Texas
29	29	2	Adams, John H.	1_3	Regiment: 10th Regiment, Texas Infantry (Nelson's)
30	30	2	Adams, John H.	1_4	Function: Infantry
31	31	2	Adams, John H.	1_5	Company: I
32	32	2	Adams, John H.	1_6	Rank: Corporal
33	33	2	Adams, John H.	1_7	Age: 42
34	34	2	Adams, John H.	1_8	Residence: Parker County, Texas
35	35	2	Adams, John H.	1_9	Enlisted: Kimball, Texas or Millican, Texas
36	36	2	Adams, John H.	1_10	Date: January 16, 1862
37	37	2	Adams, John H.		Captured: Battle Arkansas Post, Arkansas
38	38	2	Adams, John H.		Date: January 11, 1863
39	39	2	Adams, John H.		Forward: St. Louis, Missouri via Boats
40	40	2	Adams, John H.		Forward: Camp Douglas, Illinois via Rail
41	41	2	Adams, John H.		Prisoner: Camp Douglas, Illinois
42	42	2	Adams, John H.		Forwarded for Exchanged_1: City Point, Virginia
43	43	2	Adams, John H.		Date: April 10, 1863
44	44	2	Adams, John H.		Hospital: Sick
45	45	2	Adams, John H.		Location: Kingston, Georgia
46	46	2	Adams, John H.		Date: March 22, 1864
47	47	2	Adams, John H.		Captured: Franklin, Tennessee
48	48	2	Adams, John H.		Date: November 30, 1864
49	49	2	Adams, John H.		Forwarded: Nashville, Tennessee
50	50	2	Adams, John H.		Forwarded: Louisville, Kentucky
51	51	2	Adams, John H.		Arrived_LOUISVILLE: December 3, 1864
52	52	2	Adams, John H.		Forwarded: Camp Douglas, Illinois
53	53	2	Adams, John H.		Date: December 3, 1863
54	54	2	Adams, John H.		Arrived: December 6, 1863
55	55	2	Adams, John H.		Oath of Alegiance: June 18, 1865
56	56	3	Adams, Peter L.
57	57	3	Adams, Peter L.	1_1	Army: Confederacy
58	58	3	Adams, Peter L.	1_2	Location: Texas
59	59	3	Adams, Peter L.	1_3	Regiment: 10th Regiment, Texas Infantry (Nelson's)
60	60	3	Adams, Peter L.	1_4	Company: D
61	61	3	Adams, Peter L.	1_5	Function: Infantry
62	62	3	Adams, Peter L.	1_6	Rank: Private
63	63	3	Adams, Peter L.	1_7	Age: --
64	64	3	Adams, Peter L.	1_8	Residence: --
65	65	3	Adams, Peter L.	1_9	Enrolled: Fairfield, Freestone County, Texas
66	66	3	Adams, Peter L.	1_10	Date: October 26, 1861
67	67	3	Adams, Peter L.	1_11	Enlisted: Hempstead, Texas
68	68	3	Adams, Peter L.	1_12	Date: October 31, 1863
69	69	3	Adams, Peter L.		Absent_1: January 1862-April 1864

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Master​

Worksheet Formulas Cell Formula A1 Row_ID B1 Person_ID C1 Name D1 E1 Research Data <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Here it is.

 

[Fluff]

How about

Sub gzell()
   Dim Rng As Range
   Dim i As Long
   For Each Rng In Range("E2:E" & Rows.Count).SpecialCells(xlConstants).Areas
      i = i + 1
      Rng.Offset(-1, -3).Resize(Rng.Count + 1).Value = i
   Next Rng
End Sub

 

[DanteAmor]

How about without loop

At least on my computer the test with 90,000 records, is 4 seconds faster than the Fluff macro.

Sub DAMmacro4()
  Range("B2").Value = 1
  With Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(RC[3]<>"""",R[-1]C,R[-1]C+1)"
    .Value = .Value
  End With
End Sub

 

[gzell]

Thanks for the help.

 

[gzell]

Thank you for the help. Works great

 

[Fluff]

You're welcome & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.