Frontendplace Blog

Sharing Frontend developing ideas

Archive for December, 2011

Re-activate one finger click-and-drag gesture in Lion

Posted by info on 31st December 2011

When I installed Lion over my old snow leopard versioned system I kept the trackpad settings. I used to use the one-finger gesture in Lion a lot. But when installing a clean Lion default it doesn’t come with the same one finger click-and-drag gesture that used to be in Snow Leopard, and instead the user has an option of enabling a three finger drag gesture through the system preferences. This post shows you how to re-enable the one finger drag gesture. So you are able to use the same settings as before on your older system.


To enable one finger drag, go to System Preferences, click on Universal Access, and then the Mouse & Trackpad tab. Now click on the “Trackpad Options…” button available at the bottom of this screen. Enable the second option, which is named “Dragging” and select the desired draging behaviour from the drop down list
After this you should be able to do one finger click and dragging just like in good old Snow Leopard.

Posted in Other tutorials | Comments Off on Re-activate one finger click-and-drag gesture in Lion

Vlookup and concat in excel

Posted by info on 4th December 2011

Rationale:

I want to find all the objects where a specific part is used in a list of objects and show the objectlist so I know in which objects the part is used.
For example I have this table
B 1
A 2
B 3
B 4

how to display the B result like this
B 1;3;4 whereas the vlookup only return 1 value

Solution: use this macro in excel.

' Lookup_concat module
' This module vertical search all occurrences of a string in a column and
' return the values from a cell in an other column in the same
' row separated with a given separator string.
'
' ©2011 www.frontendplace.nl
'
' Requirements:
' A table sorted on the column where the Search_in_col string is
'
' Usage:
' lookup_concat(value to search{Cell|String}, search column{Range}, returned value column{Range}, seperator{String})
'
' Example:
' lookup_concat(A4,'ReferenceSheet'!$B$1:$B$100,'ReferenceSheet'!$A$1:$A$100,";")
'
' Return value:
' found values separated with separator string defaulted with ","
Function Lookup_concat(Search_String As String, Search_in_col As Range, Return_val_col As Range, ByVal Seperator As String)

Dim lRowIndex As Long
Dim strResult As String
Dim strSep As String

If Len(Seperator) = 0 Then strSep = ", " Else strSep = Seperator

For lRowIndex = 1 To Search_in_col.Count
    If InStr(1, Search_in_col.Cells(lRowIndex, 1), Search_String) Then
        If Len(strResult) = 0 Then
            strResult = Return_val_col.Cells(lRowIndex, 1).Value
        Else
            strResult = strResult & strSep & Return_val_col.Cells(lRowIndex, 1).Value
        End If
    End If
Next

Lookup_concat = Trim(strResult)

End Function

Example

 

Posted in Scripting | Comments Off on Vlookup and concat in excel