Page Comparison

...

Exporting Interactive Reports to Excel

Interactive Reports

1. In CMA on the left menu click on Interactive Report Icon
   
   
   
   

2. Select Membership > Click on the + sign to open the report options > select Member Address Labels – Avery

   
   

   
   

3. Set all the criteria’s to filter what members you want included in the export 
   
   A few important pieces of criteria to consider:

   1. Member Types –multi select the member types you want included
   2. Field – how many address lines to include
   3. Notification Types – defined by the club: what type of events and communication does the club
      send out
   4. Sort By - sort by name will sort Alpha Last Name. There are numerous other sort options, member
      number, zip code, etc…
   5. Display Name – this depends on how many name fields you use in the member file. Default will
      print the first, middle, last name of the member. The statement name is usually used as a more
      formal name such as Mr. John Smith & Mrs. Tina Smith.
      
      
      
      
      
4. Once all the criteria are set, then click View Report 
   
   

5. Click on the Export Icon > select Excel > Give it a File Name and save to the directory you want the file
   stored

Formatting Data in Excel

1. Open the File in Excel > top tool bar click Data
   
   

   

2. Notice Column A and C are not showing: expand both columns

3. Highlight column B > Cut and Paste to Column A

4. Highlight column A > Top toolbar Select Data > Text to Column

5. Check Delimited > Next

   
   

6. In the following screen check Tab and Other > click in the blank box next to Other> 
   hold the ALT Keydown while entering 010 from the key pad (this will not populate the box with the 010 this is a commandand the box will remain blank) *very important; this will not work using the numbers at the top of thekeyboard, you must use the numbers on the keypad only

   
   

   
   

7. You will notice in the Data preview box the address lines have moved over to columns > click Finish 

8. A box will appear > click OK
   
   
   
   

   1. The file at this point will still need some data manipulation before it is usable. Some members might have three address lines and some might have more.

   2. Before continuing scroll down and find the columns with the longest address and note the City/State/Zip Code column (city, state and zip are in the same cell at this point).
      
      
9. Highlight the entire spreadsheet by clicking on the Icon to the left of Column A 
   
   
10. Right click the mouse anywhere on the highlighted spreadsheet > Format Cells > click Alignment Tab > 
    
    

     

    
    

11. A few things to look for:

    1. Un-check Wrap text
    2. Un-check Merge cells
    3. Text direction and Vertical can be your preference
       
       
12. Click OK
    
    
13. The spreadsheet will still be highlighted and you should still be in Data > Adjust your column width and
    height >
    
    

14. click on Sort > Select the last Column containing city/state/zip code > sort on Values > select Order Z to A
    
    
     
    

    
    

15. Find the first line that the city and zip are not aligned to the above address lines > highlight all lines
    
    
16. containing the city/state/zip > right click Cut and Paste to the same columns as the City column above 
    
    
17. If a column contains a Country > Highlight the column Cut and Paste to 3 columns over *if you do not do
    this step the following steps will overwrite the country if not moved
    
    
18. Top Tool Bar click on the Data > Highlight the column containing the city/State/Zip Code > Select Text to Column
    
    

    

19. Check Delimited > Next

    
    

20. Un-check everything and check Comma only > Finish (this will move the City to a separate column)

21. Highlight the column containing the State/Zip Code you should still be on the Data tab > Select Text to
    Column

    
    

22. Check Delimited > Next

    
    
23. Un-check everything and check Space only > Finish (this will move the Zip Code to a separate column)
    
    
24. Delete the blank column > Shift Cells Left
    
    
    
    
    
25. Highlight the entire spreadsheet > click Sort > Select the Column containing the State > sort on Values >
    select Order A to Z (this will help find addresses in other countries or not entered correctly)
    
    
26. Once City, State, and Zip Code columns are aligned highlight the Zip Code Column > right click on the
    highlighted column > Format Cells > select the Number Tab > select Special > Zip Code (do not select Zip
    Code + 4)
    
    
    
    
27. Top Tool Bar click Home and highlight Zip Code column or All columns > click Align Text Left 
    
    

You are ready to use the data for mail merge

...