General.Tools- Excel Addin

In most cases when you work with raw data it is a good idea to use this features before using other tools. This method removes extra-spaces from all cells from the Active Sheet or only from the Selected Cells:

• If you selected a Range of cells on the Active sheet and clicked the Trim button, the method will be applied only to that range:


• If you selected only one cell, the method will be applied to all the cells from Active sheet:

• If you selected anything else than a range (ex: a chart) and clicked the Trim button, you will receive an information message and nothing will happen:

• It’s used when you need to delete all rows from the Active Worksheet that contain in the column of the current selection the same string or number as in the current selection.
• One example of situation when you should use it is when you have a worksheet from a System (ex: SAP) exported with the header of each page in your work file:

• To delete the “extra-headers”, select a cell in the first row from where you want to start the deletion and press the button from the Tab General.Tools
• The macro will start running and you can see the process into Excel’s Status Bar:

• After it completes deleting all the rows you receive confirmation message with the number of deleted rows and your file has no repeating headers in it’s rows:

This feature does exactly what the name suggests: deletes all the rows from Activesheet that have no data in any of the columns.

• It works the same as the previos tool (Delete Rows as Selection): you can see the progress in the status bar and when it finishes you receive a confirmation message. In our example, the result would be the following:

• Until now we have 3 options which can be choosen related to faster processing of Unique Values. Click on the arrow next to the combobox in order display them.

1. All in one column:
• This macro takes all the values from the selected range (can be one column or more columns !) and outputs a list only with the unique values in one column. The user selects the first cell of the output range with the help of an Input Box:

Result:

• If we select data from more columns, this feature will not consider the dependence between the column and will only output in the selected column the unique values from selection:

2. More Columns:
• This feature works similar to Excel’s Remove Duplicates feature: you can select more columns and output the unique values considering the values from all selected columns. It essentially checks if the combination of values from the selected columns is unique for each row in the selection range ! For example if we select the same range as in our last example (B2:C37) we will obtain a different result that with the “All in one column” feature:

* The advantage of using this feature instead of Excel’s Remove Duplicates default function is the speed and the fact that with General Tools’s feature you are not affecting the original data. With the Excel’s function you have more steps to follow if you want to accomplish this: copy the original values, paste them in other location, confirm:
3. Sumif 1 Criteria:
• This feature allow you to perform a very quick sumif with one criteria. Consider the following example:

• This feature gives you the possibility to very quick convert to number a text string which has a different decimal separator than the one for numbers, has blank spaces between it’s digits or/and has the minus sign at the end.

• Consider the following example which was imported to Excel from a SAP image. If you try to calculate using these amounts you are not able, since the values are strings which only look like amounts. The comma is actually in the string and should be deleted, the minus sign must be removed from the end of the string and the resulted string multiplied with -1 in case of negative values 😉

Don’t worry, you don’t have to do this !

Just, select the Range(C2:C27) and press “Numeric Selection” if you want to ask General Tools Add-in to convert very quick those strings to numeric values for you!

In case of non-numeric values, the code ignores then and converts to number only the suitable values from the selection.

• This tool converts very quick strings which look like dates to actual dates.

• Many times (usually when you import data from an information systems), you have in Excel the column with the date in a format which is not recognized by Excel as a Date. Because of this, you cannot use the dates in formulas.

• For example, consider the following example which is a sample of dates import from SAP:

• In columns C, D, E you have formulas for getting the Year, Month and Day of each Date, but you get an error because the strings from column B are not actually Dates for your Excel. They just look like dates !

• To solve this issue, you can use text formulas to take the Year: right(B2,4), Day: Left(B2,2) and Month: Mid(B2,4,2). You put those values in 3 separated columns and in a 4th column you use the Excel function Date(year,month, day):

• Don’t worry, you don’t have to know to do this or spend time with it !
• Just, select any cell from the column with the dates and press “Date Column” if you want to ask General Tools Add-in to convert very quick those strings to dates for you!
• As you can see in the print screen from the next page, the values in column B, are now recognized as Dates by Excel:

This feature can handle different text formats that look like date, but not all of them. On request, it can be improved to transform to dates more formats.

• This is a simple feature which allows you to change the Case of the selected text. You can choose an option from the four options available at the moment:

1. Proper Case:
• Converts the text within selected Range to Proper Case (the first letters of each word in Upper Case):

2. Upper Case:
• Converts the text within selected Range to Upper Case:

3. Lower Case:
• Converts the text within selected Range to Lower Case
4. First Upper Case:
• Converts only the first letter of the string to Upper Case and all other letters to Lower Case

• This feature basically allows you to insert formulas in a Range of cells without adding a new column or calculating the results in another Range. It modifies the existing data according to the formula that you insert.

• Let’s see how this feature works by testing it on the amounts which we used before also for the “Numeric Selection” and “Sumif 1 Criteria” features.
• Suppose that in F2 and F3 we have the amounts without the VAT Rate and we need the amounts including VAT (20%). We can select as “Applicable Range” the Range(“F2:F3”) and write the following formula:

We obtain:

• As you can see in the above image, the range now contains the formula. If we want to have only the values we can choose the option 2: “As Values” :

• If our data is filtered, we can choose the option “Apply to visible cells only” and the macro will apply the formula only to the visible cells:

• Only amounts from year 2014 (as filtered) where modified by the inserted formula:

• If we insert a bad formula, the code will not work:

• If at “Applicable Range” we input something else than a Range address, the box will get red and we receive a message about the invalid Range input:

• If we apply a valid formula to a valid range which contains something else than numeric values we have 2 options:
     o Mark those cells with color red (default option):

     o Don’t highlight errors (will do nothing to the cells with no numeric values, but will apply the inserted formula to the numeric cells within the selected range):