ConcatIF ??

[Rikknor]

Hello All

CountIf and SumIf are great and I use them quite a bit but today
I'm looking for a solution to a unique issue I'm having.

I have data in cells

ColumnA|ColumnB
---------- | ----------
Student1| TRA1
Student2| TRA1
Student1| TRA2
Student2| TRA2
Student3| TRA1
Student2| TRA3

I would like a formula, (although I could write this as a custom VBA function, a VBA function would entail moving modules from book to book. So a formula would be much nicer.) that would yield the following result...

ColumnD|ColumnE
---------- |-----------
Student1|TRA1,TRA2
Student2|TRA1,TRA2,TRA3
Student3|TRA1

Due to company policy (sigh) I cannot load third party add-ins, so this has to be with whatever Excel ships with.

Thank you

 

[Joe4]

(although I could write this as a custom VBA function, a VBA function would entail moving modules from book to book. So a formula would be much nicer.)

Not sure if you knew this, but you can store VBA code in your Personal Workbook also. Then, it is available whenever Excel is open for any file (on that User's computer).

I do this with a lot of VBA code that I use in a lot of different places. Then it does not need to be stored in each individual workbook.

 

[just_jon]

Put the aconcat code in a standard module<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> aconcat(a<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>,<SPAN style="color:#00007F">Optional</SPAN> sep<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN> = "")<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#007F00">' Harlan Grove, Mar 2002</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> y<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>

    <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">TypeOf</SPAN> a<SPAN style="color:#00007F">Is</SPAN> Range<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> y<SPAN style="color:#00007F">In</SPAN> a.Cells
            aconcat = aconcat & y.Value & sep
        <SPAN style="color:#00007F">Next</SPAN> y
    <SPAN style="color:#00007F">ElseIf</SPAN> IsArray(a)<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> y<SPAN style="color:#00007F">In</SPAN> a
            aconcat = aconcat & y & sep
        <SPAN style="color:#00007F">Next</SPAN> y
    <SPAN style="color:#00007F">Else</SPAN>
        aconcat = aconcat & a & sep
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>

    aconcat = Left(aconcat, Len(aconcat) - Len(sep))<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

Then

Book1
	A	B	C	D
1	aaa	one	aaa
2	kkk	xxx	onetwothree
3	aaa	two
4	lll	yyy
5	aaa	three
6
7
8
9
10
Sheet1

The above is an array formula, which requires entry via Control+Shift+Enter [CSE], not just Enter. Any direct edit of the formula will require re-entry via CSE. You can tell if the formula has been entered correctly, as it will have braces - { } - around it afterward.

EDIT Welcome to MrExcel

 

[Rikknor]

Truly Amazing, Thank you so much for the solution and thank you for the warm welcome. I didn't know about storing VBA in my personal workbook, so thank you for that too. I suppose I could always paste values if I needed to send the book elsewhere.

I think I'm going to like it here.