Concatenate Text with Same Seq No

[XLBob]

I need to add a calculated column to concatenate text with same SeqNo. see below file. details inside the workbook
https://onedrive.live.com/?cid=7DF5E31C189063EC&id=7DF5E31C189063EC!124

Thanks in advance

 

[ParamRay]

I think you'll need a custom function.

Try this custom function:

Function ConTex(Seq, SeqRng, TypRng)

    Dim i As Long
    
    ConTex = Seq
    For i = 1 To SeqRng.Count
        If SeqRng(i) = Seq Then
            ConTex = ConTex & " " & TypRng(i)
        End If
    Next i

End Function

Then you can use the function in your table using one of these formulas:

=ConTex([@SeqNo],[SeqNo],[Type])

=ConTex(Table1[@SeqNo],Table1[SeqNo],Table1[Type])

=ConTex($A2,$A$2:$A$9,$B$2:$B$9)

 

[scottsen]

gpeacock, you stumbled into the power pivot forum, that probably won't help xlbob

Bob, I am hesitant to say... "not possible", but... I'm scared. If you have any way to massage that data BEFORE you get into Power Pivot... do that, cuz i have no idea how to pull this one off.

 

[scottsen]

I talked with Rob and he agrees... without a ConcatenateX function, you are kinda stuck.

If you have a fixed (and relatively small) number of Types per SeqNo... we can probably pull of something incredibly hacky with huge nested if's, dogs and cats living together, etc...