Need to automate this manual spreadsheet -- pls help

Hello,
Here's the situation, I am building a massive spreadsheet broken out by a number of different rows & columns. It's a military spreadsheet broken out by service component. In the first column I have the service (ARMY, NAVY, AIRFORCE, MARINES). In the next several columns I have Year of Service, Actual Year, Rank, and Pay Amount. I just finished the AIRFORCE portion filling in 320 line items. Now I'm slugging through the Army data, and some of "Pay Amounts" are the same for the same year group for the Air Force (Pay Amount is column (F).

I'd like to do some sort of formula that would first look in the service column (A), then look at the grade (E), year of service (C), and actual year (D) for the Airforce row, and then see if there's an ARMY row that matches exactly (except for column A of course), and then automatically put in the same "Pay Amount" in that row for the Army. I was manually linking up the cells, but surely there is a way to automate this. I would REALLY appeciate any help with this. I should then be able to modify the formula for the NAVY and MARINES when I get to them.

THANKS in advance...

My first question would be 'What are you going to do when Pay rates increase'?

You could set up a table, as either
a simple two column table where A held a code that was Rank+Years+Actual and column B was the rate and VLookUp was used to fill-in the Pay Amount,
or
as a matrix-style where column A held Rank and Row 1 held Years, and the pseudo 'VLookUp' was done via an Index with two Match statements
to look something like:
=INDEX(B11:D13,MATCH(C1,A11:A13,0),MATCH(D1,B10:D10,0))

Do these thoughts help you?

--

Originally Posted by kilo94

Hello,
Here's the situation, I am building a massive spreadsheet broken out by a number of different rows & columns. It's a military spreadsheet broken out by service component. In the first column I have the service (ARMY, NAVY, AIRFORCE, MARINES). In the next several columns I have Year of Service, Actual Year, Rank, and Pay Amount. I just finished the AIRFORCE portion filling in 320 line items. Now I'm slugging through the Army data, and some of "Pay Amounts" are the same for the same year group for the Air Force (Pay Amount is column (F).

I'd like to do some sort of formula that would first look in the service column (A), then look at the grade (E), year of service (C), and actual year (D) for the Airforce row, and then see if there's an ARMY row that matches exactly (except for column A of course), and then automatically put in the same "Pay Amount" in that row for the Army. I was manually linking up the cells, but surely there is a way to automate this. I would REALLY appeciate any help with this. I should then be able to modify the formula for the NAVY and MARINES when I get to them.

THANKS in advance...

Thanks for the reply, I think it will help alot!

I'll elaborate a bit more. I'm conducting a "retirement simulation" and the spreadsheet itself has more columns, they're just already automated based upon the (Air Force) AF data. Year of Service would be 1-20 annotating a typical service career. Based upon what year you're in will determine what grade/rank you are (I have already estimated time in grade to help calculate the rank factor). Then based upon the actual year will tell me which pay table to look in to find the actual monthly pay. Then I convert that to an annual amount (another column), then I calculate the pay gap (another column), then I inflate the dollars to the actual year (using OSD inflation indices linked up).

So now that the AF is done I started doing the Army and using the Data Filter I saw many of the columns are the same, but I can't figure out how to do some sort of If column A = AIRFORCE and Columns C, D, and E match up with a similar row where the column A = ARMY then I want it to post the value in Column F in AIRFORCE to Column F in ARMY. So using your suggestion below, I could do something like a match of the 3 AF columns w/ the 3 Army columns and then post the value of the 4th AF column into the Army column...is that right?

Sounds horrific.

You could write (or have written) some VBA code to do this, but the code could take longer to get right than the manual entries would take.

If you are confident in Excel:
and only if : - (otherwise do not read on)
Another thought, as this is a one-off (3 times?) assuming that you have only non-formula data in columns A to F, and that G & H onwards are not used. In cell H1 put a 1, then hold the CTRL key and drag this downwards to cover all of your data, this will represent the original Row number.

Select all data (click the block above Row 1 and left of column A) and Sort over column A, then re-sort over column B, C & D
This will leave column A as the next in sequence, thus a match on B-C-D will show column A in its correct order for that match. (Air followed by Arm followed by others)

In cell G1 put =F1
In cell G2 put
=IF(F2<>"",F2,IF(AND(B2=B1,C2=C1,D2=D1),G1,""))
and formula-fill that down to the end of your data.

This should copy all previous values for F into G, and should then copy from the previous row where B C & D match

Copy column G, and Paste, Special = Values back over itsself.

Then re-sort the data back over it's original sequence column H

After checking you can delete column F and H, leaving G as the new F

------

It looks a little messy, but you can easily check the progress at each point to see if the required result is being achieved, something a macro would not allow easily.

Of course, do this on a copy of your data, just in case.

----

Originally Posted by kilo94

Thanks for the reply, I think it will help alot!

I'll elaborate a bit more. I'm conducting a "retirement simulation" and the spreadsheet itself has more columns, they're just already automated based upon the (Air Force) AF data. Year of Service would be 1-20 annotating a typical service career. Based upon what year you're in will determine what grade/rank you are (I have already estimated time in grade to help calculate the rank factor). Then based upon the actual year will tell me which pay table to look in to find the actual monthly pay. Then I convert that to an annual amount (another column), then I calculate the pay gap (another column), then I inflate the dollars to the actual year (using OSD inflation indices linked up).

So now that the AF is done I started doing the Army and using the Data Filter I saw many of the columns are the same, but I can't figure out how to do some sort of If column A = AIRFORCE and Columns C, D, and E match up with a similar row where the column A = ARMY then I want it to post the value in Column F in AIRFORCE to Column F in ARMY. So using your suggestion below, I could do something like a match of the 3 AF columns w/ the 3 Army columns and then post the value of the 4th AF column into the Army column...is that right?

Outstanding, this automated much of the process.

I'm very familiar w/ excel, but very limited in VBA, so your approach worked perfectly. I'm taking VBA class in the fall, so next time I won't have to go through this...

Thanks again, I really appreciate it.

Good to see, and thanks for the response.

Originally Posted by kilo94

Outstanding, this automated much of the process.

I'm very familiar w/ excel, but very limited in VBA, so your approach worked perfectly. I'm taking VBA class in the fall, so next time I won't have to go through this...

Thanks again, I really appreciate it.