mixlUtilities






 


mightymacros mixlUtilities Main Menu


 


The main menu is installed on Excel's cell shortcut menu


 



      If this happens simply right click outside of the list or query to activate the utilities menu 


 




 


 


 


 







Product Warning






 


Product Warning:


 


Although macros are extremely helpful and useful in saving time it is highly 


recommended that you make fresh backups of your files before executing them.


 


Some of the tools here may not run until you have backed up your file, so please


 take due care when you are editing and changing data as macros may not behave


 as you might expect them to. 


 


It may take some time for you to become familiar with them, so do try to fully


 understand their action before use.It's always best to trial them out first on 


a test sheet and make sure that you understand what's happening before


 applying the macro to important data files.



  









Installation






 


Installing and upgrading an add-in in Excel 2007 and 2010


 


 


  • In Excel 2007 select the office button  in Excel to access Excel Options
    • 

  • In Excel 2010  go to the File menu tab the select options to open The Excel Options menu
    • 

  • You can also access the add-ins folder directly from the developers tab in 2010
    • 



 




 


 


 


 


 


 


 


 




 


To re-install or upgrade to a newer version 


  1. Uncheck the old version first then click 'OK' and close the Add-ins menu


  2. Re-open the Add-ins menu


  3. Select the Browse button to access AddIns folder


  4. Either delete the old version  or paste and replace older version then select 'OK'


  5. You will need to re-enter your licence key to activate


       


 


For New installation paste the add-in into the Addins folder and click 'OK'




 


 




 


 





  









Short-cuts to Excel files






 


Shortcuts to Excel Files (mixlShortcuts)


 




 




 
The initial step is to create a new group name which will provide fast access to your Excel files.



You can also opt to choose to create a new name for your file short-cut to be displayed 

in the list without affecting the orginal file name.





Creating short-cuts to a File:

a. To set up your file group names, select the 'New Group' button to open the 

'Group Names' menu 

b. Go back to the 'Set up form' then select your group name from the option list

c. Select '(New)' in the list box then enter a 'Short-cut Name' for your file

d. Click the insert path button to select the path to your file

e. Following the file selection a message box will appear to prompt you to save the path

f  When done backup your short-cuts just in case, so you can easily restore them. 









 











Short-cuts to folders






 


Shortcuts to folders


 


Note: short-cuts to folders is not included in the free version


 


This is a very simple yet handy tool to store shortcuts to frequently used folders 


where you can open any Excel file selected within the chosen folder by simply 


double clicking the folder path or name in list list box::


 


Access short-cuts to Folders


 


  • Open Short-cuts to Excel files from the main Menu
    • 



 




 


 


Creating and removing folder short-cuts:


 


  • To create a new folder, select  'Add Folder'  button this will open a folder picker dialog 
    • 



         where you can select your folder


 


  • To remove a folder select "Remove from list button'
    • 

    

  • To open a folder, double click the item in the list. A file picker dialog 
    • 



        will then open where you can select your file from the folder


 





  





  









Cell edit






 


Edit cells macro


 


This macro is used to re-arrange values within a cell;


 


It is useful for:


 


  • Inserting words and text
    • 

  • Moving characters and their positions within cells
    • 

  • Deleting characters from fixed positions within a cell
    • 

    



Warning:


The 'Undo' button will only roll back once, so back-up your data before running this macro


 


Insert Example:


 




 


 


Example below uses the insert menu to insert the word top after the word base.


Please note, the default auto backup is turned on so you should backup your


file before using this macro.


 


TIP: Use the space bar to insert a space either before or after a word


 




 


 


Delete Example:


 




 


Example below uses the 'Delete other option' to remove the words on the right


of the word 'mountain.'


 





  









Cell tracker






 


Cell Tracker / Color cell macro


 


Cell tracker when activated simply colors a cell when double clicked


and then moves to an adjacent cell in the selected direction.


 


The tool can be used for any purpose to color cells. It's one that I find useful as it


