PDA

View Full Version : Easy Way to Find Out Who is in Your Army?



Nubiesan
10-18-2010, 09:09 PM
In an effort to keep my gifting fair and to be sure that I have invited friends to join my army, I would like to have a list of all army members so I can check them off when I send gifts.

Is there a way to download a list to Excel?


Barring that, is there a way to sort alphabetically?

Michael Rhew
10-19-2010, 01:04 AM
There is no EASY way to get a list. I have a method that I use to periodically update my list, using Excel spreadsheet.

I use the View Army button and look at each page of 25 army members. I click the mouse pointer just to the left of the topmost member icon, then scroll down and while pressing the shift key I click just under the bottom right border of the lowest member on the page. This should result in all members being highlighted. COPY.

In an open Excel spreadsheet, use the PASTE SPECIAL feature and select the UNICODE TEXT option. This will paste all of the info for up to 25 members from that page. Place the next open cell immediately below the last cell that was pasted into that worksheet.

Repeat for all pages of your army.

Now you need to use the transpose method to convert the 5 rows for each member, into a single row of five columns. I created a MACRO to do this, as it is quite tedious to do several hundred of these manually. Once all of the members data has been transposed, you can eliminate all but the second column of data, and then do a sort on that column to alphabetize the data.

Michael Rhew
10-19-2010, 04:54 PM
Pst25 will perform a Paste Special as Unicode Text of a 25 member page from the Army View, and end on the cell just below the last cell of the column paste, allowing quick entry of army member data - invoke with CTRL P


Sub Pst25()
'
' Pst25 Macro
' Macro recorded 9/19/2010 by Mike
'
' Keyboard Shortcut: Ctrl+p
'
ActiveCell.Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
ActiveWindow.SmallScroll Down:=115
ActiveCell.Offset(125, 0).Range("A1").Select
End Sub

---------------------------------------------------------------

TransP5 will convert a 5 row, single column block into a single row, 5 column block located 2 columns to the right of the original cell - invoke with CTRL T


Sub TransP5()
'
' TransP5 Macro
' Macro recorded 5/23/2010 by Mike
'
' Keyboard Shortcut: Ctrl+t
'
ActiveCell.Range("A1:A5").Select
Selection.Copy
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.Offset(5, -2).Range("A1").Select
End Sub

Nubiesan
10-20-2010, 01:00 AM
Thanks -- will give that a try!!

ajoin007
12-14-2010, 02:50 PM
hmmm i am a TOTAL noob in making macros but what my problems are
the copy/paste special works excvellent but when i do that i get the name of the perosn, his name + nickname in "" and then his level and if he's a commander.

Now what i would like is to have all my army alphabetically in excel and i just want to keep his name and his nickname. I don't need all the rest. Is there a fast way of deleting all the open spaces and to keep just his real name + nickname in column a and delete all the other stuff.

What i am trying to get is a list of all my army members alphabetically lined in column A, in Column B i would put the date when i last received a gift from that person and in column C i would put the date of the last gift i sent. Then i would work in colour codes so i can easely see who are the guildmembers and who is active and who is not.

Is it possible to make a macro for that and if yes how do i create a macro because i'm a real noob :-)

Michael Rhew
12-14-2010, 04:19 PM
It is not necessary to create a macro to delete the unwanted columns. In the header at the top where the column identifiers are ( A, B, C, etc) if you want to delete the entire column, just left mouse click on that header character (A for example), then right mouse click and select delete from the drop down menu. The entire column will disappear. Do that for each column that you do not want to keep.