11 Replies - 525 Views - Last Post: 20 November 2017 - 10:51 AM

#1 DADDYCARDONA   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 214
  • Joined: 04-July 10

Not Really Access but Excel

Posted 20 November 2017 - 09:52 AM

Hi I am trying to do something I had done many years ago, like in 2008-2010 time frame, but with Excel not Access. What I am trying to do is two things and I honestly did not know how to search for it because I searched 40 times and wrong answers or not really any solution. Okay first thing is I want to be able to filter data information by rank, military rank not scoring rank, but don't know how to do it. So for instance for Captain is a O-3 and 1st LT is a O-2 and then you can go down to like MGSGT and SGTMAJ which hold the same rank as E-9 and E-8 also have MSGT and 1STSGT which are the same rank, however a SGTMAJ would be higher ranking for he is in charge of the whole unit and 1STSGT is similar to SGTMAJ. How can I make a custom script or use a formula so I can be able to make it like O-10. That is part one, part two would be if I have a Pivot table with it and click on a section, how can I keep the same integrity of rank structure then by last name and first name status?

I have a pivot table spreadsheet, a main spreadsheet and a rank spreadsheet which in the rank spreadsheet I have the order listed as high up as captain as 1 and then those other ranks 1st and 2nd lt 2, 3 and then the enlisted up to MSGT as 4 etc.. I am trying to incorporate those other ranks in there as well but trying to make it easy for myself by starting smaller.

Is This A Good Question/Topic? 0
  • +

Replies To: Not Really Access but Excel

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,478
  • Joined: 12-June 08

Re: Not Really Access but Excel

Posted 20 November 2017 - 09:57 AM

I would figure you could use the O-# and E-# system to sort off.. that or give them all numerical values in descending order and sort off that column.. and then hide it.
Was This Post Helpful? 0
  • +
  • -

#3 DADDYCARDONA   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 214
  • Joined: 04-July 10

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:01 AM

I was hoping I could do something like I asked so many years ago for access to be able to make two separate spreadheets in the same book and use some sort of relational comparison to sort them. However, that was access and I don't know if Excel can do the same thing. How would I go about doing your first suggestions?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,478
  • Joined: 12-June 08

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:05 AM

I must not be following. Make a list of ranks. Next to that list start at the top and put one, the following 2, etc. Now you have your list in some sort of sortable order.
Was This Post Helpful? 0
  • +
  • -

#5 DADDYCARDONA   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 214
  • Joined: 04-July 10

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:09 AM

So make the list in the next column right next to the column where the rank is at and give them a sorting value in the same column that part is what I am confused on at this moment

Rank | Sorted Rank | Sorted Rank Position
Cpl  | capt        |   1


Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,478
  • Joined: 12-June 08

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:12 AM

Sure.. though typically I have the key column (this being a number) first.
Was This Post Helpful? 0
  • +
  • -

#7 DADDYCARDONA   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 214
  • Joined: 04-July 10

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:17 AM

Can you show me an example? Even if it is a link to a video or site that demonstrates that. I have not been dealing with Excel in a while.

This post has been edited by DADDYCARDONA: 20 November 2017 - 10:20 AM

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,478
  • Joined: 12-June 08

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:22 AM

Ah.. ok. Fill in abbreviations and words as needed.
Sorted Rank Position 	| Rank 	| Sorted Rank 	| 
1 			| Cpl	| capt        	|
2			| AAA	| AAaa		|
3			| BBB	| Babbas	|
4			| CCC	| asdf1		|
5			| Dsdf	| rwer		|

Was This Post Helpful? 0
  • +
  • -

#9 DADDYCARDONA   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 214
  • Joined: 04-July 10

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:30 AM

So you would hid column A n B after sorting? if so how would you keep it sorted after you create a pivot table and then double click on a cell that you want to look at the personal that fit that criteria

I am not going to lie I am still confused on how this works.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,478
  • Joined: 12-June 08

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:35 AM

Sure.. hide which ever you want.

I am not certain what why you would have a pivot table for listing people's names or what the pivot table is supposed to be showing.
Was This Post Helpful? 0
  • +
  • -

#11 DADDYCARDONA   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 214
  • Joined: 04-July 10

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:46 AM

Okay I am creating a spread sheet. My pivot table would show need or good. I was a training SNCO so meaning I was in charge of making sure the marines went to the training or was put on the training unless some unforeseen incident like they got pregnant or broke a leg. So in my pivot table I would show the types of training like Swim Qual, Rifle Qual, etc.. then I would make it so they only showed need or good on that table next to the type of training there is. So if my boss needed to know or needed me to make a fast spreadsheet with only the names that needed to go somewhere and not have to filter it him or herself, because that is to much for them lol, then all they needed to do is double click need and it does it for them. So I am trying to make sure if I double clicked would it still be in the same format of ranks and then last names and when it open. Also I wanted to make sure that sorting or filtering this would be easy
Was This Post Helpful? 0
  • +
  • -

#12 DADDYCARDONA   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 214
  • Joined: 04-July 10

Re: Not Really Access but Excel

Posted 20 November 2017 - 10:51 AM

So trying to use this filter really did not work out for me so that is why I am asking about how to do this here.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1