Military Members

[thetank]

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

 

[thetank]

Cool, so how are you setting up the count of name and rating table on the right?

 

[Mark W.]

Cool, so how are you setting up the count of name and rating table on the right?

It's a PivotTable.

 

[thetank]

Not familiar with pivot tables!?!?

 

[Mark W.]

Not familiar with pivot tables!?!?

Well, there's no time like the present to learn.

Simply, select a single cell in your data list, and choose the "PivotTable..." entry in the Data menu. Let the wizard take you by the hand. It's all drag and drop.

 

[thetank]

Thanks for your help.

 

[Mark W.]

To learn more just enter "PivotTable" as a keyword on the Answer Wizard tab of the Excel online Help system.

 

[2rrs]

Try this link: www.excel-vba.com

Look for Dynamic reporting

There is a sample spreadsheet setup that will do what you are looking for.

There is no VBA (macro) in this workbook, He just uses SUMPRODUCT formulas and drop-down lists created with Data/Validation.

Sorry for the Butt in; I did not see all the exchanges till after I posted.

 

[thetank]

It's the least that I can do for the men and women in uniform...

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Rate/Rank	Name	Rating	Grade		Count of Name	Rating
2	ABF3	LANG	ABF	E4		Grade	ABF	BM	BU	Grand Total
3	BM2	SHOC	BM	E5		E4	1			1
4	BMC	GRAY	BM	E7		E5		1	11	12
5	BU1	DAIL	BU	E6		E6			16	16
6	BU1	SCHW	BU	E6		E7		1		1
7	BU1	HATC	BU	E6		Grand Total	1	2	27	30
8	BU1	ALBE	BU	E6
9	BU1	NELS	BU	E6
10	BU1	CHEN	BU	E6
11	BU1	LEWI	BU	E6
12	BU1	WALL	BU	E6
13	BU1	GAMM	BU	E6
14	BU1	MEEL	BU	E6
15	BU1	GOIN	BU	E6
16
17
18
Sheet1

This worked for most but there was a problem with a few of the rates. Here they are:

CEC MIHA
CEC HAVI
CMC TURN
CMC LUCI
CMC MCCR
CMC EATO
CMC BLAC
CMCS HAMI
CMCS ATTE
CSCS FREI
CSSN NEAL
CUCM HEIK

These returned a blank spot in the rate column and gave a rank of E7

CEC is a Construction Electrition E7
CMC is a Construction Mechanic E7
CMCS is a Construction Mechanic E8
CSCS is a Culinary Specialist E8
CSSN is a Culinary SPecialist E3
CUCM is a Construcion Utilites E9

All of the rates can have a C, CS, or CM behind them.

Also, I want to protect the sheet so it can't be changed and leave about 600 cells for entry. If there is no data I would like it to be blank. Right now it returns a #N/A if there is no data in front.

Thanks again

 

[Mark W.]

These returned a blank spot in the rate column and gave a rank of E7

CEC is a Construction Electrition E7
CMC is a Construction Mechanic E7
CMCS is a Construction Mechanic E8
CSCS is a Culinary Specialist E8
CSSN is a Culinary SPecialist E3
CUCM is a Construcion Utilites E9

All of the rates can have a C, CS, or CM behind them.

What's a SN?

 

[thetank]

The Navy has several different "services" within itself.

From E1 to E3 you have four different rank structures:

Seaman Hospitalman Fireman Constructionman
E1: SR HR FR CR
E2: SA HA FA CA
E3: SN HN FN CN

This is why I am having a hard time. I don't know Excel well enough to figure out how to keep them all separated.

I am with a construction battallion and we typically have only construction related rates, but since we are reservists, they sometimes put non construction related personel in our unit. They eventually change to construction ratings, but we still have to account for them in their current rate.