Concatenate based on condition...is it possible with a formula?

[PP3321]

I want to concatenate values based on Month (Column A).

Is it possible with a formula?

*I am using Excel 2010

 

[PP3321]

Option Explicit

Sub test()
    Dim dic    As Object
    Dim myRange As Range
    Dim r      As Range
 
    Set dic = CreateObject("Scripting.Dictionary")
    Set myRange = Range("A2", Cells(Rows.Count, 1).End(xlUp))
    For Each r In myRange
        dic(r.Value) = dic(r.Value) & r.Offset(, 1).Value & ";"
    Next
    For Each r In myRange
        r.Offset(, 2).Value = dic(r.Value)
    Next
End Sub

I kind of did this with VBA, using Dictionary.

There are 2 problems with VBA:

1. Requirement is to show concatenated value at the top row for each month.

2. My team does not get VBA.
If possible, want to achieve this with a formula...

 

[Chihiro]

I'd recommend combining VBA & Formula.

Construct UDF via VBA, then use that formula in cells.
Follow link below.
Scroll down to section "Concatenate text based on criteria with User Defined Function"
https://www.extendoffice.com/documents/excel/2723-excel-concatenate-based-on-criteria.html

 

[bosco_yip]

See per attached file (non-VBA proposal),

using formula + Conditional Formatting.

Regards
Bosco

 

[PP3321]

@Chihiro
Thank you so much for sharing me the link...

 

[Somendra Misra]

Hi,

Try below code modified your code:

Option Explicit

Sub test()
    Dim dic    As Object
    Dim myRange As Range
    Dim r      As Range
    Dim j As Long
    Dim cntr As Long
   
    Set dic = CreateObject("Scripting.Dictionary")
    Set myRange = Range("A2", Cells(Rows.Count, 1).End(xlUp))
    For Each r In myRange
        dic(r.Value) = dic(r.Value) & r.Offset(, 1).Value & ";"
    Next
    cntr = 1
    For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Application.WorksheetFunction.CountIf(Range("A2:A" & j), Range("A" & j)) = 1 Then
            Range("C" & j).Value = Mid(dic(cntr), 1, Len(dic(cntr)) - 1)
            cntr = cntr + 1
        End If
    Next
       
       
       
End Sub

Regards,

 

[PP3321]

@bosco_yip
Wow!!!Thank you so much for this!!!!!!!It is awesome!!!

 

[PP3321]

@bosco_yip

I like how you used countif for conditional formatting...

 

[PP3321]

@Somendra Misra
Thank you!!!!

Your logic is
If count is 1 = first instance

Is that right?

I ran your macro but I got error 5.
When I have time I will look into it.

Thank you!!!!!

 

[Somendra Misra]

Yes.

Well I checked the code, it did not gave any error.

Regards,

 

[PP3321]

OK thank you for checking. Maybe I did something wrong.
Thank you!!!!!