Military Members

thetank · Mar 18, 2005

I am in the military and I need to keep track of how many people we have in our unit (over 500). They want to know how many of each rating we have and what pay grade they are in.

Example
BU1 Smith is an Builder (BU) and is an E6
BUC Nelson is a Builder (BU) and is an E7
BUCN Schmukatelli is a Builder (BU) and is an E3
EO3 Deere is an Equipment Operator (EO) and is an E4

We also have other ratings other than BU, EO etc.. that would fall into an other category, but they still want to know how many of each.

Basically I need to know how many E1's, E2's, E3's, etc we have: how many BU3's, BU2's, BU1's etc. we have, and then how many E5 and above we have in each category as well as overall.

I have gotten a majority of it figured out but I am having trouble getting the program to recognize the difference between say a ABFSN (Aviation Boatswains Mate E3) and an ABF1 (Aviation Boatswains Mate E6).

Thanks in advance

just_jon · Mar 18, 2005

So characters 3 thru x tell you the grade?

thetank · Mar 18, 2005

RE. Military Members

That is the hard part. Rates are:
E1 = CR
E2 = CA
E3 = CN
E4 = 3
E5 = 2
E6 = 1
E7 = C
E8 = CS
E9 = CM

Typically the rating in front is two characters (ie BU for Builder) however there are also rates that have three characters (ie ABF)

just_jon · Mar 18, 2005

Can you have an AB as well as an ABF? Trying to see if there will ever be a match at 2 characters and another at 3 for the 1st bit.

thetank · Mar 18, 2005

No there are no rates that start out the same. The rates are mostly two characters, however there are some that are three.

Those are just the enlisted rates. Officers are
ENS = O1
LTjg = O2
LT = O3
LCDR = O4
CDR = O5
CAPT = O6
RADM = O7

but there are only about 30 officers in the unit, so I can do them by hand if I need to. It's the enlisted that is the tedious task. The guy that did this before me has a pivot table set up, but I don't know how to use a pivot table and there are errors with it when you run into the rate with three characters and you physically have to subrtact out the E4 and below to get a count of E5-E9.

If you want, I can e-mail you the excel sheet that I have, so you can take a look.

Mark W. · Mar 18, 2005

Book1

A

B

C

D

E

F

G

H

I

J

K

1

BU1

E6

2

BUC

E7

3

BUCN

E3

4

EO3

E4

5

6

7

8

Sheet1

thetank · Mar 18, 2005

Mark, I think I can use that, but I don't think it is what I am looking for. I get the data from several different files. And I copy and paste just the rate/rank and last name. I don't need to have the last name, it is just there to keep it straight since I know most of their names and what their rates are.

They just want to know:

How many E6's we have overall and from that total they want to know how many BU1, EO1, CM1 etc...

How many E5's we have overall and from that total they want to know how many BU2, EO2, CM2 etc...

They want this for each rank.

I get a list of over 500 names and copy and paste into a sheet. Some months I have 520 some months it may only be 490. It fluctuates. This is only a portion of the list that I get

ABF3 LANG
BM2 SHOC
BMC GRAY
BU1 DAIL
BU1 SCHW
BU1 HATC
BU1 ALBE
BU1 NELS
BU1 CHEN
BU1 LEWI
BU1 WALL
BU1 GAMM
BU1 MEEL
BU1 GOIN
BU1 HAWK
BU1 JOHN
BU1 MORA
BU1 GASP
BU1 HIND
BU2 NOSA
BU2 BRAN
BU2 CLAY
BU2 WILT
BU2 BROW
BU2 WEIS
BU2 GILL
BU2 RENN
BU2 WINA
BU2 RUDD
BU2 NUSS

Like I said, I can email what I have and you can evaluate the best way to do it.