Change military grades into rank

air force guy

New Member
Joined
Apr 17, 2010
Messages
23
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,926
Office Version
  1. 2019
Platform
  1. Windows
You may want to use Vlookup Function.

 

air force guy

New Member
Joined
Apr 17, 2010
Messages
23
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,423
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

air force guy

New Member
Joined
Apr 17, 2010
Messages
23

ADVERTISEMENT

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
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,860
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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))),"")
 

air force guy

New Member
Joined
Apr 17, 2010
Messages
23

ADVERTISEMENT

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
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,860
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,860
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Or if you have Office 365 you can use this formula.

=TEXTJOIN(", ",TRUE,FILTER($C$2:$C$23,$A$2:$A$23=G2))
 

air force guy

New Member
Joined
Apr 17, 2010
Messages
23
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: 5

Watch MrExcel Video

Forum statistics

Threads
1,113,831
Messages
5,544,561
Members
410,620
Latest member
Barque
Top