can help to maintain postion while working through records on a sheet without 


needing to access Excel's menu ribbon.


 




 


The add-in works with any sheet double click event on the selected cell and you will  need


to click the activate button to initiate this action


 


You can select the cell infill color and move direction at any time


either before or during activation.


 


 


Once activated the toggle button displays 'Click off'


This means that you can now color cells


 




 


To deactivate this function on the sheet simply click the form button


and Color cells function will be turned off as below:


 





  









Protect and hide sheets






 


Protect and hide sheets:


 




 




 


 


 




 


 




 


 


 


 


 


 


 


 


 


 


 


 


 


 



  









Multi-find






 


Multi-find Macro


 


 


Search for multiple items in a list or table, highlight their cell interior background colour


to identify the items that have been found. Use Excel's filter by colour to display and filter the items


 




 




 


 




 


 


 


 


 


 


 


 


 


 


 


 


 


 



  









Find header






 


 


Find Header Macro (mixlNavigator)


 


This tool will help you to navigate across large spreadsheets without 


loosing your place


 


 




 


 


Whilst the Guide is in 'Static mode you can grab and adjust the line spacing 


to help focus your attention on any area captured between the lines.


 


Once the guide is in place you can skip across your page from 


header to header without fear of losing your place


 


The [Lists...]  This function is also used with the multi-filter add-in and uses 


a stored list. 


This will help you to store a special list of select headers which could save


 you time by displaying only headers in the list that are of interest.


 


You can can also select and add headers one by one to make your special 


list and save it as a stored list.


 





  









Multi-lookup






 


The multi-lookup macro (mixlMultilookup)


 


This tool is not an ordinary lookup, although it can function as one.


It's primary use is to find all values associated with the look-up item.


 


Example:


If you look up item XYZ  and XYZ is sold in Australia and the US, a normal lookup


will only return a single result  'Australia' whereas the multi-lookup will return: Australia,US


 


Warning:


Do not activate Overwrite unless you are sure and


have backed up and saved your work.


 


If you select the correct header row in the returning list you should 


not get an overwrite warning if the column for the returning values


is empty.


 


 


Multi-lookup Types:


       


1. Return all including duplicates.  


       This is not very efficient and it accommodates lookups of duplicate 


       values in the return file or list. This is similar to a standard lookup 


       function except it includes the capability of returning more that one


       value.


       


2. Do not return duplicates


       This function is more efficient and will run faster and is best suited for lookups


       where the returning list comprises only unique and non repeating values


        If the returning list contains duplicate keys or values then there will be gaps


       in the data returned on the duplicate items, so it is best to remove 


       duplicate records prior to selecting this option.


 




 


 





  









MultiHeaderLookup






Multi-column header lookup macro


The multi-header lookup tool performs multiple lookups across 


a worksheet.by searching for values specified within a key column


 range and then matching column headers in the  return worksheet 


to populate the cells.


As well as specifying the search column where your common values


 are found. You will need to specify the row numbers for the column


 headers  by selecting the search header row and the return header 


row number                                                 


                                                 


There is an auto-backup function, just in case you                                                 


select the wrong return sheet.                                                 


                                                 


The multi-column look-up is a very powerful tool that can save 


a lot of time when working with large worksheets


 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 


                                                 



  









Multi-find and replace






 


Multi-find and Replace macro


 


Note: keep form open and do not close if unsure of the operation


 


You can always reverse your action whilst the form is open by selecting the


 'Reverse (Undo)' button then 'Apply'


 


In order to load the list box you will need to have find and replace values in adjecent cells.


 


  • As in the Example  below 'Test 1' is to be replaced by 'Test A'
    • 

  • To add to the listbox: only select the first column values, the adjacent column values will 
    • 



       be automatically added to the list


 


 


 


 





  









Multi-filter






 


The  Multi-filter macro


 


The Multi-filter macro is ideal for filtering a list of items, which will save 


