Add number to cell based on another cell's value

gzell

New Member
Joined
Apr 20, 2019
Messages
37
I am trying to find a way to insert a number in Column B (Person_ID) based on Cell Column G that is Blank. Then repeat the number in Column B until reach the next blank cell in Column G. Add 1 to number and repeat, thus giving each soldier an ID number. This would Loop to the end of worksheet. There are 27,000+ rows in spreadsheet. Below Ezekiel Able would be 1 in worksheet and next soldier would be 2 and so on.

Any help would be greatly appreciated.

Row_IDPerson_IDLast NameFirst NameMiddleData_IDResearch Data
1AbleEzekiel
2AbleEzekiel1_1Army: Confederacy
3AbleEzekiel1_2Location: Texas
4AbleEzekiel1_3Regiment: 10th Regiment Texas Infantry (Nelson's)
5AbleEzekiel1_4Function: Infantry
6AbleEzekiel1_5Company: C
7AbleEzekiel1_6Rank: Private
8AbleEzekiel1_7Age: Unknown
9AbleEzekiel1_8Residence: Unknown
10AbleEzekiel1_9Enrolled: Buchanan Texas
11AbleEzekiel1_10Date: October 16 1861
12AbleEzekiel1_11Enlisted: Houston Texas
13AbleEzekiel1_12Date: October 25 1861
14AbleEzekiel2_1Detail:: Hospital Nurse
15AbleEzekiel2_1Detail: November 8 1862
16AbleEzekiel3_1Relieved of Duty: Nurse
17AbleEzekiel3_1Date: February 4 1862
18AbleEzekiel4_1Captured: Battle of Arkansas Post
19AbleEzekiel4_1Date: January 11 1863
20AbleEzekiel4_2Forwarded: St. Louis Missouri via Boats
21AbleEzekiel4_3Forwarded: Camp Douglas Illinois via Rail
22AbleEzekiel4_4Prisoner: Camp Douglas Illinois
23AbleEzekiel4_5Died: March 7 1863
24AbleEzekiel4_5Cause: Small Pox
25AdamsJohnH
26AdamsJohnH1_1Army: Confederacy
27AdamsJohnH1_2Location: Texas
28AdamsJohnH1_3Regiment: 10th Regiment Texas Infantry (Nelson's)
29AdamsJohnH1_4Function: Infantry
30AdamsJohnH1_5Company: I
31AdamsJohnH1_6Rank: Corporal
32AdamsJohnH1_7Age: 42
33AdamsJohnH1_8Residence: Parker County Texas
34AdamsJohnH1_9Enlisted: Kimball Texas or Millican Texas
35AdamsJohnH1_10Date: January 16 1862
36AdamsJohnH1_13Captured: Battle Arkansas Post Arkansas
37AdamsJohnH1_13Date: January 11 1863
38AdamsJohnH1_13Forwarded: St. Louis Missouri via Boats
39AdamsJohnH1_13Forwarded: Camp Douglas Illinois via Rail
40AdamsJohnH1_13Prisoner: Camp Douglas Illinois
41AdamsJohnH1_13Forwarded for Exchanged: City Point Virginia
42AdamsJohnH1_13 Date: April 10 1863
43AdamsJohnHHospital: Sick
44AdamsJohnHLocation: Kingston Georgia
45AdamsJohnHDate: March 22 1864
46AdamsJohnHCaptured: Franklin Tennessee
47AdamsJohnHDate: November 30 1864
48AdamsJohnHForwarded: Nashville Tennessee
49AdamsJohnHForwarded: Louisville Kentucky
50AdamsJohnHArrived: December 3 1864
51AdamsJohnHForwarded: Camp Douglas Illinois
52AdamsJohnHDate: December 3 1863
53AdamsJohnHArrived: December 6 1863
54AdamsJohnHOath of Alegiance: June 18 1865
55AdamsPeterL
56AdamsPeterL1_1Army: Confederacy
57AdamsPeterL1_2Location: Texas
58AdamsPeterL1_3Regiment: 10th Regiment Texas Infantry (Nelson's)
59AdamsPeterL1_4Function: Infantry
60AdamsPeterL1_5Company: D
61AdamsPeterL1_6Rank: Private
62AdamsPeterL1_7Age: --
63AdamsPeterL1_8Residence: --
64AdamsPeterL1_9Enrolled: Fairfield Freestone County Texas
65AdamsPeterL1_10Date: October 26 1861
66AdamsPeterL1_11Enlisted: Hempstead Texas
67AdamsPeterL1_12Date: October 31 1863
68AdamsPeterLAbsent: January 1862-April 1864
69AdamsWilliamL
70AdamsWilliamL1_1Army: Confederacy
71AdamsWilliamL1_2Location: Texas
72AdamsWilliamL1_3Regiment: 10th Regiment Texas Infantry (Nelson's)
73AdamsWilliamL1_4Function: Infantry
74AdamsWilliamL1_5Company: D
75AdamsWilliamL1_6Rank: Private
76AdamsWilliamL1_7Age: --
77AdamsWilliamL1_8Residence: --
78AdamsWilliamL1_11Enlisted: Camp Brazos Texas
79AdamsWilliamL1_12Date: April 1 1862
80AdamsWilliamLHospital: Sick
81AdamsWilliamLLocation: Brownsville Arkansas
82AdamsWilliamLDate: June 17 1862
83AdamsWilliamLAbsent: January 1863-April 1864

<tbody>
</tbody>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
How about in B2 copied down
=COUNTIF(G$2:G2,"")
 
Upvote 0
Hello,

In cell B3, you can try =IF(AND(C3=C2,D3=D2),B2+1,B3)

Hope this will help
 
Last edited:
Upvote 0
thanks for the info. But I am trying to get this through 27,000 rows with number changing based on another cell's value (empty)
 
Upvote 0
Did you try either of the solutions offered?
If so did they work? If not, in what way did they not work?
 
Upvote 0
Tried James suggestion. It places 1 in cell B4. I would like to give each person a id number. Ezekiel Able would have a 1 for in column B for each row associated with his name, John Adams would be 2, etc. Sorry for not making this clear in the original post.
 
Upvote 0
Did you try my suggestion?
 
Upvote 0
Tried James suggestion. It places 1 in cell B4. I would like to give each person a id number. Ezekiel Able would have a 1 for in column B for each row associated with his name, John Adams would be 2, etc. Sorry for not making this clear in the original post.

Tested with your sample data... looks like it does generate your expected results ...
 
Last edited:
Upvote 0
@James006
With your formula I get the numbers 1 to 23 in B3:B25 & then nothing but 1s thereafter.
That's not to mention the circular references error.
 
Upvote 0
yes, I tried it. works as expected. thanks. I am trying to find a solution that will number all rows for each individual. Thank you for the response.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top