mixlUtilities

mixlUtilities

mightymacros mixlUtilities Main Menu

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

To access this menu, right click any cell on the open worksheet

Please note mixlUtilities may be hidden if you right click over a table or query list

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

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 a short-cut to a File:

a. Set up your file group categories by selecting the 'New Group' button to open the 'Change Group Names' menu
then start creating your group names
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'
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

Creating 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::

1, To Access shortcuts to Folders

Open Shortcuts to Excel files from the main Menu

2. Creating and removing Shortcuts to Folders:

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

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

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)

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.

The multi-lookup will search and return all values relating to the lookup item or key

There are two types of multi lookups available here:



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 associated with the lookup item.



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: The multi Column Header Lookup macro(multiHeaderLookup)

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 the return worksheet to populate the cells

As well as specifying the search culumn 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

Store and insert text: Insert Text Macro:

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:

Select the Column where the change occurs
Header row for your data
The insert row height to separate your data
The number of rows to inset
The row colour

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

Some miscellaneous tools to edit values in cells:

Change case

Cell edit

Paste Special - values

Rounding macro

Remove spaces

Change case

Change case macro

Change the csae of text to

Upper case
Lower Case
Proper case

Select the text case option from thr drop box then select the 'OK' button

Select the range of data to convert, then select the 'OK' button

Cell edit

Edit cell macro

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

It is useful for:

Inserting prefixes and suffixes in text
Moving characters and their positions within cells
Deleting characters from fixed positions within a cell

Example: Using the filter function then adding a suffix to a visible range of cells:

Select the option to insert text
Enter the position from the left
Enter the values to inset within the cell

Paste Special - values: Space Special Values macro

This is a simple form with a button that 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.

This more direct and will save you from the repetative operation of opening or selecting paste special values

from the shortcut menu

Paste Special values will be available with a single click.

Rounding macro

The Rouding Macro

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

Select the number of Decimal places to round
Select the rounding method
Select the range

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

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

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