# Excel Question **URL:** \ **Category:** Factual Questions\ **Created:** [June 18, 2002, 4:34pm UTC](https://boards.straightdope.com/t/excel-question/114703 "2002-06-18T16:34:38Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![BZ00000](https://avatars.discourse-cdn.com/v4/letter/b/47e85d/32.png) [@BZ00000](https://boards.straightdope.com/u/BZ00000)\ **Post date:** [June 18, 2002, 4:34pm UTC](https://boards.straightdope.com/t/excel-question/114703/1 "2002-06-18T16:34:38Z")
I have census of the employees that show the Employee Number, Last name, First name. Then under the employees the dependents are listed with only the last name and first name fields populated. What I need to do is get a total number of dependents for each employee. Is there a way to input a formula that will allow me to add up the dependents for each employee. The excel looks like this: Emp # Last First 15481 ADAMS BOB ADAMS MIKE 65642 SMITH FRANK JONES STEVE SMITH FRED 3215 WEST DONNA WEST BETTY WEST JIM WEST ED WEST BOB WEST VICKI So I would want to have it total 1 for Adams, 2 for Smith, and 6 for West. I think any formula would have to be based on the Employee # field because the last names can be and are different in some cases. The actual excel file has thousands of names. ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [June 18, 2002, 4:43pm UTC](https://boards.straightdope.com/t/excel-question/114703/2 "2002-06-18T16:43:37Z")
Sounds more like a job for a relational database, like Access. With a good relational database design, you will be able to expand this as well, in case your data needs on employees and dependents grows in the future. Zev Steinhardt ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [June 18, 2002, 6:31pm UTC](https://boards.straightdope.com/t/excel-question/114703/3 "2002-06-18T18:31:42Z")
If you’re interested in writing a macro, it should be easy to do. Start the cell pointer at the first cell, with a counter variable set to 1. Tell it to look at (not move to) the cell below. If it sees text, increase the variable by 1, then look at the cell below that. Repeat until it sees a number. When it sees a number instead of text, tell it to record the value of the variable two columns over (or however many to make it show up on the same line as the employee number but in an empty, available column. Then move to the cell that has the number it saw, reset the counter to 1 and recurse the whole procedure. Hope this helps. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [June 18, 2002, 6:38pm UTC](https://boards.straightdope.com/t/excel-question/114703/4 "2002-06-18T18:38:48Z")
Wait. In looking at your example, I don’t see how you get “1 for Adams, 2 for Smith, and 6 for West.” There’s 1 name under Adams, not including Adams Bob, and there’s two names under Smith, not including Smith Frank, but the only way to get 6 for West is to include West Donna. Anyway, in the summary I gave above, if you want to include the name with the employee number, set the counter to 1. If you don’t, set it to 0. ---
**Author:** ![BZ00000](https://avatars.discourse-cdn.com/v4/letter/b/47e85d/32.png) [@BZ00000](https://boards.straightdope.com/u/BZ00000)\ **Post date:** [June 18, 2002, 8:56pm UTC](https://boards.straightdope.com/t/excel-question/114703/5 "2002-06-18T20:56:51Z")
I added the numbers wrong for West. I did, however, find a great message board for Excel questions. > **[MrExcel Message Board](https://www.mrexcel.com/board/)** > > Your One Stop for Excel Tips & Solutions If you have Excel questions, I suggest you try this site. Very helpful and fast. And thank you to everoine’s help here, too. ---
**Author:** ![KarmaComa](https://avatars.discourse-cdn.com/v4/letter/k/e36b37/32.png) [@KarmaComa](https://boards.straightdope.com/u/KarmaComa)\ **Post date:** [June 18, 2002, 10:18pm UTC](https://boards.straightdope.com/t/excel-question/114703/6 "2002-06-18T22:18:30Z")
> [@](#): > > \*Originally posted by KneadToKnow \* > **Then move to the cell that has the number it saw, reset the counter to 1 and recurse the whole procedure.** You may be better off iterating the procedure rather than recursing it. :smack: ---
**Author:** ![sailor](https://avatars.discourse-cdn.com/v4/letter/s/a587f6/32.png) [@sailor](https://boards.straightdope.com/u/sailor)\ **Post date:** [June 18, 2002, 11:06pm UTC](https://boards.straightdope.com/t/excel-question/114703/7 "2002-06-18T23:06:49Z")
While you’re all here, is there some way to make excel save a backup copy (like Word does) when you save it? ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [June 19, 2002, 1:24am UTC](https://boards.straightdope.com/t/excel-question/114703/8 "2002-06-19T01:24:54Z")
> [@](#): > > \*Originally posted by KarmaComa \* > \*\*You may be better off iterating the procedure rather than recursing it. \*\* Some days, I only curse my Excel procedures. Other days, I recurse it … 😃 ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [June 19, 2002, 12:57pm UTC](https://boards.straightdope.com/t/excel-question/114703/9 "2002-06-19T12:57:20Z")
> [@](#): > > \*Originally posted by sailor \* > While you’re all here, is there some way to make excel save a backup copy (like Word does) when you save it? File \> Save \> Options \> Always create backup > [@](#): > > \*Originally posted by KarmaComa \* > You may be better off iterating the procedure rather than recursing it. I did misspell it, but according to [http://www.m-w.com](http://www.m-w.com), recurs means > [@](#): > > to occur again after an interval : occur time after time ---
**Author:** ![LateComer](https://avatars.discourse-cdn.com/v4/letter/l/b5ac83/32.png) [@LateComer](https://boards.straightdope.com/u/LateComer)\ **Post date:** [June 19, 2002, 1:11pm UTC](https://boards.straightdope.com/t/excel-question/114703/10 "2002-06-19T13:11:57Z")
If they’re in a definable range, you can do something simpler than a macro such as: =counta(range)-countblank(range) I would also second the Access idea. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [June 19, 2002, 1:14pm UTC](https://boards.straightdope.com/t/excel-question/114703/11 "2002-06-19T13:14:41Z")
That link above to [http://www.m-w.com](http://www.m-w.com) won’t work. This one should. And I meant to say also that **KarmaComa** ’s point is well taken and I should have just said “repeat.” 🙂 **Knead** Trying not to spend all his five-dollar words in one place