you time looking for multiple items using Excel's filter


where you need to locate then check and uncheck boxes. 


This macro will help you to rapidly focus on your list of items filtered within 


a single column. The macro tool also can store your commonly used filter


 criteria which is useful for repeatative tasks: 


 




 


Applying the Filter:


 


 


 


 


 


Selecting and deselecting list items when applying the filter


 


  • Drill into your listed items by double clicking on any item within
    • 



      the listbox to apply the filter on that single item.


  • Multi selection is available by holding down shift or Ctrl key and 
    • 



      using the mouse or up/down arrow keys


  • To clear the selection click the 'Deselect' button
    • 

  • Uncheck 'Include list items in filter' checkbox  to apply a filter that 
    • 



      excludes all the items within the listbox




 



  









Goto sheet






 




Go to Workbook / Sheet macro:



Is a navigation tool that can also be used to rearrange the order of the worksheets



The Goto sheet macro displays all open workbooks. 



Select a workbook to go to that workbook. 

The workbook sheets will then be displayed in the list below:		



Select a sheet in the list to go to the selected sheet












  









Clean trim and convert to text






 


Clean, trim and convert to text macro


 


 


This macro clean and trim hidden characters which also assists lookup operations


 providing a consisant data-type for for lookup operation.


 


This is a simple yet very handy tool that can quickly and easly convert


 selected values to text format


 





  









Delete empty Rows






 


Delete empty rows macro


 


This again is a simple tool that works on the selected sheet's used range and removes


any blank rows of cells.


 





  









Insert rows on change






 


Insert Rows on Change macro:       


 


Helps to visually group and separate your data


 


Use the delete empty rows macro to remove the inseted row


                            


Pre-sort your data values into groups                            


Then use the  insert rows macro to insert rows on change:   


 


  1. Select the column where the change occurs
    2. 

  2. Header row for your data
    3. 

  3. The insert row height to separate your data
    4. 

  4. The number of rows to inset
    5. 

  5. The row colour
    6. 



                             




 


After applying Insert rows on change:


 


                                   



  









Show duplictes






 


Show Duplicates macro


 


If you just don't like removing duplicates without knowing what they are.


 


This macro will only highlight the item when it is repeated


 


The 'Show all repeating values ' macro will highlight every instance of an item that is repeated.


 




 



  









Show all repeating values






 


Show all repeating values:


 


Shows all the duplicate values within a range selected on a worksheet


 


 





  









Misc tools






 


Misc macro tools


 


 


Change case


Paste Special - values


Rounding macro


Remove spaces


Insert Bookmarks


Remove Hyperlinks


Multiple Hyperlinks


Store and insert text







Change case






 


Change case macro


 


Change the case of text:


 


  1. Upper case
    2. 

  2. Lower Case
    3. 

  3. Proper case
    4. 



 


  • Select the range of data 
    • 

  • Select the case option 
    • 

  • Select the 'OK' button to change
    • 

    





 


 


 


 



  









Paste Special - values






 


Space Special Values macro


 


This formt acts as a floating button menu and can remain open until closed


 


Older versions of Excel used to have floating command bars that were extremely useful and versatile.


 


Paste Special values will be available with a simple click.


 


 




 



  









Rounding macro






 


The Rouding Macro


 


This macro rounds numbers within their current cell location, whilst maintaing


 the original cell value


 


  1. Select the number of decimal places to round
    2. 

  2. Select the rounding method
    3. 

  3. Select the range
    4. 



 


Clearing the rounding formula returns the original values


 




 


 


 



  









Remove spaces






 


Remove Spaces


 


Removes spaces all spaces and gaps within cells that includes:


  • trailing spaces
    • 

  • Gaps between words or characters
    • 

  • Leading spaces or gaps in cells
    • 

    






  









Insert Bookmarks






 


Insert Bookmarks / Sort Sheets


 


This macro tool can be used either to:


  • Create an linked index or table of contents to sheets within a workbook
    • 

  • Sort or move selected sheets within a workbook
    • 

    

    



