Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Add number to cell based on another cell's value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Location
    Cool, Texas
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add number to cell based on another cell's value

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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add number to cell based on another cell's value

    How about in B2 copied down
    =COUNTIF(G$2:G2,"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add number to cell based on another cell's value

    Hello,

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

    Hope this will help
    Last edited by James006; Sep 7th, 2019 at 11:54 AM.

  4. #4
    New Member
    Join Date
    Apr 2019
    Location
    Cool, Texas
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add number to cell based on another cell's value

    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)

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add number to cell based on another cell's value

    Did you try either of the solutions offered?
    If so did they work? If not, in what way did they not work?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Apr 2019
    Location
    Cool, Texas
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add number to cell based on another cell's value

    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.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add number to cell based on another cell's value

    Did you try my suggestion?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add number to cell based on another cell's value

    Quote Originally Posted by gzell View Post
    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 by James006; Sep 10th, 2019 at 08:59 AM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Add number to cell based on another cell's value

    @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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Apr 2019
    Location
    Cool, Texas
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add number to cell based on another cell's value

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •