Change military grades into rank

air force guy

New Member
Joined
Apr 17, 2010
Messages
28
Afternoon all
Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work consistently, sometimes blank cell is civilian and formula stops working
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You may want to use Vlookup Function.

 
Upvote 0
You may want to use Vlookup Function.

I tried that before with no success. I have a column with all the grades (enlisted - officer) plus civilians. However that data is difficult to translate unless you have a cheat sheet. I want to create new column with the grade with is easier to understand
 
Upvote 0
It would help a great deal if you could show us a small sample of your data (sensitive data amended).
Either click on the XL2BB icon in the reply window (or in my signature block below where it says XL2BB) and it will take you to the boards XL2BB instruction and download page for the boards Addin to post usable screenshots.
 
Upvote 0
It would help a great deal if you could show us a small sample of your data (sensitive data amended).
Either click on the XL2BB icon in the reply window (or in my signature block below where it says XL2BB) and it will take you to the boards XL2BB instruction and download page for the boards Addin to post usable screenshots.
Thank you...will post tomorrow with what I have. I appreciate your help
 
Upvote 0
How about this?

Book1
ABCDEFGH
1Pay GradeRankAbbreviationClassificationPay GradeRank Abbr
2E-1Airman BasicABEnlisted AirmanE-9CMSgt
3E-2AirmanAmnEnlisted AirmanCCM
4E-3Airman First ClassA1CEnlisted AirmanCMSAF
5E-4Senior AirmanSrAEnlisted Airman 
6E-5Staff SergeantSSgtNoncommissioned Officer 
7E-6Technical SergeantTSgtNoncommissioned Officer 
8E-7Master SergeantMSgtNoncommissioned Officer 
9E-8Senior Master SergeantSMSgtNoncommissioned Officer 
10E-9Chief Master SergeantCMSgtNoncommissioned Officer 
11E-9Command Chief Master SergeantCCMNoncommissioned Officer 
12E-9Chief Master Sergeant Of The Air ForceCMSAFNoncommissioned Officer (Special) 
13O-1Second Lieutenant2d LtCommissioned Officer 
14O-2First Lieutenant1st LCommissioned Officer 
15O-3CaptainCaptCommissioned Officer 
16O-4MajorMajField Officer 
17O-5Lieutenant ColonelLt CoField Officer 
18O-6ColonelColField Officer 
19O-7Brigadier GeneralBrigGeneral Officer 
20O-8Major GeneralMaj GGeneral Officer 
21O-9Lieutenant GeneralLt GeGeneral Officer 
22O-10GeneralGenGeneral Officer 
23O-10General of the Air ForceGAFGeneral Officer 
Sheet3
Cell Formulas
RangeFormula
H2:H23H2=IFERROR(INDEX($C$2:$C$23,AGGREGATE(15,3,(($A$2:$A$23=$F$2)/($A$2:$A$23=$F$2)*ROW($A$2:$A$23))-ROW($A$1), ROWS($H$1:H1))),"")
 
Upvote 0
How about this?

Book1
ABCDEFGH
1Pay GradeRankAbbreviationClassificationPay GradeRank Abbr
2E-1Airman BasicABEnlisted AirmanE-9CMSgt
3E-2AirmanAmnEnlisted AirmanCCM
4E-3Airman First ClassA1CEnlisted AirmanCMSAF
5E-4Senior AirmanSrAEnlisted Airman 
6E-5Staff SergeantSSgtNoncommissioned Officer 
7E-6Technical SergeantTSgtNoncommissioned Officer 
8E-7Master SergeantMSgtNoncommissioned Officer 
9E-8Senior Master SergeantSMSgtNoncommissioned Officer 
10E-9Chief Master SergeantCMSgtNoncommissioned Officer 
11E-9Command Chief Master SergeantCCMNoncommissioned Officer 
12E-9Chief Master Sergeant Of The Air ForceCMSAFNoncommissioned Officer (Special) 
13O-1Second Lieutenant2d LtCommissioned Officer 
14O-2First Lieutenant1st LCommissioned Officer 
15O-3CaptainCaptCommissioned Officer 
16O-4MajorMajField Officer 
17O-5Lieutenant ColonelLt CoField Officer 
18O-6ColonelColField Officer 
19O-7Brigadier GeneralBrigGeneral Officer 
20O-8Major GeneralMaj GGeneral Officer 
21O-9Lieutenant GeneralLt GeGeneral Officer 
22O-10GeneralGenGeneral Officer 
23O-10General of the Air ForceGAFGeneral Officer 
Sheet3
Cell Formulas
RangeFormula
H2:H23H2=IFERROR(INDEX($C$2:$C$23,AGGREGATE(15,3,(($A$2:$A$23=$F$2)/($A$2:$A$23=$F$2)*ROW($A$2:$A$23))-ROW($A$1), ROWS($H$1:H1))),"")
Thank you...this is a lot longer than my IF formula. When I do a deployed pull I want to be able to copy and paste this to a new column then paste it all the way down
 
Upvote 0
With VBA custom function. I made the data into a table, you can replace 'Table7' with A2:D23 in this example.