Methods and options available for this tool:


 


When the form is activated the sheet names in the active worbook will load into the listbox.


 


To insert multiple bookmarks


  • multi-select or select more than one item within the list by either by:
    • 

  • Selecting and dragging
    • 

  • or using a combination of either the  shift/ ctrl key and up 
    • 



      or down arrow key


 


Select a single custom link


  • to create a single bookmark
    • 

  • If you are on sheet5 and you want a bookmark to sheet1 then 
    • 



     select sheet1 in the list and click Inset Link.


 


Creating a link back to your contents table or index:


  • Option 1 ('Create Link - Cell address 'A1 on all sheets)
    • 

  • Inserts a link on all selected sheets in range "A1"  if blank
    • 



 


Alternate Option:


  • Allow Insert row if cell 'A1' contains data
    • 

  • Both options must be checked
    • 



 


Sorting Sheets


  • Sort sheets button will sort all sheets in ascending order
    • 

  • The spin buttons on the top right will allow you to move one sheet at a time
    • 



 


 


 




 



  









Remove Hyperlinks






 


Remove Hyperlinks


 


This macro function removes unwanted links form your worksheet


 


On activation following form appears


 


  • Select the range containing the hyperlinks
    • 

  • Select 'OK' to remove
    • 

    






  









Multiple Hyperlinks






 


Multiple Hyperlinks


 


This macro function creates more than one Hyperlink by selecting multiple link addresses


 


On activation following form appears


 


  • Select the range containing the addresses
    • 

  • Select 'OK' to to create Hyperlinks
    • 

    



 





  









Store and insert text






 


Insert Text Macro:


 






 


 


 




 



  





  









Date tools






 


Date tools


 


Convert yyyymmdd date


Convert mm/dd/yy to dd/mm/yy


Convert to text date mmm-yy


Convert text mmm-yy to date


Convert text mmm-yy to date


Show weekends


Convert date to week number







Convert yyyymmdd date






 


Convert  yyyymmdd to date format


 


Converts 20130722 into 22/07/2013


 


Select the range the press 'OK' to convert


 




 



  









Convert mm/dd/yy to dd/mm/yy






 


Convert mm/dd/yy to dd/mm/yy


 


Converts 12/28/2013 into 28/12/2013


 


Select the range the press 'OK' to convert


 





  









Convert string date dd.mm.yyyy






 


Convert string date dd.mm.yyyy to date format


 


Converts 28.12.2013 into 28/12/2013


 


Select the range the press 'OK' to convert


 





  









Convert to text date mmm-yy






 


Convert date to a text format mmm-yy


 


Converts 28.12.2013 into Dec-13 


 


For use in charting by month when you don't want excel to pick up the date


 


Select the range the press 'OK' to convert


 


 





  









Convert text mmm-yy to date






 


Convert text format mmm-yy to a date


 


Reverses text mmm-yy into a date format for 1st of month


 



  









Show weekends






 


Show weekends


 


Changes font colour of weekend dates to red.


 


Select the range then OK


 


 





  









Convert date to week number






 


Convert date to a week number


 


Changes date format to a numeric  value representing the week number


Warning there is no undo 


 





  





  









Save copy






 


Save Copy macro


 


When you try to save a copy using 'Save As' , the current file you are working 


on is not saved and is replaced as the 'Save As' version. This can be annoying


 if you just want to save a backup copy and there are no other options readily at hand.


 


The Save Copy macro opens a 'Save Copy' dialog and allows you make a backup copy


while you are working on the open file.. 


 


 





  









Uninstalling






 


Uninstalling an add-in in Excel 2007 and 2010


 


 


  • In Excel 2007 select the office button  in Excel to access Excel Options
    • 

  • In Excel 2010  go to the File menu tab the select options to open The Excel Options menu
    • 

  • You can also access the add-ins folder directly from the developers tab in 2010
    •