Looking for a single text value and returning 1 or multiple text values in one cell

**mike7952** · 10-23-2014, 07:20 AM

This should do it

Sub abc()
 Dim i As Long, ii As Long, a
 
 With Worksheets("sheet1")
    a = .Range("a1").CurrentRegion
 End With
 
 With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(a)
        If Not IsEmpty(a(i, 1)) Then
            ii = i
            .Item(a(i, 1)) = a(i, 3)
        Else
            .Item(a(ii, 1)) = .Item(a(ii, 1)) & ", " & a(i, 3)
        End If
    Next
    With Worksheets("sheet2")
        a = .Range("a2", .Cells(Rows.Count, 1).End(xlUp).Offset(, 1))
    End With
    For i = 1 To UBound(a)
        If .exists(a(i, 1)) Then a(i, 2) = .Item(a(i, 1))
    Next
    With Worksheets("sheet2")
        .Range("a2").Resize(UBound(a), UBound(a, 2)) = a
    End With
 End With
End Sub

**cramnij** · 10-23-2014, 11:31 AM

Hi Mike,
Thank you very much for your fantastic macro! It works perfectly in the demo file. However, when I use your macro (somewhat adapted) in my real file, I get the error message: "subscript out of range".
Sheet1 in my original file is a PivotTable, is this a big problem? Hope not.
Additionally, the first text in sheet1 starts in cell A7. Therefor I adjusted your code (line 5) "a1" to "a7". This works in the demo file so I assume this is correct. I also adjusted the sheet names but I don’t know if it’s possible that sheet names contain spaces?
Sheet2 starts at cell A3. Is it necessary to make a code adjustment for that?
Finally, I'd like the result in another column (not B but M). Do not know how to modify the code for that.

Hopefully you can to help me with the final adjustments. Already many thanks in advance!
With kind regards,
Marc

Looking for a single text value and returning 1 or multiple text values in one cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Looking for a single text value and returning 1 or multiple text values in one cell

Re: Looking for a single text value and returning 1 or multiple text values in one cell

Thread Information

Users Browsing this Thread

Similar Threads

Looking for a single text value and returning 1 or multiple text values

Hi, Need help with returning multiple values into single cell

[SOLVED] Excel - Returning multiple values in a single cell that match criteria

Returning a value to a single cell based on multiple values elsewhere.

Searching for non specific text and returning multiple values

Bookmarks

Bookmarks

Posting Permissions