Book1
ABCDEFGH
1Pay GradeRankAbbreviationClassificationNameGradeAbbr
2E-1Airman BasicABEnlisted AirmanBillO-8Maj G
3E-2AirmanAmnEnlisted AirmanSteveO-7Brig
4E-3Airman First ClassA1CEnlisted AirmanGaryO-10Gen, GAF
5E-4Senior AirmanSrAEnlisted AirmanCraigE-7MSgt
6E-5Staff SergeantSSgtNoncommissioned OfficerAdamO-8Maj G
7E-6Technical SergeantTSgtNoncommissioned OfficerGioE-9CMSgt, CCM, CMSAF
8E-7Master SergeantMSgtNoncommissioned Officer
9E-8Senior Master SergeantSMSgtNoncommissioned Officer
10E-9Chief Master SergeantCMSgtNoncommissioned Officer
11E-9Command Chief Master SergeantCCMNoncommissioned Officer
12E-9Chief Master Sergeant Of The Air ForceCMSAFNoncommissioned Officer (Special)
13O-1Second Lieutenant2d LtCommissioned Officer
14O-2First Lieutenant1st LCommissioned Officer
15O-3CaptainCaptCommissioned Officer
16O-4MajorMajField Officer
17O-5Lieutenant ColonelLt CoField Officer
18O-6ColonelColField Officer
19O-7Brigadier GeneralBrigGeneral Officer
20O-8Major GeneralMaj GGeneral Officer
21O-9Lieutenant GeneralLt GeGeneral Officer
22O-10GeneralGenGeneral Officer
23O-10General of the Air ForceGAFGeneral Officer
Sheet3
Cell Formulas
RangeFormula
H2:H7H2=GRADES(G2,Table7)


VBA Code:
Function GRADES(iLookup As String, Tbl As Range)
Dim AR() As Variant: AR = Tbl.Value

With CreateObject("System.Collections.ArrayList")
    For i = LBound(AR) To UBound(AR)
        If AR(i, 1) = iLookup Then .Add AR(i, 3)
    Next i
    
    GRADES = Join(.ToArray, ", ")
End With

End Function
 
Upvote 0
Or if you have Office 365 you can use this formula.

=TEXTJOIN(", ",TRUE,FILTER($C$2:$C$23,$A$2:$A$23=G2))
 
Upvote 0
With VBA custom function. I made the data into a table, you can replace 'Table7' with A2:D23 in this example.

Book1
ABCDEFGH
1Pay GradeRankAbbreviationClassificationNameGradeAbbr
2E-1Airman BasicABEnlisted AirmanBillO-8Maj G
3E-2AirmanAmnEnlisted AirmanSteveO-7Brig
4E-3Airman First ClassA1CEnlisted AirmanGaryO-10Gen, GAF
5E-4Senior AirmanSrAEnlisted AirmanCraigE-7MSgt
6E-5Staff SergeantSSgtNoncommissioned OfficerAdamO-8Maj G
7E-6Technical SergeantTSgtNoncommissioned OfficerGioE-9CMSgt, CCM, CMSAF
8E-7Master SergeantMSgtNoncommissioned Officer
9E-8Senior Master SergeantSMSgtNoncommissioned Officer
10E-9Chief Master SergeantCMSgtNoncommissioned Officer
11E-9Command Chief Master SergeantCCMNoncommissioned Officer
12E-9Chief Master Sergeant Of The Air ForceCMSAFNoncommissioned Officer (Special)
13O-1Second Lieutenant2d LtCommissioned Officer
14O-2First Lieutenant1st LCommissioned Officer
15O-3CaptainCaptCommissioned Officer
16O-4MajorMajField Officer
17O-5Lieutenant ColonelLt CoField Officer
18O-6ColonelColField Officer
19O-7Brigadier GeneralBrigGeneral Officer
20O-8Major GeneralMaj GGeneral Officer
21O-9Lieutenant GeneralLt GeGeneral Officer
22O-10GeneralGenGeneral Officer
23O-10General of the Air ForceGAFGeneral Officer
Sheet3
Cell Formulas
RangeFormula
H2:H7H2=GRADES(G2,Table7)


VBA Code:
Function GRADES(iLookup As String, Tbl As Range)
Dim AR() As Variant: AR = Tbl.Value

With CreateObject("System.Collections.ArrayList")
    For i = LBound(AR) To UBound(AR)
        If AR(i, 1) = iLookup Then .Add AR(i, 3)
    Next i
   
    GRADES = Join(.ToArray, ", ")
End With

End Function
Here is what I have...
1603730687731.png

=IF(C2=1,"2 LT",IF(C2=2,"1 LT",IF(C2=3,"Capt",IF(C2=4,"Maj",IF(C2=5,"Lt Col",IF(C2=6,"Col",IF(C2=7,"Brig Gen",IF(C2=8,"Maj Gen",IF(C2=9,"Lt Gen",IF(C2=10,"Gen",IF(C2=31,"E1",IF(C2=32,"E2",IF(C2=33,"E3",IF(C2=34,"E4",IF(C2=35,"E5",IF(C2=36,"E6",IF(C2=37,"E7",IF(C2=38,"E8",IF(C2=39,"E9",IF(C2="","CIV"))))))))))))))))))))

And it's working now, not sure why it didn't work on my original worksheet
 

Attachments

  • 1603730687833.png
    1603730687833.png
    6.3 KB · Views: 9
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,757
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top