Re: Is there a formula for conditional concatenating?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: JulieD (JulieD_at_hctsReMoVeThIs.net.au)
Date: 03/23/05 

Date: Thu, 24 Mar 2005 00:47:14 +0800

Hi Erny

i don't think MS people come in here to read "hints" :)

Harald Staff and i were playing around with this idea - as another poster
raised it a few days ago - and i did a limited concat_if funciton
check out
http://tinyurl.com/5wzuz

for the discussion and the'function' ... you can put it in your personal.xls
file and then access it via the paste function wizard under user defined
functions.

Cheers
JulieD

"Erny Meyer" <ErnyMeyer@discussions.microsoft.com> wrote in message
news:9E769932-5392-4396-AB56-764E68DE6162@microsoft.com...
> Thanks Julie, very much appreciate, actually I wished to avoid any
> programming, as I will need to paste whatever formula I could find to a
> larger range (lots of reference values and lots of names).
>
> I was wondering whether something similar to the SUMIF formula for values
> could existe such as a CONCATENATEIF for strings (maybe a hint to
> microsoft
> to add a functionality).
>
> I will use your code as an option.
>
> Kind regards,
> Erny
>
> "JulieD" wrote:
>
>> Hi Erny
>>
>> no formula that i know of (someone else might know) but this code will do
>> it
>> for you
>> --
>> Sub concatvals()
>> Dim strvalue As String
>> Dim strsearch As String
>>
>> For Each cell In Range("D1:D100")
>> If Not IsNull(cell.Value) Then
>> strsearch = cell.Value
>> strvalue = ""
>> For Each c In Range("A1:A100")
>> If c.Value = strsearch Then
>> If Len(strvalue) < 1 Then
>> strvalue = c.Offset(0, 1).Value
>> Else
>> strvalue = strvalue & ", " & c.Offset(0, 1).Value
>> End If
>> End If
>> Next
>> End If
>> cell.Offset(0, 1).Value = strvalue
>> Next
>> End Sub
>>
>> ---
>> where D1:D100 contains the unique references from column A
>> where E1:E100 is where the results can be put
>> where A1:A100 is your lists of codes
>> where B1:B100 is the list of people you want to concatenation in to
>> column E
>> next to the appropriate code id D
>>
>> ---
>> if you would like assistance implementing the code, please post back
>>
>> Cheers
>> JulieD
>>
>> "Erny Meyer" <ErnyMeyer@discussions.microsoft.com> wrote in message
>> news:33F09320-8789-4D9A-9DE9-F205EC35559C@microsoft.com...
>> >I would like to concatenate cells from a column based on the match in
>> >the
>> > preceding column with a defined value.
>> >
>> > Example:
>> > Col. A B
>> > Row1: T1 Jim
>> > Row2: T3 Paul
>> > Row3: T1 Joe
>> > Row4: T2 Mike
>> > Row5: T3 Caroline
>> >
>> > The formula should return for T1 for instance: Jim;Joe;
>> > (for T2: Mike;
>> > for T3: Paul;Caroline;)
>> > Cells in column A can repeat any number of times.
>> >
>> > Thanks in advance,
>> > Erny
>>
>>
>>

Relevant Pages

  • Re: Multiple value return
    ... > i found the example you posted a bit confusing ... ... > Sub concatvals() ... > Dim strvalue As String ... > Dim strsearch As String ...
    (microsoft.public.excel)
  • Re: Multiple value return
    ... then AFAIK you'll need to use code to achieve this e.g. ... Sub concatvals() ... Dim strvalue As String ... Dim strsearch As String ...
    (microsoft.public.excel)
  • Re: Saving strings to ini file
    ... >> Writing code to read and write an ini for captions, hints and other text ... I'm not using THE ini file for language use, ... >> other string constants, things normally declared like ...
    (alt.comp.lang.borland-delphi)
  • Re: Parsing, mid, left, trim or right to find specific word in string
    ... The OP specifically asked for hints only. ... Dim StringBeingSearched As String ... Notice that the msgbox only returns Aardvark not anteater. ...
    (microsoft.public.excel.programming)
  • Re: very simple encryption
    ... I just need a simple way to encrypt/decrypt a string, main purpose is, ... making it not readable by human eye, any hints on this? ...
    (comp.lang.java.programmer)