|
Aawulf -> How to sort leaders in WitPExcel (10/5/2005 12:59:25 AM)
|
Objective:
To keep the leaders as they are already assigned in a scenerio, but still want to be able to quickly see who is assigned where and to verify that you don't have leaders with multiple assignments.
Obstacle:
In many scenerios, the leaders are not thoroughly sorted by nationality or even by for which side they fight. Because of the size of the data files, the choice to spend less time loading and saving and the goal of keeping the data managable, the original data files are read and written to in piecemeal according to which data the user is editing.
In hindsight, I should created two sheets for Leaders; one for the Allies and one for the Japs. At the time, I chose not to because I wanted to leave the flexibility to the user to decide how many slots to allocate to either side.
In order for the "Find Leaders" feature to work, the leaders have to be sorted by side, with the Japs using the 1-7145 slots and the Allies using the 7146-19999 slots. It is easy enough to just sort the leaders ina scenerio by Nationality to achieve this mix, but any sorting of the leaders alters their assignments as those are determined on the HQ, TF, LCU, Ship and Air Group sheets.
Solution:
For those who have no aspirations about manipulating the minute details of the assignments of the 19999 leaders', there is a solution where one can maintain the leaders' assignments while still being able to make use of the "Find Leaders" feature found in WitPExcel.
1. On the Leader sheet, paste the values 1-19999 on the unused blue column "T" (cells T5:T20003) on the far right of the sheet, starting on row 5 (Leader Slot #1). These cells should mirror column "A" when you do this. DO NOT use a reference formula to achieve this; either copy and paste the values or use the series function of Excel.
2. Pick an unused column on the HQ sheet such as an unused "User Field" or even the blue column "AC" and type into the first slot cell (row 5) =VLOOKUP(N5,Leaders!$T$5:$U$20003,20)
I entered =VLOOKUP(N5,Leaders!$T$5:$U$20003,20) in cell "W5".
3. Copy the cell with the formula you just entered and paste it to all of the slots in that column.
I pasted it to "W6:W219".
4. Repeat steps #2 and #3 for each of the following sheets, using the corresponding formulae in the row 5 cells for pasting down:
- TF =VLOOKUP(M5,Leaders!$T$5:$U$20003,20)
- Jap LCUs =VLOOKUP(o5,Leaders!$T$5:$U$20003,20)
- Ally LCUs =VLOOKUP(o5,Leaders!$T$5:$U$20003,20)
- Jap Air Grps =VLOOKUP(E5,Leaders!$T$5:$U$20003,20)
- Ally Air Grps =VLOOKUP(E5,Leaders!$T$5:$U$20003,20)
- Jap Ships =VLOOKUP(N5,Leaders!$T$5:$U$20003,20)
- Ally Ships =VLOOKUP(N5,Leaders!$T$5:$U$20003,20)
5. Go to cell "U5" on the Leaders sheet and unhide that column. Do this exact same to this column as you to it's neighboring column "T" in step #1.
6. Sort the leaders (Leaders!$b$5:$T$20003) to your heart's content.
7. Select Leaders!$T$5:$U$20003 and sort that selection by the "T" column, ascending.
9. On each of the above sheets copy and paste the values from the columns where you entered the formulae to the respective columns where the leaders are assigned. Be sure to paste them as values and not formulae.
10. Repeat steps #1 and #5 (no need to unhide the column now) so that you can sort more later and only have to do steps #6-#9 to retain your ongoing leaders assignments.
*This should work. I must admit that I haven't tested it as yet. If any Excel guru's see a flaw in my logic or if it fails in practice, then please let me know on this forum. Since I am only able to grab a few minutes a day for leisure, I only come to the forums every few days.
---OR---
I will include an optional macro button that resets the leader assignments after sorting in the next revision and you could wait it out until then.
|
|
|
|
