Regular expressions are a system for matching patterns in text data, which are widely used in UNIX systems, and occasionally on personal computers as well. They provide a very powerful, but also rather obtuse, set of tools for finding particular words or combinations of characters in strings.
[Link]
The more you work on Excel, the more indispensable Dynamic Ranges become. From simple data validation to conditional formatting and vlookups, dynamic ranges become the core referential element in Excel. The first page I ever read on the subject : Dynamic Pages on Ozgrid.com :
Possibly one of Excels most underutilised aspects is its ability to create dynamic named ranges that will expand and contract according to the data in them.
If you haven’t worked on them, now is the best time to start reading up.
On the newsgroups, I saw a question about detecting duplicate entries in a column. Jan Karel suggested the pre-emptive approach with data validation which will not allow the user to enter a duplicate entry.
There are cases where duplicate entries have to be allowed but a visual indication indicates the duplicate values. Conditional Formatting comes in handy in those cases :
Suppose B1:B20 holds the data. the conditional formatting should be applied by selecting the range and applying the “Formula Is” is : =COUNTIF($B$2:$B$20,B2)>1.
Duplicate data can still be entered but is automatically indicated :
Ever needed the values from a range of cells as a string separated by commas ? I used do this in a two step concatenate : the first step is for every cell in the range and then another concatenate for all the “concatenated cells”.
So today, I decided to create my own UDF to generate a string from a selection of cells and any “separator”.
Here’s the ConcSep function :
Public Function ConcSep(InCells As Range, Sep As String) As String
Dim OutStr As String
Dim Cell As Variant
OutStr = “”
If InCells Is Nothing Then Exit Function
On Error Resume Next
For Each Cell In InCells
OutStr = OutStr & Cell.Value & Sep
Next Cell
OutStr = Left(OutStr, Len(OutStr) - Len(Sep))
ConcSep = OutStr
End Function
And here’s the output :
Obviously, this now goes into the add-in you created . and will also be part of my Utilities project.
Last week I posted about a user-defined function to get the unique values in a range. I’ve now extended this function to return unique or duplicate values based on a parameter passed through the function call. so the function definition now changes from :
to
If you want the function to return Unique values only, then set GetUnique to 1 in your function call. Setting GetUnique to 0 will return Duplicate values.
The function basically imports all the values in the range to the array Entries with a corresponding array EntCount that counts the number of occurrences of every value. The rest is easy to figure out.
Obviously, it would be a good idea to create your own add-in where you can have such functions at close range for your every-day use.
Here’s the code (remember to correct the comments).
screen-shot :
A while back, j-walk posted an entry about Filling a ListBox with Unique Items.
Here’s my modified version to transform it into a UDF (User-Defined Function)that can be used in a spreadsheet:
On Error Resume Next
For Each Cell In AllCells
ColUnique.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
‘ Sort the collection
For i = 1 To ColUnique.Count - 1
For j = i + 1 To ColUnique.Count
If ColUnique.Item(i) > ColUnique.Item(j) Then
Swap1 = ColUnique.Item(i)
Swap2 = ColUnique.Item(j)
ColUnique.Add Swap1, before:=j
ColUnique.Add Swap2, before:=i
ColUnique.Remove i + 1
ColUnique.Remove j + 1
End If
Next j
Next i
‘ Feed the sorted, non-duplicated items to the temporary array
ReDim Preserve tmp(ColUnique.Count - 1)
For i = 1 To ColUnique.Count
tmp(i - 1) = ColUnique(i)
Next i
Unique = tmp
‘ output the values
If AllCells.Rows.Count >= AllCells.Columns.Count Then
Unique = WorksheetFunction.Transpose(Unique)
End If
Erase tmp
End Function
Here’s how it looks on your sheet:
Remember to press Ctrl+Shift+Enter !
Powered by WordPress