Excel concatenate() is seriously crippled, it can add 2 or more strings together, as long as they are supplied as separate parameters. This means, when you have a range of cells with text which you want to add up to create a large text, you need to write an ugly looking biggish concatenate() or use ‘&’ operator over and again.
I felt bored enough the other day to write a better concatenate(), one that can accept a range as input and output one text with all the contents of the input range. What more you can use this to delimit the input range with your own favorite character.
For example, if each of the 7 cells in a1:a7 have “a”, “b”, “c”, “d”, “e”, “f”, “g”, if you want to add all of them up using concatenate you would have to write concatenate(a1,a2,a3,a4,a5,a6,a7) which can be painful if you are planning to do this over a large range or something.
Instead, you can use concat(a1:a7) by installing the UDF (User defined function) I have written. Its nothing miraculous or anything, it just does the dirty job of going through the range for you. If you want to delimit the input range with a comma just use concat(a1:a7,",") to get the out of a,b,c,d,e,f,g Just download the concat() UDF excel add-in and double click on it to install it. If you are little weary of installing UDFs / Macros from third parties, copy past the below excel code in a new sheet’s VB editor and save the sheet as an excel addin (.xla extension)
Added on Aug 26, 2008: I have updated the code, copy it again if you have the old one
Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
if cstr(cell.value)<>"" and cstr(cell.value)<>" " then
retVal = retVal & cstr(cell.Value) & dlm
end if
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function
Did you find this useful? Are you looking for some other excel UDFs as well, drop a comment, I am a busy coffee drinker, but between the sips I can whip out ugly looking but functional vb code 😛
13 Responses to “Data Validation using an Unsorted column with Duplicate Entries as a Source List”
Pivot Table will involve manual intervention; hence I prefer to use the 'countif remove duplicate trick' along with 'text sorting formula trick; then using the offset with len to name the final range for validation.
if using the pivot table, set the sort to Ascending, so the list in the validation cell comes back alphabetically.
Hui: Brillant neat idea.
Vipul: I am intrigued by what you are saying. Please is it possible to show us how it can be done, because as u said Hui's method requires user intervention.
Thks to PHD and all
K
Table names dont work directly inside Data validation.
You will have to define a name and point it to the table name and then use the name inside validation
Eg MyClient : Refers to :=Table1[Client]
And then in the list validation say = MyClient
Kieranz,
Pls download the sample here http://cid-e98339d969073094.skydrive.live.com/self.aspx/.Public/data-validation-unsorted-list-example.xls
Off course there are many other ways of doing the same and integrating the formulae in multiple columns into one.
Pls refer to column FGHI in that file. Cell G4 is where my validation is.
Vipul:
Many thks, will study it latter.
Rgds
K
[...] to chandoo for the idea of getting unique list using Pivot tables. What we do is that create a pivot table [...]
@Vipul:
Thanks, that was awesome! 🙂
@Playercharlie Happy to hear that 🙂
Great contribution, Hui. Solved a problem of many years!
Thanks to you, A LOT
Hi Hui,
Greeting
hope you are doing well.
I'm interested to send you a private vba excel file which i need to show detail of pivot in new workbook instead of showing in same workbook as new sheet.
Please contact me on muhammed.ye@gmail.com
Best Regards