Excel Question

Factual Questions
1

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.

2

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

3

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.

4

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.

5

I added the numbers wrong for West.

I did, however, find a great message board for Excel questions.

If you have Excel questions, I suggest you try this site. Very helpful and fast.

And thank you to everoine’s help here, too.

6

You may be better off iterating the procedure rather than recursing it.

:smack:

7

While you’re all here, is there some way to make excel save a backup copy (like Word does) when you save it?

8

Some days, I only curse my Excel procedures. Other days, I recurse it …
:smiley:

9

File > Save > Options > Always create backup

I did misspell it, but according to http://www.m-w.com, recurs means

10

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.

11

That link above to 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.” :slight_smile:

Knead
Trying not to spend all his five-dollar words in one place