Translatum Greek Translation Forum

[spiros]

There are many macros available which will do the job in a variety of different ways. The only disadvantage is that they are very slow if you have many records.

The simplest way to go about it is to select the column you want and then select Date/Filter/Advanced filter and check the Unique records only option.

If you want to do more things with Excel I recommend the free ASAP Utilities Excel add-in which has automated in an extra menu a number of useful procedures. For example (just the submenu range)

Find / Replace in all sheets   

Search/find and/or replace in all sheets at once. Shows all matches in a box which allows you to walk through the results easily.
Easy to find text across all sheets. Also has the power to deal with special characters such as line feeds (alt+enter), carriage returns and tabs.

Move screen (place selection in top-left of screen)   
Move the screen. This will place your selection in the left-top of the screen)

Show selection in center of screen   
Place your selection in the center of your screen. Easy because you don't have to use the scrollbars.

Custom formula error message   
The custom formula error message makes it easy to display a custom message for formula errors. This means that in stead of e.g. #VALUE! You can have your own (more logical) error message displayed.

Paste Special (with combinations)   
Paste Special, with multiple options combined e.g. paste both values AND formatting at the same time

Paste values and formatting   
Paste your selection with both values & formatting at the same time
Convert cell's value to its formatted look   

Convert the value or formula in selected cells to the way they are formatted.
E.g. cell A1 with the value [=12*2] formatted as [EUR 24,-]
This function will put ['EUR 24,-] in cell A1.

Copy formulas without changing their references   
Copy formula's exactly. Normally you have to block all references with $ if you want to achieve this. Not with this function. It will copy all formula's exactly without changing any references.

Move selected range (only values)   
Move the data in your cell's, without the formatting. Just like the copy and paste as values, but now moving.

Copy multiple selections   
Copy multiple selections. Something that is normally not possible in Excel.

Transpose data with formulas   
Transpose data with formula. This will transpose your data and keep the correct formulas.

Empty duplicates in selection   
Empty duplicates in selected cells. This will empty all duplicate values that are found. The first cells containing the duplicated value will keep its value.

Randomize list   
Randomizes a list by row.

Flip selected cells   
This will flip the selected cells. e.g. if you have selected three cells in one row, containing the number 15, 16 and 17, this utility will reverse the order to
17, 16, 15. This utility works for selections in one row or one column.

Delete all rangenames in selection   
Remove all rangenames in your selected range.

Delete all range names in the entire workbook   
Remove all rangenames in the entire workbook.

[spiros]

Download page

What's new in 3.10:
   
—Select » Conditional select
We've added the possibility to select:
number (constant), e.g. 12
number (formula), e.g. =12*2.20371
number (link to sheet), e.g. =sheet1!A1
number (link to file), e.g. ='[Test alignment.xls]Sheet1'!$A$1
—Range » Remove empty rows
This could already be accomplished with the SELECT » Conditional row and column select, hide or delete function, but it is now available as a single utility.
—Columns/Rows » Color each n'th row or column.
Now remembers your last used color.
—Columns/Rows » Merge column data (join cells)
You can now specify how to handle empty cells. You can also specify if the cells have to be treated as formulas, values or formatted text. I've also added the option to merge with the tab- character of use a line-break.
Merge columns
—Text » Insert before/after
Improved combined insert before and/or after An apostrophe can now also be inserted before. This can be handy when you want do display your formulas as text.
You can also specify if the cells have to be treated as formulas, values or formatted text.
You can exclude hidden cells. (comes in handy when you're working with filtered ranges).
Insert before/after
—Fill » Quicknumber numbering of cells
The cells were always aligned right. This is now optional.
—Format » Wrap text
Wraps the text in selected cells, which makes long lines break into multiple lines.
—Format » Unwrap text
Unwraps the text in selected cells.
—Web » Clean webimported data
Added the option to remove all cell-borders.
—Web » Extract hyperlinks
Extracts the hyperlink-address of the cell. This value can be placed in the cell itself or in the column next to it.
—Export » Export selection or active sheet a new file
Export range as picture, the image size has been expanded from 800x600 px to a maximum of 5000x5000 px
—Several small additions.

Bug fixes:

—False Virus alert in sytem.asap Gone ?
Some virusscanners reported a virus in the file system.asap.
Of course ASAP Utilities is virus-free, but it caused a lot of "stress".
The reason was caused by the codeline: insertline() which was in the System » Macro/VBA information » Code washer part. Here you can choose to remove empty lines, commentlines and the indentation from your vba modules. Do do this we used the .insertline() command. Since this caused some virusscanners to panic, we've used another approach to accomplish the same result.
—Menu error
Fixed problems that could occur when the location of the favorites menu could not be found or was not properly set.
—Selection
Fixed an annoying bug that sometimes caused to change the selection of your cells. This could happen when you selected the entire sheet or entire columns or rows.
—System » Remove all macros/VBA from current file
On some systems this produced a compile error. This is now solved (at least on my test-systems).
—Objects/Comments » Comment tools
There was a bug in the "Create report in new file" feature as comments are reported (incorrect) multiple times: Comments of the first sheet were printed again as comments belonging to the second sheet if the second sheet does not contain any comments itself. The wrong duplicates were repeated until a sheet contained a comment again.
—Export » HTML export
Fixed a combination that produced incorrect HTML tags (color outside <TD>tag)
—Export » Export selection or active sheet a new file
Fixed the bug that produced an error in Excel 2003 "The command could not be completed by using the range specified. Select a single cell within the range and try the command again.

[spiros]

Check out this very handy and free excel add-in The Duplicate Master.
    
Highlight duplicate cells or rows    
Delete duplicate cells or rows    
Select duplicate cells or rows    
Extract unique cells or rows to a master list    
Extract and count the number of duplicate cells or rows to a master list
Run a row match on any number of columns

[spiros]

ASAP Utilities 4.2.4 has been released!

New and improved utilities

—Export » Export selection or active sheet as new file...
The default file extension for tab separated files is now tsv instead of txt (tsv stands for tab separated value)
—Export » Export worksheets as separate files...
The default file extension for tab separated files is now tsv instead of txt (tsv stands for tab separated value)
—Formula » Convert formulas to their values
Improved so that it now works on filtered data too.
Normally the utility changes the formulas to their calculated values in all selected cells (hidden or not). If you have selected cells in a filtered list only the visible cells are affected.
—Web » Activate hyperlinks (create from cell values)...
Improved, you can now choose to use the "regular" hyperlink or to use the =HYPERLINK() formula to create the hyperlinks. The formula-method is preferred for shared workbooks and when it involves a large amount of hyperlinks.



Activate hyperlinks (create from cell values)...
—Columns & Rows » Put together rows or columns from several sheets...
The column width of the original data is now copied too.
—Export » Export selected chart or range as image (file)...
You can now choose whether you want to have the utility automatically closed after the export is created.
Export selected chart or range as image (file)...



—Fill » Insert before current value...
Fill » Insert after current value...



You can now choose to skip empty cells in your selection. There is also a 'reset' button added that will clear the insert before and after values and reset the options to their defaults. Because your previous settings are always remembered this can be handy.
Insert before/after example
—Web » Extract hyperlinks...
Your last used settings are remembered.
—Fill » Add leading zeros...
has been renamed to:
Fill » Fill up with leading zeros...
We think this is a better description of what the utility does; Fill the current value with leading zeros up to the total length you specify. For example if you enter 3, the number 1 becomes 001 and 11 becomes 011. If you enter 6, the number 1 becomes 000001.
—Format » The smart difference...
has been renamed to
Format » Detect and visualize adjacent data/group changes
to better reflect what the utility does
—New worksheetfunction =ASAPISBOLD()
Returns true of the referenced cell is bold.
—General
Several minor improvements

[spiros]

ASAP Utilities 4.2.7 has been released!

— Fill » Insert before current value...
Fill » Insert after current value...
Contains a new option to skip the empty cells in your selection
asap-utilities-425-insert-before-after-skip-empty-cells.png

— Formula » Use a significant number of decimals...
This utility has been moved to the 'numbers' submenu. It is also still listed in the 'Format' submenu.

— Export » Export worksheets as separate files...
Improved. You can now choose a custom file extension and with some text files you can choose your own field-enclosers and decimal separator.
 Export » Export worksheets as separate files...

— Range » Find / Replace in all sheets...
Besides searching within all worksheets (default) you can also choose to only find/replace on the active worksheet or within the selected ranges.
Updated: Range » Find / Replace in all sheets...

— Web » Convert hyperlinks to =HYPERLINK() formulas
Updated so that it keeps type the hyperlink formatting if set (blue underlined).

— Text » Replace accented vowels (á, ë, etc.)...
has been renamed to:
Replace accented characters (á, ë, ñ etc.)...



The utility now works on all accented characters, both vowels and consonants.
Furthermore it will automatically detect lower- and uppercase characters

[spiros]

ASAP Utilities 4.2.9 has been released!

Text » Replace accented characters (á, ë, ñ etc.)...
The following characters have been added: ă, ǻ, ĕ, ї, ĩ, ĭ
If you don't see this characters when you start the utility, you will have to click on the 'Default' button in order to get them.



4.2.8
Objects & Comments » Delete all objects on current worksheet
This utility can now be used on multiple worksheets and has been renamed to:
Delete all objects in selected worksheets



Delete all objects in selected worksheets
ASAP Utilities Options » Start the last used utility again...
This now is the default 'repeat' action after a tool in ASAP Utilities has been used so that you can also use it from the Excel menu (Edit > Repeat) or with Excel's built-in shortcuts F4 or Control+Y.

[spiros]

ASAP Utilities 4.5.3!

http://www.asap-utilities.com/...ies-newsletter-2009-02.php#new

[spiros]

ASAP Utilities 4.5.7 has been released!

In ASAP Utilities you can choose between the following languages:

English
Dutch (Nederlands)
German (Deutsch)
Spanish (Español)
French (Français)